C # LINQ Query (MYSQL EF) - Single and Recent Entries

Question

I have a table, let's call it Record. Contains: ID (int) | CustID (int) | Time (date and time) | Data (varchar)

I need the last (last) entry for each client:

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 causes an error:

System.NotSupportedException was not handled by Message = LINQ user code for entities does not recognize the method "Faizan_Kazi_Utils.Record LastOrDefault [Record] (System.Collections.Generic.IEnumerable`1 [Faizan_Kazi_Utils.Record]) ', and this method cannot be translated into repository 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 retrieves only the last (last member) of each group.

- LINQ, SQL?

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()
    }
);

, Linq → SQL Query, , . , . , , , , CustID select new. , SQL Tracer, . http://www.foliotek.com/devblog/tuning-sql-server-for-programmers/

:

- ? 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))

, , , .

Answer 2

, , , , , :

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

, grp.LastOrDefault(), #, , SQL . LINQ SQL-, db . , .

, , , LINQ to SQL , LINQ ( , ) #, IEnumerable/IQueryable grp. LastOrDefault().

Answer 4

, LastOrDefault() Last()? (, , )

Because I cannot understand how MySQL can return the "Default" group to you. This is not something that can simply be translated into SQL.

Answer 5

I had another idea:

// Get a list of all the id 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 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;