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

Question

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
GROUP BY c.Id

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

Strange, it should work. What is the stack trace of the error? — Pavel Gatilov, Apr 18 '12 at 08:16

score 1 · Answer 1 · answered Apr 18 '12 at 11:01

1

Maybe try using GroupJoin() for your query:

var result = db.GetTable<Country>()
               .GroupJoin(db.GetTable<Team>(),
                   c => c.Id,
                   t => t.CountryId,
                   (country, teams) => new
                   {
                       CountryId = country.Id,
                       TeamsTotal = teams.Count(),
                       TeamsWithoutOwnerFree = teams.Count(t => t.OwnerId == 0)
                   })
               .ToList();

answered Apr 18 '12 at 11:01

RePierre

9,358
2
20
37

Thanks, will try. Could you please also advise "translation" from extension methods into linq2sql for the GroupJoin? Thank you – Budda Apr 18 '12 at 15:50
@Budda, I don't quite understand the question - what do you mean by "translation from extension methods into linq2sql"? – RePierre Apr 19 '12 at 07:20
I just wanted to write the same using Linq2Sql syntax (as in my original post), not syntax that is based on the ExtensionMethods, but that is not a big deal. – Budda Apr 19 '12 at 13:13
I've checked you suggestion and it looks like resolved execution problem. But the generated query still doesn't have component to calculate 'TeamsWithoutOwnerFree'... – Budda Apr 19 '12 at 13:15
it looks like I should write an additional "query" to fetch those teams that have OwnerId==0. Will try – Budda Apr 19 '12 at 13:19

score 0 · Answer 2 · answered Apr 19 '12 at 13:24

Due to RePierre help I was able to find the proper query:

var v = db.GetTable<Country>().Where(country => country.Allowed)
                    .GroupJoin(
                        db.GetTable<Team>(),
                        country => country.Id,
                        team => team.CountryId,
                        (country, teams) => new CountryTeamsInfo
                                                {
                                                    CountryId = country.Id,
                                                    TeamsTotal = teams.Count(),
                                                    TeamsWithoutOwnerFree = teams.Count(t => t.OwnerId != 0),
                                                }
                    ).GroupJoin(
                        db.GetTable<Team>().Where(te=>te.OwnerId==0),
                        cti => cti.CountryId,
                        team => team.CountryId,
                        (cti, teams) => new CountryTeamsInfo
                        {
                            CountryId = cti.CountryId,
                            TeamsTotal = cti.TeamsTotal,
                            TeamsWithoutOwnerFree = teams.Count(t => t.OwnerId != 0),
                        }
                    )
                    ;

My concern is it uses 2 sub-queries... will try to optimize that. Any ideas would be great

P.S. Actually, generated SQL query looks ugly as well:

SELECT
cti.Id as Id1,
cti.c1 as c11,
(
    SELECT
        Count(*)
    FROM
        teams te
    WHERE
        cti.Id = te.Country AND te.User = 0
) as c2
FROM
(
    SELECT
        country.Id,
        (
            SELECT
                Count(*)
            FROM
                teams t1
            WHERE
                country.Id = t1.Country
        ) as c1
    FROM
        countries country
    WHERE
        country.allow

You should not be concerned that it uses two subqueries because linq to sql uses defered execution - meaning that only one query goes to the database. From this point of view, you can split your queries however you like to maintain your coding style and improve readability. — RePierre, Apr 19 '12 at 14:50
if there's a parent-child relationship between `Countries` and `Teams` you could try this approach http://pastebin.com/KzG68fEP — RePierre, Apr 19 '12 at 15:07

score 0 · Accepted Answer · edited May 07 '16 at 04:47

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.

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

3 Answers3