LINQ2SQL Doesn’t return the same result as T-SQL…

Question

I’ve the following tables:

Paciente -> PacienteTag -> Tag

The typical N:M between Paciente and Tag with an intermediary table.

I want to obtain how many patients have each tag. Simple:

SELECT Tag.TagName, COUNT(PacienteTag.TagId) AS Totals  FROM Tag
LEFT JOIN PacienteTag ON Tag.TagId = PacienteTag.TagId
GROUP BY Tag.TagName
ORDER BY Tag.TagName

The result is a simple table like this:

Tag1, 0
Tag2, 0
Tag3, 0
Tag4, 2
Tag5, 0
Etc…

However when I created my LINQ2SQL(given my limited LINQ2SQL knowledge) version of that, the results came like:

Tag1, 1
Tag2, 1
Tag3, 1
Tag4, 2
Tag5, 1
Etc…

Puzzled, but more or less understanding what LINQ was doing, I downloaded Linqer and told it to translate the SQL Expression for me.

It came up with this:

from t in db.Tag
join t0 in db.PacienteTag on t.TagId equals t0.TagId into t0_join
from t0 in t0_join.DefaultIfEmpty()
group t by new {
  t.TagName
} into g
orderby
  g.Key.TagName
select new {
  g.Key.TagName,
  Totals = (Int64?)g.Count()
}

Which is not far from my approach. But surprisingly, that also comes back with the same wrong results!

I can see where the error “is” but I cannot come with an alternative.

WHat am I doing wrong?

NOTE: in the above example, the table PacienteTag has two records that associate Tag4 with two random patients, my T-SQL is returning the right values.

Ideas?

Answer 1

Assuming that your relationships are set up correctly and there is a reference to PacienteTags from Tag called PacienteTags, the following should work.

from t in db.Tag orderby t.TagName 
select new { t.TagName, Totals = g.PacienteTags.Count() };

I've found that the need to join in LINQ to (anything) is pretty rare... just utilize the generated references to get at data in other tables (or entities if using EF).