selecting a distinct count from LINQ Stored Proc

Question

Hi there

I have a stored proc that returns a resultset thus:

testID(guid), testName, outcomeID(guid), outcomeName - fields

testGuid1, testName1, OutcomeGuid1, outcome1

testGuid1, testName1, OutcomeGuid2, outcome2

testGuid1, testName1, OutcomeGuid3, outcome3

testGuid1, testName1, OutcomeGuid4, outcome4

testGuid1, testName1, OutcomeGuid5, outcome5

testGuid2, testName2, OutcomeGuid9, outcome9

testGuid2, testName2, OutcomeGuid1, outcome1

testGuid2, testName2, OutcomeGuid3, outcome3

etc. so basically there are a number of tests which each have a number of outcomes, some of the outcomes will be common across the tests

what i would like to do is get a MAX count of outcomes across all tests, i would like to do this with some sort of lambda linq type thing. so for the above results i am looking for the number 5

if i do :

var Results = dc.getTests(id); 
//need the whole resultsset for binding to something in a mo.
//then would like to do somethign along the lines of:
int count = Results.GroupBy(t=>t.testID).count(*).Max() //or something?

if i debug i get (removing the count part of the above line) a IGrouping Linq thing which does have a non public count property, but i cant seem to get to it. i expect there is a better way of getting this number anyway. could someone enlighten me please

many thanks

nat

Answer 1

var max = Results.GroupBy(t=>t.testID).Max(grp => grp.Count());

However, note that personally I'd be looking for a way to do this at the DB, rather than fetching all that data over the network just to get a single number. A good option would be to push the code into a UDF, and change the SP so simply SELECT * FROM dbo.YourUdf(args) - the reason being that UDFs are composable, so you could map the UDF into the data-context and have:

var max = db.MyUdf(args).GroupBy(t=>t.testID).Max(grp => grp.Count());

(i.e. the same code), and it would do the work at the server, just bringing back a single number over the network.