How to get rows by LINQ

Question

Everybody.

I have following data.

DATE|COLUMN1|COLUMN2|VALUE
--------------------------
1   |A      |D      |100
--------------------------
2   |A      |D      |200
--------------------------
1   |B      |D      |150

I need get rows where DATE does have max value. Like that

DATE|COLUMN1|COLUMN2|VALUE
--------------------------
2   |A      |D      |50
--------------------------
1   |B      |D      |150

Sorry for my bad English.

Possible duplicate of [How to select only the records with the highest date in LINQ](http://stackoverflow.com/questions/470440/how-to-select-only-the-records-with-the-highest-date-in-linq) — Mladen Oršolić, Nov 24 '16 at 12:38

score 1 · Answer 1 · answered Nov 24 '16 at 13:31

1

As I can understand, you need max date for every group of column1 -

for that you can go with

var custsLastAccess = from c in db.data //data is your table
                      group c by c.COLUMN1 into grp
                      select grp.OrderByDescending(c => c.DATE).FirstOrDefault();

answered Nov 24 '16 at 13:31

Viplock

3,259
1
23
32

No, because I lost association with other tables in database. – Adil Nov 24 '16 at 13:46
ok ! for doing this kind of data fetching you have to do group by thing , and if you wanna join with other data table, you should create a class and convert the data into that type. and use it for other usage. Like joining with other tables – Viplock Nov 24 '16 at 13:54
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/128953/discussion-between-viplock-and-adil). – Viplock Nov 24 '16 at 13:56
check the answer of http://stackoverflow.com/questions/9173410/linq-combining-join-and-group-by question – Viplock Nov 24 '16 at 13:58

Tatranskymedved · Answer 2 · 2016-11-24T13:15:00.300

0

You can first select the max from the LINQ and then select by this max:

var max = rows.Max(a => a.DATE);
var result = rows.Where(a => a.DATE == max).ToList();

Simplier:

var result = rows.Where(a => a.DATE == rows.Max(b => b.DATE)).ToList();

Optimizer/compiler should optimize the query above.

Expression:

var result = from r in rows
       where r.DATE == rows.Max(a => a.DATE)
       select r;

edited Nov 24 '16 at 13:15

answered Nov 24 '16 at 13:06

Tatranskymedved

4,194
3
21
47

score 0 · Answer 3 · answered Nov 24 '16 at 19:25

I found solve. The following code decided mine task.

    public override IQueryable<ENTITY> Get()
    {
        return base.Get().GroupBy(x => new { x.COLUMN1, x.COLUMN2 }, (key, group) => new
        {
            row = group.OrderByDescending(x => x.DATE).FirstOrDefault()
        }).Select(x => x.row);
    }

How to get rows by LINQ

3 Answers3