Linq to sql where offer and count

Question

I have a linq-to-sql query that I use to populate an object that has ints as properties and whose function is to hold count.

The request looks like this (at the moment)

var TheQuery = from..... where .... in thefilteredresults select new MyModel() { Total = thefilteredresults.Count(), TotalTime = (from x in thefilteredresults where x.Outcome == 4).Sum(t => t) }.Single(); 

At first I have some filtering of the problems, and then a calculation based on the filtered results of the original where clause. What do i need to change?

Thanks.

Answer 1

You can hack this together:

 (from x in table where filter(x) group x by 0 into g select new { Count = (int?)g.Count() ?? 0, Sum = (int?)g.Sum(x => x.Value) ?? 0 }).Single() 

SQL Server optimizes unnecessary grouping. It is probably best to document why you wrote it like this.

Edit: I included the weird look in the int. The reason for this is to tell Linq to SQL to assume that the value is NULL and use the COALELCE function call to convert NULL to 0. This is a hack that should also be documented.

Answer 2

Since you are really only looking for one result, this is probably the easiest way:

 var filteredResults = from .... where .... select ....; var myModel = new MyModel{ Total = filteredResults.Count(), ... };