What are some of the Use Cases<\/h2>\n\n- Is used when working with entities with no key<\/strong> propertie(s) defined, may it be a Table, Stored Procedure or a View<\/li>\n
- Is used when mapping to queries specified in the model<\/li>\n
- Is used for ad hoc queries where changes are not tracked in DbContext.\u00a0<\/em>In other words, queries that are strictly read-only<\/li>\n<\/ul>\n
So let’s get Started<\/h2>\n
First let’s define the two POCO classes. Each of these classes below maps to the tables in the database.<\/p>\n
public class BaseAggr\r\n {\r\n public long BaseAggrId { get; set; }\r\n [Display(Name = \"Portfolio ID\")]\r\n public string PortfolioId { get; set; }\r\n\r\n [Display(Name = \"Scenario ID\")]\r\n public long? ScenarioId { get; set; }\r\n\r\n [Display(Name = \"Sum Pnl\")]\r\n public double? SumPnl { get; set; }\r\n\r\n [Display(Name = \"Business Date\")]\r\n [DataType(DataType.Date)]\r\n public DateTime? BusinessDate { get; set; }\r\n }\r\n<\/pre>\n public class WhatIfAggr\r\n {\r\n public long WhatifAggrId { get; set; }\r\n\r\n [Display(Name=\"Portfolio ID\")]\r\n public string PortfolioId { get; set; }\r\n\r\n [Display(Name =\"Scenario ID\")]\r\n public long? ScenarioId { get; set; }\r\n\r\n [Display(Name =\"Sum PnL\")]\r\n public double? SumPnl { get; set; }\r\n\r\n [Display(Name =\"Business Date\")]\r\n [DataType(DataType.Date)]\r\n public DateTime? BusinessDate { get; set; }\r\n }\r\n<\/pre>\nNext, let’s create a database view. As you can tell from the query below that it’s using Common Table Expression to form a result set.<\/p>\n
CREATE OR ALTER VIEW [dbo].[vwVarByPortfolios] AS\r\nSELECT * FROM \r\n(SELECT portfolio_id, scenario_id as scenario_id, sum_pnl as var_pnl \r\n\tFROM (SELECT portfolio_id,scenario_id, sum_pnl, RANK() OVER (PARTITION BY portfolio_id ORDER BY sum_pnl DESC) AS pnl_rank FROM base_aggr) AS Agg\r\n\tWHERE pnl_rank = 98\r\n) AS A\tINNER JOIN\r\n\t\r\n(SELECT portfolio_id AS wi_portfolio_id, scenario_id AS wi_scenario_id, sum_pnl AS wi_var_pnl \r\n\tFROM (SELECT portfolio_id,scenario_id, sum_pnl, RANK() OVER (PARTITION BY portfolio_id ORDER BY sum_pnl desc) AS pnl_rank from whatif_aggr) AS Agg\r\n\tWHERE pnl_rank = 98\r\n ) AS X\r\n ON portfolio_id = wi_portfolio_id;\r\nGO<\/pre>\nIn addition to the classes above, you would also need a class for Query Type to hold results from the database view like so:<\/p>\n
public class PortfolioView\r\n{\r\npublic string PortfolioID { get; set; }\r\npublic long ScenarioID { get; set; }\r\npublic double VarPnl { get; set; }\r\npublic string WiPortfolioID { get; set; }\r\npublic long WiScenarioID { get; set; }\r\npublic double WiVarPnl { get; set; }\r\n}\r\n<\/pre>\nNext, we will create a DbQuery property in the DbContext class in order for it to be recognized as a query type.<\/p>\n
public DbQuery<\/strong> PortfolioView { get; private set; }\r\n<\/pre>\nNext, we will write a fluent API in DbConext’s OnModelCreating method to map the view to the Query Type i.e. vwVarByPortfolios<\/strong>. The “ToView<\/em>” extension method is used to configure the mapping especially when a relational database is being targeted.<\/p>\nmodelBuilder.Query()<\/strong>.ToView<\/strong>(\"vwVarByPortfolios\")\r\n .Property(p => p.PortfolioID).HasColumnName(\"portfolio_id\");\r\n\r\n modelBuilder.Query().ToView(\"vwVarByPortfolios\")\r\n .Property(p => p.ScenarioID).HasColumnName(\"scenario_id\");\r\n\r\n modelBuilder.Query().ToView(\"vwVarByPortfolios\")\r\n .Property(p => p.VarPnl).HasColumnName(\"var_pnl\");\r\n\r\n modelBuilder.Query().ToView(\"vwVarByPortfolios\")\r\n .Property(p => p.WiPortfolioID).HasColumnName(\"wi_portfolio_id\");\r\n\r\n modelBuilder.Query().ToView(\"vwVarByPortfolios\")\r\n .Property(p => p.WiScenarioID).HasColumnName(\"wi_scenario_id\");\r\n\r\n modelBuilder.Query().ToView(\"vwVarByPortfolios\")\r\n .Property(p => p.WiVarPnl).HasColumnName(\"wi_var_pnl\");\r\n<\/pre>\nPlease note, DbQuery does not require a primary key to be defined unlike its DbSet<T> counterpart. The Id’s are being used here just because they are needed.<\/p>\n
What you cannot Do:<\/h2>\n
Couple things worth noting is that in this version i.e. EF Core 2.2, EF Core does not allow Views to be created in the database when migrations are run. Conversely, you cannot reverse engineer views yet. This will most likely be available in the future version of EF Core i.e. 3.0. Here’s the EF Core Roadmap<\/a> in case you wish you know further.<\/p>\nLastly, you can query the database view in your console app or a Web App, although in this case, the latter is being used along with Razor Pages.<\/p>\n
public class PortfolioModel : PageModel\r\n {\r\n private readonly DataAggr.Models.DataAggrContext _context;\r\n\r\n public PortfolioModel(DataAggrContext context)\r\n {\r\n _context = context;\r\n }\r\n\r\n [BindProperty]\r\n public IList PortfolioView { get; set; }\r\n\r\n public async Task OnGetAsync()\r\n {\r\n PortfolioView = await _context.Query()\r\n .ToListAsync();\r\n }\r\n }\r\n<\/pre>\nA quick snapshot of the output of the View from inside SQL Management Studio.<\/p>\n
![\"\"](\"https:\/\/obioberoi.com\/wp-content\/uploads\/2019\/02\/SqlView-1.jpg\")
<\/p>\n
Output on a Razor Page in ASP.NET Core.<\/p>\n
![\"\"](\"https:\/\/obioberoi.com\/wp-content\/uploads\/2019\/02\/PortfolioView-1024x583.jpg\")
<\/p>\n
Hope you found the article useful!<\/p>\n","protected":false},"excerpt":{"rendered":"
If you have been using Entity Framework 6.x or EF Core, then you are already familiar with Entities and its usage. Query Types is a new addition to EF Core 2.1 which you can refer to Entities as its cousin. … Continue reading
So let’s get Started<\/h2>\n
First let’s define the two POCO classes. Each of these classes below maps to the tables in the database.<\/p>\n
public class BaseAggr\r\n {\r\n public long BaseAggrId { get; set; }\r\n [Display(Name = \"Portfolio ID\")]\r\n public string PortfolioId { get; set; }\r\n\r\n [Display(Name = \"Scenario ID\")]\r\n public long? ScenarioId { get; set; }\r\n\r\n [Display(Name = \"Sum Pnl\")]\r\n public double? SumPnl { get; set; }\r\n\r\n [Display(Name = \"Business Date\")]\r\n [DataType(DataType.Date)]\r\n public DateTime? BusinessDate { get; set; }\r\n }\r\n<\/pre>\n public class WhatIfAggr\r\n {\r\n public long WhatifAggrId { get; set; }\r\n\r\n [Display(Name=\"Portfolio ID\")]\r\n public string PortfolioId { get; set; }\r\n\r\n [Display(Name =\"Scenario ID\")]\r\n public long? ScenarioId { get; set; }\r\n\r\n [Display(Name =\"Sum PnL\")]\r\n public double? SumPnl { get; set; }\r\n\r\n [Display(Name =\"Business Date\")]\r\n [DataType(DataType.Date)]\r\n public DateTime? BusinessDate { get; set; }\r\n }\r\n<\/pre>\nNext, let’s create a database view. As you can tell from the query below that it’s using Common Table Expression to form a result set.<\/p>\n
CREATE OR ALTER VIEW [dbo].[vwVarByPortfolios] AS\r\nSELECT * FROM \r\n(SELECT portfolio_id, scenario_id as scenario_id, sum_pnl as var_pnl \r\n\tFROM (SELECT portfolio_id,scenario_id, sum_pnl, RANK() OVER (PARTITION BY portfolio_id ORDER BY sum_pnl DESC) AS pnl_rank FROM base_aggr) AS Agg\r\n\tWHERE pnl_rank = 98\r\n) AS A\tINNER JOIN\r\n\t\r\n(SELECT portfolio_id AS wi_portfolio_id, scenario_id AS wi_scenario_id, sum_pnl AS wi_var_pnl \r\n\tFROM (SELECT portfolio_id,scenario_id, sum_pnl, RANK() OVER (PARTITION BY portfolio_id ORDER BY sum_pnl desc) AS pnl_rank from whatif_aggr) AS Agg\r\n\tWHERE pnl_rank = 98\r\n ) AS X\r\n ON portfolio_id = wi_portfolio_id;\r\nGO<\/pre>\nIn addition to the classes above, you would also need a class for Query Type to hold results from the database view like so:<\/p>\n
public class PortfolioView\r\n{\r\npublic string PortfolioID { get; set; }\r\npublic long ScenarioID { get; set; }\r\npublic double VarPnl { get; set; }\r\npublic string WiPortfolioID { get; set; }\r\npublic long WiScenarioID { get; set; }\r\npublic double WiVarPnl { get; set; }\r\n}\r\n<\/pre>\nNext, we will create a DbQuery property in the DbContext class in order for it to be recognized as a query type.<\/p>\n
public DbQuery<\/strong> PortfolioView { get; private set; }\r\n<\/pre>\nNext, we will write a fluent API in DbConext’s OnModelCreating method to map the view to the Query Type i.e. vwVarByPortfolios<\/strong>. The “ToView<\/em>” extension method is used to configure the mapping especially when a relational database is being targeted.<\/p>\nmodelBuilder.Query()<\/strong>.ToView<\/strong>(\"vwVarByPortfolios\")\r\n .Property(p => p.PortfolioID).HasColumnName(\"portfolio_id\");\r\n\r\n modelBuilder.Query().ToView(\"vwVarByPortfolios\")\r\n .Property(p => p.ScenarioID).HasColumnName(\"scenario_id\");\r\n\r\n modelBuilder.Query().ToView(\"vwVarByPortfolios\")\r\n .Property(p => p.VarPnl).HasColumnName(\"var_pnl\");\r\n\r\n modelBuilder.Query().ToView(\"vwVarByPortfolios\")\r\n .Property(p => p.WiPortfolioID).HasColumnName(\"wi_portfolio_id\");\r\n\r\n modelBuilder.Query().ToView(\"vwVarByPortfolios\")\r\n .Property(p => p.WiScenarioID).HasColumnName(\"wi_scenario_id\");\r\n\r\n modelBuilder.Query().ToView(\"vwVarByPortfolios\")\r\n .Property(p => p.WiVarPnl).HasColumnName(\"wi_var_pnl\");\r\n<\/pre>\nPlease note, DbQuery does not require a primary key to be defined unlike its DbSet<T> counterpart. The Id’s are being used here just because they are needed.<\/p>\n
What you cannot Do:<\/h2>\n
Couple things worth noting is that in this version i.e. EF Core 2.2, EF Core does not allow Views to be created in the database when migrations are run. Conversely, you cannot reverse engineer views yet. This will most likely be available in the future version of EF Core i.e. 3.0. Here’s the EF Core Roadmap<\/a> in case you wish you know further.<\/p>\nLastly, you can query the database view in your console app or a Web App, although in this case, the latter is being used along with Razor Pages.<\/p>\n
public class PortfolioModel : PageModel\r\n {\r\n private readonly DataAggr.Models.DataAggrContext _context;\r\n\r\n public PortfolioModel(DataAggrContext context)\r\n {\r\n _context = context;\r\n }\r\n\r\n [BindProperty]\r\n public IList PortfolioView { get; set; }\r\n\r\n public async Task OnGetAsync()\r\n {\r\n PortfolioView = await _context.Query()\r\n .ToListAsync();\r\n }\r\n }\r\n<\/pre>\nA quick snapshot of the output of the View from inside SQL Management Studio.<\/p>\n
<\/p>\n
Output on a Razor Page in ASP.NET Core.<\/p>\n
<\/p>\n
Hope you found the article useful!<\/p>\n","protected":false},"excerpt":{"rendered":"
If you have been using Entity Framework 6.x or EF Core, then you are already familiar with Entities and its usage. Query Types is a new addition to EF Core 2.1 which you can refer to Entities as its cousin. … Continue reading