Casey Crookston Casey Crookston - 12 days ago 6
SQL Question

LINQ: Having trouble navigating across multiple jeft join

What I'm trying to do in SQL looks like this:

SELECT
....
ct.FirstName + ' ' + ct.LastName
....
FROM
Leads l
LEFT JOIN LeadContacts lc ON lc.LeadID = l.LeadID
LEFT JOIN Contacts ct on ct.ContactID = lc.ContactID


In the Lead model:

public class Lead
{
....
public virtual ICollection<LeadContact> LeadContacts { get; set; }
....
}


And in the LeadContact model:

public class LeadContact
{
....
[ForeignKey(nameof(LeadID))]
public virtual Lead Lead { get; set; }

[ForeignKey(nameof(ContactID))]
public virtual Contact Contact { get; set; }
....
}


And now, I'm trying to build an object from an instance of:

leads = IQueryable<Lead>...


And for the life of me, I can't figure out how to navigate to the Contacts table.

var results = leads.Select(l => new QuoteSearchItem
{
....
SomeProperty = l.SomeProperty,
LeadSales = l.LeadContacts. ?????
SomeOtherProperty = l.SomeOtherProperty
....
});


QuoteSearchItem.LeadSales is a string. It needs to be:

Contacts.FirstName + " " + Contacts.LastName


Because of the relationship type, l.LeadContacts.Contacts is not an option.

What do I need to do so that this is possible?

Answer

You can do that easily if you use comprehension syntax (which use SelectMany when there are multiple froms):

var query = from l in Leads
            from lc in l.LeadContacts.DefaultIfEmpty()
            from ct in lc.Contacts.DefaultIfEmpty()
            select new
            {
               //....
               ContactName = ct.FirstName + ' ' + ct.LastName
               //....
            };

And if you wish, running this in LinqPad, you can get the lambda version + if that is Linq To SQL the SQL itself.

EDIT: Your class is implying that there is a single Contact per LeadContact, then you can shorten this:

var query = from l in Leads
            from lc in l.LeadContacts.DefaultIfEmpty()
            select new
            {
                //....
                ContactName = lc.Contact.FirstName + ' ' + lc.Contact.LastName
                //....
            };

It almost maps to this sample which use the Northwind sample database:

var data = from c in Customers
               from o in c.Orders.DefaultIfEmpty()
               select new {
                 CustomerId = c.CustomerID,
                 OrderId = (int?)o.OrderID,
                 Employee = o.Employee.FirstName + ' ' + o.Employee.LastName
               };

which yields this SQL:

-- Region Parameters
DECLARE @p0 NChar(1) = ' '
-- EndRegion
SELECT [t0].[CustomerID] AS [CustomerId], [t1].[OrderID] AS [OrderId], ([t2].[FirstName] + @p0) + [t2].[LastName] AS [Employee]
FROM [Customers] AS [t0]
LEFT OUTER JOIN [Orders] AS [t1] ON [t1].[CustomerID] = [t0].[CustomerID]
LEFT OUTER JOIN [Employees] AS [t2] ON [t2].[EmployeeID] = [t1].[EmployeeID]

EDIT: What I was saying, instead of:

var results = leads.Select(l => new QuoteSearchItem
{
    ....
    LeadSales = l.LeadContacts. ?????
    ....
});

Do it like:

var results = from l in leads 
              from lc in l.LeadContacts.DefaultIfEmpty() 
              select new  QuoteSearchItem
              {
                ....
                LeadSales = lc.Contact.FirstName + " " + lc.Contact.LastName
               ....
              };

Either in (method) lambda form or comprehension syntax, end result is same. When selectMany is needed I find comprehension syntax to be easier. And as I said, if you are dying for method syntax, try running it in LinqPad and it will give you lambda counterpart, something like:

var result = leads
   .SelectMany (
      l => l.LeadContacts.DefaultIfEmpty (), 
      (l, lc) => 
         new QuoteSearchItem
         {
            //... 
            LeadSales = lc.Contact.FirstName + " " + lc.Contact.LastName
         }
   );