CODEWITHSUNDEEP
c#.netlinqentity-frameworklinq-to-entities
dodjavola
dodjavola

Reputation: 177

What's wrong with my EF query?

I've got a huge problem. I can't figure out what's wrong with my LINQ query. Here is SQL query that gets the job done:

SELECT     TOP (100) PERCENT MIN(DateTime) AS DateTime, ETAG
FROM         dbo.Table
GROUP BY ETAG, LEFT(DateTime, 10)
ORDER BY ETAG

This query gives me about 50k results, when executed. I want the same in LINQ. Here is what I tried:

from d in Table
group d by new { d.ETAG, Date = d.DateTime.Value.Year + d.DateTime.Value.Month + d.DateTime.Value.Day } into g
orderby g.Key.ETAG
select g.OrderBy(e => e.DateTime).FirstOrDefault()

When I execute this, it takes few min to be done, and gives me about 9k results. Something is wrong, but I can't figure what. Please, help me get this right... Thanks in advance

Upvotes: 2

Views: 294

Answers (2)

mclark1129
mclark1129

Reputation: 7592

Using .NET 4 or higher, Entity Framework provides several utility functions specifically for date values. This may help you get over the hurdle with the first example and prevent you from having to eager load all rows before you group.

from d in Table
group d by new { d.ETAG, Date = EntityFunctions.TruncateTime(d.DateTime) } into g
orderby g.Key.ETAG
select g.OrderBy(e => e.DateTime).FirstOrDefault();

Edit:

Alternatively, if your aim is to group by just the date portion of the field you could try:

from d in Table
let dt = d.DateTime
group d by new { d.ETAG, y = dt.Year, m = dt.Month, d = dt.Day } into g
orderby g.Key.ETAG
select g.OrderBy(e => e.DateTime).FirstOrDefault();

Reference: http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/cb58ae5f-5db9-4054-92b6-a1bf63764574/

Upvotes: 0

Nathan Taylor
Nathan Taylor

Reputation: 24606

Based on your two queries, the group by in your LINQ statement is fundamentally different because you are not grouping by the actual date, but rather the integer sum of the Year, Month, and Day.

Instead, try this:

from d in Table
group d by new { d.ETAG, Date = date.DateTime.Value.Date } into g
orderby g.Key.ETAG
select g.OrderBy(e => e.DateTime).FirstOrDefault()

Edit

Unfortunately, due to the limitations of Entity Framework the above query will not work (even though it is valid in LINQ to SQL). In fact, there is no efficient parallel to the your SQL query using Entity Framework. The only option to obtain the output you want with a LINQ expression is to capture all of the records with a .ToList() prior to grouping and transforming them. Not ideal, but it will work.

from d in Table.ToList()
group d by new { d.ETAG, Date = date.DateTime.Value.Date } into g
orderby g.Key.ETAG
select g.OrderBy(e => e.DateTime).FirstOrDefault()

Upvotes: 1

Related Questions