C# Sql Raw Query to Entity Framework query

Question

I have a group data in my database like that:

Code   ModificationDate
--------------------------
 A     2020/01/02
 A     2020/01/01
 B     2020/01/03
 B     2020/01/01
 C     2020/01/04
 C     2020/01/01

And I want to get the value with the most recent ModificationDate from each group of codes.

I tried some linq queries, but I always get the same error

Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable'

To workaround it, I wrote this code:

var query = _context.Customers.FromSqlRaw(@"
            SELECT t.* FROM(
                  SELECT[Code], MAX([ModificationDate]) as [ModificationDate]
                  FROM[Customers]
                  GROUP BY[Code]
            ) c
            INNER JOIN[Customers] t
            ON t.[Code] = c.[Code] AND t.[ModificationDate] = c.[ModificationDate]");

It works fine, but I want to translate it to a linq query that doesn't trigger the mentioned error.

I don't want to use AsEnumerable or ToList because I have a lot of data and load it into memory is going to be slow.

Do something like this : db.OrderByDescending(x => x.ModificationDate).GroupBy(x => x.ModificationDate).Selectx => x.First()).ToList(); — jdweng
– jdweng, Commented Nov 19, 2020 at 11:26
It triggers the same error: .First()' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. :( — Pablo
– Pablo, Commented Nov 19, 2020 at 11:34
The is something wrong with your model. Each table in the model should be an Enumerable() object. right click you model variable db and select Definition to locate the model. — jdweng
– jdweng, Commented Nov 19, 2020 at 11:45
.AsEnumerable() does NOT load the whole data into memory! — marc_s
– marc_s, Commented Nov 19, 2020 at 12:09
Then is a good practice to use .AsEnumerable() in this example? — Pablo
– Pablo, Commented Nov 19, 2020 at 14:04

Gert Arnold · Accepted Answer · 2020-11-19 12:02:33Z

1

This exception occurs frequently with GroupBy in EF core 3/5. In your case there is way to make EF happy by following this query pattern (based on the Chinook sample database, not knowing your class model):

from c in Customers
from i in c.Invoices.OrderByDescending(i => i.InvoiceDate).Take(1)
select new 
{
    c.LastName,
    i.BillingAddress
}

This is even the preferred query pattern, even failing sufficient GroupBy support, because (at least in Sql Server, maybe other providers too), EF translates Take into the efficient ROW_NUMBER() OVER(PARTITION BY ...) construct in SQL. The LINQ query above is translated to this SQL statement in EF-core 3.1.10:

SELECT [c].[LastName], [t0].[BillingAddress]
FROM [Customer] AS [c]
INNER JOIN (
    SELECT [t].[InvoiceId], [t].[BillingAddress], [t].[BillingCity], [t].[BillingCountry], [t].[BillingPostalCode], [t].[BillingState], [t].[CustomerId], [t].[InvoiceDate], [t].[Total]
    FROM (
        SELECT [i].[InvoiceId], [i].[BillingAddress], [i].[BillingCity], [i].[BillingCountry], [i].[BillingPostalCode], [i].[BillingState], [i].[CustomerId], [i].[InvoiceDate], [i].[Total]
    , ROW_NUMBER() OVER(PARTITION BY [i].[CustomerId] ORDER BY [i].[InvoiceDate] DESC) AS [row]
        FROM [Invoice] AS [i]
    ) AS [t]
    WHERE [t].[row] <= 1
) AS [t0] ON [c].[CustomerId] = [t0].[CustomerId]

It's a bit disappointing, though, that EF doesn't reduce the fields in the subquery according to the properties requested in the LINQ query. That could be achieved by a query like this:

from c in Customers
from i in c.Invoices.OrderByDescending(i => i.InvoiceDate)
    .Select(i => new { i.BillingAddress, i.BillingCity })
    .Take(1)
select new 
{
    c.LastName,
    i.BillingAddress,
    i.BillingCity
}

...unfortunately causing some code repetition.

edited Nov 19, 2020 at 12:02

answered Nov 19, 2020 at 11:56

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ivan Stoev Over a year ago

This works for collection navigation properties and even manual joins (using correlated subquery syntax and not GroupJoin syntax ), but not for GroupBy scenarios with the standard pattern query.GroupBy(e => KeyExpr(e)).SelectMany(g => g.OrderBy{Descending}(e => OrderExpr(e)).Take(N)). It really needs Support ability to select top N of each group #13805 being implemented. The interesting is that this actually represents where row_number() over (partition by order by) <= N pattern.

Gert Arnold Over a year ago

@Ivan Yep, I know, and I wonder why EF has made GroupBy support so hard for themselves.

Pablo · Accepted Answer · 2020-11-23 09:39:57Z

0

Finally i'm using this:

IQueryable<Customer> query =
           from c in _context.Customers.GroupBy(a => a.Code).Select(a => a.Max(a => a.ModificationDate))
           from s in _context.Customers
           where c == s.ModificationDate
           select s;

This works fine

answered Nov 23, 2020 at 9:39

Pablo

288 bronze badges

Collectives™ on Stack Overflow

C# Sql Raw Query to Entity Framework query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related