CODEWITHSUNDEEP
c#.netlinqsql-server-2008visual-studio-lightswitch
Oskar Eriksson
Oskar Eriksson

Reputation: 855

LINQ - filter duplicates with condition

I'm developing an .NET application where I need to filter data from SQL Server 2008 depending on a condition. My data structure looks like this:

enter image description here

The result should be grouped and sorted by Key. By default, the row with Revision null should be returned for each Key, but if revisionParameter is set the row with the corresponding Revision number should be returned. If the parameter is two, the output should be:

abc     2     FALSE
def    null   TRUE
ghj     2     FALSE
klm    null   TRUE

How can I accomplish this in LINQ? Thanks

EDIT: Response to lazyberezovsky: Your LINQ expression rewritten as lambda:

partial void RuleEntriesByUserSaveId_PreprocessQuery(int? UserSaveId, ref IQueryable<RuleEntry> query)
{
query = query.Where(re => re.Revision == null || re.Revision == value)
             .GroupBy(re => re.Key)
             .Select(g => g.FirstOrDefault(x => x.Revision != null) ?? g.First());
}

Upvotes: 4

Views: 1291

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236208

from r in Table
where !r.Revision.HasValue || r.Revision.Value == value
group r by r.Key into g
select g.FirstOrDefault(x => x.Revision.HasValue) ?? g.First()

That will select either records which have revision equal to null or to specified value. After grouping by key, we try to find any record which will have revision with value (that value will be equal to filter). If there is no records with revision, we just take first record from group.

Upvotes: 3

Related Questions