Not getting LEFT JOIN query in LINQ to pick up empty rows

Question

This is the SQL query I am trying to convert into a LINQ query:

SELECT
    ContactID,
    COUNT (ls.SoldToContactID) AS Count
FROM
    Contacts c
    LEFT OUTER JOIN LeadSales ls on c.ContactID = ls.SoldToContactID
GROUP BY 
    c.ContactID

I'm getting the results expected from leadSales, but I am not picking up any of the rows from contacts that have no match in leadSales.

var leadSales = (IQueryable of the LeadSales table)
var contacts = (IQueryable of the Contacts table)

BuyerList = from ls in leadSales
    join contact in contacts on ls.SoldToContactID equals contact.ContactID into sb
    from subBuyer in sb.DefaultIfEmpty()
        group ls by new {
            subBuyer.ContactID,
            FirstName = subBuyer.FirstName,
            LastName = subBuyer.LastName
        } into g
        select new LeadBuyersByStateItem
        {
            ContactID = g.Key.ContactID,
            Name = g.Key.LastName + ", " + g.Key.FirstName,
            LeadsCount = g.Count()
        };

EDIT:

Based on feedback from Ivan, I now have this:

BuyerList = from contact in contacts
                        join ls in leadSales on contact.ContactID equals   ls.SoldToContactID into c_ls
                        from ls in c_ls.DefaultIfEmpty()
                        group contact by new
                        {
                            ls.Contact.ContactID,
                            FirstName = ls.Contact.FirstName,
                            LastName = ls.Contact.LastName
                        } into g
                        select new LeadBuyersByStateItem
                        {
                            ContactID = g.Key.ContactID,
                            Name = g.Key.LastName + ", " + g.Key.FirstName,
                            LeadsCount = g.Count()
                        };

But, this is throwing this error:

The cast to value type 'System.Int32' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.

You don't give up :) I could close it for the 3rd time because it's still a duplicate of http://stackoverflow.com/questions/3404975/left-outer-join-in-linq. Instead, this time I'll give you a hint: `from c in contacts join ls in leadSales on c.ContactID equals ls.SoldToContactID into c_ls from ls in c_ls.DefaultIfEmpty() ...`. Hope you can manage it from there. — Ivan Stoev, Mar 15 '17 at 19:32
Possible duplicate of [LEFT OUTER JOIN in LINQ](http://stackoverflow.com/questions/3404975/left-outer-join-in-linq) — mrfreester, Mar 15 '17 at 19:35
@Ivan, I had already tried that after your last comment (the second time). Screw it. I'm just going to use the SQL and forget LINQ. — Casey Crookston, Mar 15 '17 at 19:43
@CaseyCrookston Hmm, it works usually. What ORM are you using? — Ivan Stoev, Mar 15 '17 at 19:44
Entity Framework. I was getting a null error -- don't recall now. I've moved on. Sometimes just using good ol fashioned SQL is so much easier that LINQ. — Casey Crookston, Mar 15 '17 at 19:46
Hopefully not EF Core? Because it's known to have currently problems with `left outer join`. Why don't you forget about the SQL query and joins, and use simply `from c in contacts select new LeadBuyersByStateItem { ContactId = c.ContactID, Name = c.LastName + ", " + c.FirstName, LeadsCount = leadSales.Count(ls => ls.SoldToContactID == c.ContactID) }`? Simple, and no way EF to make a mistake :) , — Ivan Stoev, Mar 15 '17 at 19:52
No, not core. I'll post what I have now, and the error it's throwing — Casey Crookston, Mar 15 '17 at 19:58
This worked!!! `from c in contacts select new LeadBuyersByStateItem { ContactId = c.ContactID, Name = c.LastName + ", " + c.FirstName, LeadsCount = leadSales.Count(ls => ls.SoldToContactID == c.ContactID) }` Wow that is so much simpler. Thank you! If you post as the answer I'll mark it as correct. — Casey Crookston, Mar 15 '17 at 20:06

score 3 · Accepted Answer · answered Mar 16 '17 at 10:10

The problem with the initial LINQ query is that you have exchanged the left and right parts, so it was equivalent of SQL LeadSales LEFT OUTER JOIN Contacts.

The problem with the updated query is that the group by clause is using ls.Contact, and since ls is the right end of the left outer join, it can be null. To match the SQL query, use contact variable (the left end of the join which is guaranteed to exist). Also note that SQL COUNT(ls.SoldToContactID) is excluding NULL values, so you have to account that in the LINQ query (there is no direct LINQ equivalent construct).

So the direct LINQ translation of the SQL query is like this:

from c in contacts
join ls in leadSales on c.ContactID equals ls.SoldToContactID into c_ls
from ls in c_ls.DefaultIfEmpty()
group ls by new
{
    c.ContactID,
    c.FirstName,
    c.LastName
} into g
select new LeadBuyersByStateItem
{
    ContactID = g.Key.ContactID,
    Name = g.Key.LastName + ", " + g.Key.FirstName,
    LeadsCount = g.Count(ls => ls != null)
};

But if you forget the SQL query and concentrate on the desired result, you'll see that there are simpler LINQ / EF versions of the same query.

The easiest is if you have navigation property from Contact to LeadSale (I've noticed you have from LeadSale to Contact) like

public ICollection<LeadSale> LeadSales { get; set; }

Then the query is simply:

from c in contacts
select new LeadBuyersByStateItem
{
    ContactID = c.ContactID,
    Name = c.LastName + ", " + c.FirstName,
    LeadsCount = c.LeadSales.Count()
};

If you don't have such property, you can use Count with condition:

from c in contacts
select new LeadBuyersByStateItem
{
    ContactID = c.ContactID,
    Name = c.LastName + ", " + c.FirstName,
    LeadsCount = leadSales.Count(ls => ls.SoldToContactID == c.ContactID)
};

or group join:

from c in contacts
join ls in leadSales on c.ContactID equals ls.SoldToContactID into c_ls
select new LeadBuyersByStateItem
{
    ContactID = c.ContactID,
    Name = c.LastName + ", " + c.FirstName,
    LeadsCount = c_ls.Count()
};

To recap, in all cases you should start the LINQ query with the required table.

Not getting LEFT JOIN query in LINQ to pick up empty rows

1 Answers1