Budda Budda - 5 months ago 18x
SQL Question

How to create Linq2Sql query that will group records from linked table and calculate 2 count fields

Here I found how to join tables using Linq2Sql and count amount of linked records LINQ - Left Join, Group By, and Count

I've implemented it and it works ok for me: the following expression

var v = from c in db.GetTable<Country>()
join t0 in db.GetTable<Team>() on c.Id equals t0.CountryId into t1
from team in t1.DefaultIfEmpty()
group team by c.Id into teamsGrouped
select new CountryTeamsInfo
CountryId = teamsGrouped.Key,
TeamsTotal = teamsGrouped.Count(),
// TeamsWithoutOwnerFree = teamsGrouped.Count(t => t.OwnerId==0)
List<CountryTeamsInfo> res = v.ToList();

generates the following query:

SELECT c.Id, Count(*) as c1
FROM countries c
LEFT JOIN teams t1 ON c.Id = t1.Country

In fact I need also to count those linker records that has OwnerId field non-equal to 0.

It looks like I should just uncomment that line in linq expression (TeamsWithoutOwnerFree = teamsGrouped.Count(t => t.OwnerId==0)), but that doesn't work, attempt to execute that causes an error:

The given key was not present in the dictionary

The query don't come to SQL log file, and I can't examine it in the debugger.

What should be a proper way to count those lines from 'teams' table that meet additional criteria.

P.S. if it matters, I use C# 4.0, MySql 5.1 and BLToolkit 4.1


Here is query that works in my environment:

from c in db.GetTable<Country>()
where c.Allowed
select new CountryTeamsInfo
    CountryId = c.Id,
    TeamsTotal = db.GetTable<Team>().Count(t => t.CountryId == c.Id && t.Allowed),
    TeamsHasOwner = db.GetTable<Team>().Count(t => t.CountryId == c.Id && t.Allowed && t.OwnerId != 0),

Not the best solution (I need to repeat teams selection criteria t.Allowed in each sub-query), but still works well and generated SQL is good.