4

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());
}
Oskar Eriksson
  • 845
  • 1
  • 9
  • 28

1 Answers1

3
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.

Sergey Berezovskiy
  • 232,247
  • 41
  • 429
  • 459
  • Thanks for your answer. I'm actually doing a LightSwitch application where the simplest thing to do is write it as lambda. Therefore I rewrote your code, tho I didn't get it to work. Does my rewritten code look ok? – Oskar Eriksson Apr 16 '13 at 12:23
  • It's also more native-feeling than he integrated query language; sometimes reminds me of the awful inline-XML integration with VB.NET. – Grant Thomas Apr 16 '13 at 13:06
  • @OskarEriksson your query looks fine for me (I'd used `HaValue` property instead of comparing with null). What's exactly not working? Exception? – Sergey Berezovskiy Apr 16 '13 at 15:11
  • @lazyberezovsky It seems that the problem isn't the query itself, LightSwitch doesn't support "group by" out of the box hence it would require a lot of hassle to get that work. Are there any way to solve my problem without a "group by"? Really appreciate your help! – Oskar Eriksson Apr 17 '13 at 08:20
  • @OskarEriksson unfortunately I haven't used LightSwitch yet, but looks like grouping is a well-known problem. Take a look on [LightSwitch Team Blog](http://blogs.msdn.com/b/lightswitch/archive/2011/04/08/how-do-i-display-a-chart-built-on-aggregated-data-eric-erhardt.aspx) for solving this issue – Sergey Berezovskiy Apr 17 '13 at 08:58
  • @lazyberezovsky Yes I have seen all those guides. You don't think it's possible to rewrite the query without using "group by"? – Oskar Eriksson Apr 17 '13 at 09:01
  • @OskarEriksson you can move all calculations into memory by calling `Table.AsEnumerable()` or `Table.ToList()`. That will be slow (if you have many data), but aggregation should work – Sergey Berezovskiy Apr 17 '13 at 09:40
  • 1
    Your query is correct, however I an error saying that i can't user First() if it's not a final operation. So I changed g.First() to g.FirstOrDefault(). – Oskar Eriksson Apr 19 '13 at 12:19
  • @OskarEriksson yes, you did a right thing - there is no difference in this case, because each group will have at least one item – Sergey Berezovskiy Apr 19 '13 at 12:34