How to filter entity framework result with multiple columns using a lambda expression

Question

I have the following table:

enter image description here

And the following data:

enter image description here

How can i filter the result, so that i only get the latest row from each omraade_id (sorted descending by timestamp)?

Which in this case would be the rows with id: 1010 and 1005

--

From @lazyberezovsky's answer, i have created the following expression:

dbConnection = new ElecEntities();

            var query = from data in dbConnection.Valgdata
            orderby data.timestamp descending
            group data by data.omraade_id into g
            select g.FirstOrDefault();

            return query.ToList();

It returns two rows with the ID 3 and 4, which are the first two rows in the database, and also the ones with the lowest timestamp. Any idea why?

Eren Ersönmez · Accepted Answer · 2012-11-12 16:18:52Z

2

var query = dbConnection.Valgdata
                        .GroupBy(x => x.omraade_id)
                        .Select(g => g
                             .OrderByDescending(x => x.timestamp)
                             .FirstOrDefault());

edited Nov 12, 2012 at 16:18

answered Nov 12, 2012 at 14:12

Eren Ersönmez

39.2k8 gold badges76 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Kenci Over a year ago

Thanks for trying. This returns all 11 rows (returns row=3 9 times and row=4 2 times) ?

Kenci Over a year ago

Thanks again. I appreciate the effort. This returns a Grouping object with two keys. The first key contains all the rows from the first omraade_id, and the second key contains all the rows from the second omraade_id (not sorted).

Eren Ersönmez Over a year ago

@Kenci I just tried and it worked fine for me. Are you sure you have the all parenthesis in the right place?

Kenci Over a year ago

I have copy-pasted your answer. I have uploaded an image of what i get here: img202.imageshack.us/img202/6407/dbgroupquery.png

Eren Ersönmez Over a year ago

@Kenci you're right, I'm sorry. Apparently, I made a mistake while trying to convert what I have to the Linq syntax. See my update.

|

nawfal · Accepted Answer · 2012-11-12 15:08:00Z

1

I have no experience with EF, so I'm unsure if only SQL-esque linq works here. A plain C#-ish:

var query = dbConnection.Valgdata.GroupBy(u => u.omraade_id)
      .Select(x => x.FirstOrDefault(y => x.Max(p => p.timestamp) == y.timestamp));

edited Nov 12, 2012 at 15:08

answered Nov 12, 2012 at 14:45

nawfal

73.8k59 gold badges342 silver badges379 bronze badges

2 Comments

nawfal Over a year ago

@Kenci why is it FirstOrDefault? First works in all cases! Because you will always have a maximum timestamp no matter what.

Kenci Over a year ago

The timestamp column is nullable, and it throws an error when i execute the query with First() - but i should probably change this :)

Tilak · Accepted Answer · 2012-11-12 14:05:45Z

0

You have put filter on every item. It should be applied on complete query result, not on every item.

Following is updated query.

 var query = (from data in dbConnection.Valgdata
        orderby data.timestamp descending
        group data by data.omraade_id into g
        select g).FirstOrDefault();

answered Nov 12, 2012 at 14:05

Tilak

30.8k19 gold badges85 silver badges133 bronze badges

1 Comment

Kenci Over a year ago

Thanks for the answer. I have tried your suggestion, it returns 9 results (all the rows with id = 6)

Sergey Berezovskiy · Accepted Answer · 2012-11-12 14:35:03Z

0

var query = from v in dbConnection.Valgdata
            orderby v.timestamp descending
            group v by v.omraade_id into g
            select g.First();

This will return only record with max timestamp for each omraade_id.

UPDATE query above works fine to me (at least for MS SQL Linq provider). Also you don't need to do FirstOrDefault - if omraade_id is grouped, then it definitely has at least one row.

var query = from v in dbConnection.Valgdata
            group v by v.omraade_id into g
            select g.OrderByDesc(x => x.timestamp).First();

edited Nov 12, 2012 at 14:35

answered Nov 12, 2012 at 13:46

Sergey Berezovskiy

237k44 gold badges441 silver badges468 bronze badges

2 Comments

Kenci Over a year ago

It returns the same if i write descending or ascending

Sergey Berezovskiy Over a year ago

@Kenci see updated answer. Maybe its linq provider specific issue. Try to order records after grouping. That definitely should work.

Kenci · Accepted Answer · 2012-11-12 14:36:20Z

0

This is my solution so far:

var data = dbConnection.Valgdata.Where(x => x.godkendt == false).ToList();
var dataGrouped = data.GroupBy(x => x.omraade_id).ToList();

List<Valgdata> list = new List<Valgdata>();

 foreach (var grpdata in dataGrouped)
            {
                var dataGroup = grpdata.OrderByDescending(x => x.timestamp).ToList();
                list.Add(dataGroup.FirstOrDefault());
            }
return list;

I dont know if it is the most effective, but it works.

answered Nov 12, 2012 at 14:36

Kenci

4,90216 gold badges70 silver badges113 bronze badges

Collectives™ on Stack Overflow

How to filter entity framework result with multiple columns using a lambda expression

5 Answers 5

8 Comments

2 Comments

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

8 Comments

2 Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related