How to map a table valued function to a query type in EF Core 2.1

.net-core c# entity-framework-core

Question

I have a table valued function on SQL Server

CREATE FUNCTION [dbo].[FStatusDepo] 
(   
    @ProvozID int, @ZonaID int NULL, @minutesWarning int
)
RETURNS TABLE 
AS
RETURN 
(
SELECT 
      q.[DepoID]
      ,SUM(CASE WHEN v.Vjezd IS NOT NULL AND datediff(mi, v.Vjezd, GETDATE())>@minutesWarning THEN 1 ELSE 0 END)  AS VjezduPoLhute
      ,SUM(CASE WHEN v.Vjezd IS NOT NULL AND datediff(mi, v.Vjezd, GETDATE())<=@minutesWarning THEN 1 ELSE 0 END)  AS VjezduVLhute
  FROM 
  ...

I have a new project with ASP.NET Core 2.1 and EF Core 2.1.

I have read that I can define a new Query type within DbContext:

public class  StatusDepo
{
    public int DepoID { get; set; }
    public int VjezduPoLhute { get; set; }
    public int VjezduVLhute { get; set; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder
            .Query<StatusDepo>().ToView("FStatusDepo");

When I look at the example:

https://github.com/aspnet/EntityFrameworkCore/blob/dev/samples/QueryTypes/Program.cs

I see there, that I should probably also add this line to the DbContext:

    public DbQuery<StatusDepo> StatusDepos { get; set; }

Then I am able to execute:

   using (DixieContext db = new DixieContext())
    {
        var listStatusDepo = 
              db.StatusDepos.FromSql("SELECT * FROM [dbo].[FStatusDepo] (1, 1, 20)").ToList();
    }  

This works OK but the line:

    modelBuilder.Query<StatusDepo>().ToView("FStatusDepo")

has no purpose at all, is there any other construct for parametrized views or should I just delete the line and use FromSql only?

1
2
6/26/2018 1:49:26 PM

Accepted Answer

is there any other construct for parametrized views or should I just delete the line and use FromSql only?

Yes. You should remove modelBuilder.Query<StatusDepo>().ToView("FStatusDepo") It does nothing here because you're not mapping your class StatusDepo to a SQL VIew but you're using a SQL Function that returns a table.

EF Core ToView() method is for mapping a class to a SQL View (and configure mapping property to columns if needed) so you'll not need to write your SQL because EF will know how to generate it for you.

In your case your code below is sufficient:

using (DixieContext db = new DixieContext())
{
    var listStatusDepo = db.StatusDepos.FromSql("SELECT * FROM [dbo].[FStatusDepo] (1, 1, 20)").ToList();
}  
2
6/26/2018 1:49:11 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow