LINQ to SQL: Grouping and limiting a record set

Question

Hello all,

I'm using LINQ to SQL and I have a stored procedure which brings back a result set that looks like so:

Type    Field1   Field2
 5        1        1
 6        2        0
21        0        0

I'm hoping to do a few things with this record set:

1) Have 3 groups of results, one that has values in both field1 and field2, one that has a value in field1 but not field2 and one that has zeros in field1 and field2.

2) I'm only interested in a subset of types. I have a list of type id's I'm looking for (say 5 and 21 for this example). Right now the type values are stored in an enumeration but I can move them to a better data type if appropriate.

I've gotten to where I can group each set but I'm unsure of how to limit the types so I only bring back those I'm interested in.

Here's what I have:

var result = from all in dataContext.sp(..variables...)
           group all by all into list
           let grp1 = (from a in list
                      where a.field1 != 0 && a.field2 != 0
                      select a)
           let grp2 = (from b in list
                     where b.field1 == 0 && b.field2 != 0
                     select b)
           let grp3 = (from c in list
                      where c.field1 == 0 && c.field2 == 0
                      select c)
           select new { grp1, grp2, grp3 };

Any help is appreciated.

Answer 1

Do you actually want all the data for those groups? If so, you might as well do the grouping back in .NET - just filter in SQL:

// Assuming interestingTypes is an array or list of the interesting types
var query = db.Whatever.Where(entry => interestingTypes.Contains(entry.Type)
                              // I understand you're not interested in this group
                              && !(entry.Field1==0 && entry.Field2==1));

var grouped = query.AsEnumerable()
                   .GroupBy(entry => new { entry.Field1, entry.Field2 });

An alternative to GroupBy is to use ToLookup:

var lookup = query.AsEnumerable()
                  .ToLookup(entry => new { entry.Field1, entry.Field2 });

Then:

var values00 = lookup[new { Field1=0, Field2=0 }];

(Then values00 will be an IEnumerable<T> of your entry type.)

If you're only interested in the types for each field group, you could change the lookup to:

var lookup = query.AsEnumerable()
                  .ToLookup(entry => new { entry.Field1, entry.Field2 },
                            entry => entry.Type);

You'd fetch values00 in the same way, but each entry would be the type rather than the whole record.

Answer 2

I dont think you will be able to do it in a single query (maybe, but not without it being ugly).

I would recommend storing the result of the stored proc, and just use 3 queries. Or modify the stored proc to return the 3 resultsets you are looking for.