CODEWITHSUNDEEP
c#entity-frameworklinq
KJSR
KJSR

Reputation: 1757

Linq Get Latest record for a set of Values by its latest date entered

I am having issue figuring out how to filter a set of values from my Database table by the latest date entered

so far I have the following linq statement

List<int> vals = new List<int>{1, 2, 3, 4, 5};

Context.Table.Where(x => x.Col1 == col1 && vals.Contains(x.Col2)).OrderByDescending(x => x.DateEntered).Distinct().ToList();

It just seems to returning all rows? So basically for each vals items e.g. 1,2,3,4 it should return one row.

Any suggestions please?

Upvotes: 0

Views: 7845

Answers (3)

Hassan Raza
Hassan Raza

Reputation: 1

var checkIn = iclock_transaction
               .Where(x => x.punch_state == "0" )
               // .AsEnumerable()
               .GroupBy(x=>new { x.emp_code,x.punch_time.Date })
               // order by descending on the group
               // and then take the first
               .Select(grp => grp.OrderBy(x => x.punch_time).First())
               .ToList();

//********* Perfectly work with check out   ********
var checkOut = iclock_transaction
               .Where(x => x.punch_state == "1")
               // .AsEnumerable()
               .GroupBy(x => x.emp_code)
               // order by descending on the group
               // and then take the first
               .Select(grp => grp.OrderByDescending(x => x.punch_time).First())
               .ToList();

iclock_transaction = checkIn.Concat(checkOut).ToList();

Upvotes: 0

NetMage
NetMage

Reputation: 26907

What you need to do is group by the Col2 in vals so you can select the latest of each group. Using LINQ Lambda syntax:

List<int> vals = new List<int>{1, 2, 3, 4, 5};

var ans = Context.Table.Where(x => x.Col1 == Col1 && vals.Contains(x.Col2))
                       .GroupBy(x => x.Col2)
                       .Select(xg => xg.OrderByDescending(x => x.DateEntered).First())
                       .ToList();

Upvotes: 0

Camilo Terevinto
Camilo Terevinto

Reputation: 32068

If you want the latest for each record, you can use a GroupBy with a Select:

Context.Table
    .Where(x => x.Col1 == col1 && vals.Contains(x.Col2))
    .GroupBy(x => x.Col2)
    // order by descending on the group
    // and then take the first
    .Select(grp => grp.OrderByDescending(x => x.DateEntered).First())
    .ToList();

Upvotes: 2

Related Questions