Multiple Left Join LINQ-to-entities

Question

I have 3 tables:

Dealerships
------------
ID, Name, Website

Locations
------------
ID, DealershipID, Address, Ect.

Contacts
------------
ID, LocationID, Name, Ect.

So the relationship shows that we have dealerships who have multiple locations (Example: Weed Chevrolet of PA, Weed Chevrolet of NJ) and then each location has its own contacts (Example: Managers of PA location, Managers of NJ location). I need to join the 3 tables together. This is what I have:

var results = from d in entities.dealerships
              join l in entities.locations on d.ID equals l.DealershipID
              join c in entities.contacts on l.ID equals c.LocationID
              select new
              {
                  Name = d.Name,
                  Website = d.Website,
                  Address = l.Address + ", " + l.City + ", " + l.State + " " + l.Zip,
                  Contact = c.FirstName + " " + c.LastName,
                  WorkPhone = c.WorkPhone,
                  CellPhone = c.CellPhone,
                  HomePhone = c.HomePhone,
                  Email = c.Email,
                  AltEmail = c.AltEmail,
                  Sells = l.Sells
               }

When I attempt to bind results to a BindingSource and then to a DataGridView I receive the following error:

Unable to cast the type 'System.Nullable`1' to type 'System.Object'.
LINQ to Entities only supports casting Entity Data Model primitive types.

What can it be? I am new to JOIN statements in LINQ so I am sure I am doing something wrong.

EDIT: There is data in the database so the results shouldn't be null, just to clarify

Answer 1

var EmplistDriver = (from a in data
                     join b in db.DesignationDetails on a.DesignationID equals b.DesignationDetailID into EmployeeBonus
                     from b in dataBonus.DefaultIfEmpty()

                     join x in db.EmployeeCommission on a.EmployeeDetailID equals x.EmployeeDetailID into EmployeeCommission
                     from x in dataComm.DefaultIfEmpty()

                     join c in db.EmployeeAdvance on a.EmployeeDetailID equals c.FKEAEmployeeID

                     join d in db.EmployeeAllowance on a.EmployeeAllowanceID equals d.EmployeeAllowanceID
                     join e in dataAtt on a.EmployeeDetailID equals e.EmployeeDetailID

                     join f in dataDri on a.EmployeeDetailID equals f.EmployeeDetailID
                     join h in db.ProjectAllocation on f.FKAllocationID equals h.PKAllocationID

                     join i in db.ProjectDetails on h.FKProjectDetailID equals i.ProjectDetailID


                     where a.IsActive == true && c.EAIsActive == true && d.IsActive == true && e.EAIsActive == true && h.IsActivity == true

                     select new
                     {
                         c.BalanceAmount,
                         c.BalanceDue,
                         d.FoodAllowance,
                         i.DriverBasicSalary,
                         d.OtherAllowance,
                         d.AccommodationAllowance,
                         e.EABasicWorktime,
                         BonusAmount = (b.BonusAmount == null ? 0 : b.BonusAmount),
                         CommissionAmount = (x.CommissionAmount == null ? 0 : x.CommissionAmount),
                         TotalOverTime,
                         TotalHr

                     }).FirstOrDefault();

Answer 2

You were close but I discovered that you have to convert it from LINQ-To-Entities to LINQ-To-Objects. First I had to cast the entities using AsEnumerable() then use ToList(). This made it so I could use functions like ToString() and String.Format(). Thanks for leading me in the right direction. Here is the final code:

var query = from d in entities.dealerships
            from l in entities.locations.Where(loc => loc.DealershipID == d.ID).DefaultIfEmpty()
            from c in entities.contacts.Where(cont => cont.LocationID == l.ID).DefaultIfEmpty()
            where d.Keywords.Contains(keywords) || l.Keywords.Contains(keywords) || l.Sells.Contains(keywords) || c.Keywords.Contains(keywords)
            select new
            {
                Dealership = d,
                Location = l,
                Contact = c
            };

var results = (from r in query.AsEnumerable()
               select new
               {
                   Name = r.Dealership.Name,
                   Website = r.Dealership.Website,
                   Contact = r.Contact.FirstName + " " + r.Contact.LastName,
                   Address = r.Location.Address + ", " + r.Location.City + ", " + r.Location.State + " " + r.Location.Zip,
                   WorkPhone = r.Contact.WorkPhone,
                   CellPhone = r.Contact.CellPhone,
                   Fax = r.Contact.Fax,
                   Email = r.Contact.Email,
                   AltEmail = r.Contact.AltEmail,
                   Sells = r.Location.Sells
               }).ToList();

bindingSource.DataSource = results;

Answer 3

Since your results is IQueryable, EF will try to cast on the data store side and it won't work because cast only works with scalar types. You should call ToList() on the results like this:

var results = (from d in entities.dealerships
              join l in entities.locations on d.ID equals l.DealershipID
              join c in entities.contacts on l.ID equals c.LocationID
              select new
              {
                  Name = d.Name,
                  Website = d.Website,
                  Address = l.Address + ", " + l.City + ", " + l.State + " " + l.Zip,
                  Contact = c.FirstName + " " + c.LastName,
                  WorkPhone = c.WorkPhone,
                  CellPhone = c.CellPhone,
                  HomePhone = c.HomePhone,
                  Email = c.Email,
                  AltEmail = c.AltEmail,
                  Sells = l.Sells
               }).ToList();