CODEWITHSUNDEEP
c#linq
Youssef Khloufi
Youssef Khloufi

Reputation: 705

LINQ, first record in each group only if condition is met

I have this LINQ query to select the first record of each group:

repo.GroupBy(x => x.Revision, (key, g) => g.OrderBy(y => y.CreationDate).First())

Now I want to select the first record of each group only if the record meet the condition:

!x.Terminated

Obviously I can't append a Where to my GroupBy because it returns an Object and I want to be able to check the condition in my database without having to bring every first record of each group to memory and operate from there.

** Edit

A group of records that shares the same Revision in my design means that its the same register but edited many times. This way the record with the more recent creation date is the actual revision or version of the register and the rest of records in the group are history. My problem was that I was marking only the actual revision in a group as terminated so it was not possible to filter by the terminated feature before grouping ordering each group by creation date and selecting only the first record.

Now as a solution to my problem, I am going to mark the whole group as terminated every time I terminate a register so I can later filter the terminated records before grouping, ordering by date and selecting the first record of each group as suggested by @Jon Skeet and @Thomas Andreè Lian.

I will let the question without an accepted answer for a while maybe someone can suggest something else that allows to filter by the terminated feature after grouping, ordering and selecting the first record of each group.

Upvotes: 1

Views: 5405

Answers (2)

user6996876
user6996876

Reputation:

Linq methods, returning IQueryable<T>, are lazily evaluated and not materialized.

That means that you actually can add a where condition and the whole expression tree will be updated accordingly before the translation to SQL happens (by a DB provider).

You're missing an important aspect of the execution flow: the generated db command will be retrieved from the db only when you enumerate the collection.

Chaining a Where after a Group in Linq does not result in in-memory filtering but in a HAVING clause, exactly what you're asking.

The fluent version could be simplified as

repo.GroupBy(x => x.Revision)
.Select(x => x
   .OrderBy(y => y.CreationDate)
   .First())
.Where( x => !x.Terminated)

Or the query version should be 

var query = from rp in repo 
group rp by rp.Revision into grouped 
where !grouped.OrderBy(y => y.CreationDate).First().Terminated 
select grouped.OrderBy(y => y.CreationDate).First();

Of course it depends on the DB provider if it is able to translate the above query into SQL through the class mapping: it may be difficult indeed.

Upvotes: 2

Thomas Andre&#232; Wang
Thomas Andre&#232; Wang

Reputation: 3429

I would assume this would do the trick.

repo.Where( x => !x.Terminated)
        .GroupBy(x => x.Revision, (key, g) => g.OrderBy(y => y.CreationDate).First())

As i understand you want to eliminate all the Terminated elements anyway (might as well do it at the beginning).

Upvotes: 0

Related Questions