C# LINQ query (MYSQL EF) - Distinct and Latest Records

Question

I have a table, lets call it Record. Containing: ID (int) | CustID (int) | Time (datetime) | Data (varchar)

I need the latest (most recent) record for each customer:

SQL

select * from record as i group by i.custid having max(id);

LINQ version 1

dgvLatestDistinctRec.DataSource = from g in ee.Records
                                  group g by g.CustID into grp
                                  select grp.LastOrDefault();

This throws an error:

System.NotSupportedException was unhandled by user code Message=LINQ to Entities does not recognize the method 'Faizan_Kazi_Utils.Record LastOrDefault[Record ](System.Collections.Generic.IEnumerable`1[Faizan_Kazi_Utils.Record ])' method, and this method cannot be translated into a store expression. Source=System.Data.Entity

LINQ version 2

var list = (from g in ee.Records
            group g by g.CustID into grp
            select grp).ToList();
Record[] list2 = (from grp in list
                  select grp.LastOrDefault()).ToArray();
dgvLatestDistinctRec.DataSource = list2;

This works, but is inefficient because it loads ALL records from the database into memory and then extracts just the last (most recent member) of each group.

Is there any LINQ solution that approaches the efficiency and readability of the mentioned SQL solution?

Answer 1

Update:

var results = (from rec in Record group rec by rec.CustID into grp 
    select new
    {
        CustID = grp.Key,
        ID = grp.OrderByDescending(r => r.ID).Select(x => x.ID).FirstOrDefault(),
        Data = grp.OrderByDescending(r => r.ID).Select(x => x.Data).FirstOrDefault()
    }
);

So I made a test table and wrote a Linq -> SQL Query that will do exactly what you need. Take a look at this and let me know what you think. Only thing to keep in mind if this query is scaled I believe it will run a query to the DB for each and every CustID record after the grouping in the select new. The only way to be sure would be to run SQL Tracer when you run the query for info on that go here .. http://www.foliotek.com/devblog/tuning-sql-server-for-programmers/

Original:

Could you do something like this? from g in ee.Records where g.CustID == (from x in ee.Records where (g.CustID == x.CustID) && (g.ID == x.Max(ID)).Select(r => r.CustID))

That's all pseudo code but hopefully you get the idea.

Answer 2

I'm probably too late to help with your problem, but I had a similar issue and was able to get the desired results with a query like this:

from g in ee.Records
group g by g.CustID into grp
from last in (from custRec in grp where custRec.Id == grp.Max(cr => cr.Id) select custRec)
select last

Answer 3

What if you replace LastOrDefault() with simple Last()? (Yes, you will have to check your records table isn't empty)

Because I can't see a way how MySQL can return you "Default" group. This is not the thing that can be simply translated to SQL.