Wrong Statement: Left Join and Grouping

[thomq]

I found the following mistake:

Sql Statement:
Select BenutzerBuNo.BuNo, COUNT(Bestellung.id) AS best
from BenutzerBuno left outer Join Bestellung
on BenutzerBuno.BuNo = Bestellung.BusinessNo
AND BenutzerBuNo.BenutzerID = Bestellung.BenutzerID
where benutzerBuno.BenutzerID = 11271
Group by BenutzerBuno.BuNo
ORDER BY benutzerbuno.BuNo

returns the following Linq Statement (VB):

From t In db.BenutzerBuNos _
Group Join t0 In db.Bestellungs _
On New With { t.BuNo, t.BenutzerID } _
Equals New With { .BuNo = t0.BusinessNo, t0.BenutzerID } Into t0_join = Group _
From t0 In t0_join.DefaultIfEmpty() _
Where _
CLng(t.BenutzerID) = 11271 _
Group t By t.BuNo Into g = Group _
Order By _
BuNo _
Select _
BuNo, _
best = CType(g.Count(),Int64?)


The SQL-Statement is counting the Joined Table ID's and the Result is 0 if this table is empty.

The LINQ Statement is counting the number of records and is returning a minimum of 1 even if the joined table is empty.

Cheers
Thomas

[Mikhail Oumantsev]

I understand the problem.
It would be helpful if you send me you designer.vb and dbml files.
[e-mail]