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

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开发者_StackOverflow solution?

---

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.

---

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

---

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.

---

I think grp.LastOrDefault(), a C# function, is something that SQL doesn't know about. LINQ turns your query into an SQL query for your db server to understand. You might want to try and create an stored procedure instead, or another way to filter out what your looking for.

The reason your second query works is because the LINQ to SQL returns a list and then you do a LINQ query (to filter out what you need) on a C# list, which implements the IEnumerable/IQueryable interfaces and understands the grp.LastOrDefault().

---

I had another idea:

// Get a list of all the id's i need by:
// grouping by CustID, and then selecting Max ID from each group.
var distinctLatest = (from x in ee.Records
                          group x by x.CustID into grp 
                          select grp.Max(g => g.id)).ToArray();

// List<Record> result = new List<Record>();

//now we can retrieve individual records using the ID's retrieved above
// foreach (int i in distinctLatest)
// {
//    var res = from g in ee.Records where g.id == i select g;
//    var arr = res.ToArray();
//    result.Add(res.First());
// }

// alternate version of foreach
dgvLatestDistinctRec.DataSource = from g in ee.Records
                                           join i in distinctLatest
                                           on g.id equals i
                                           select g;