Linq to Entities multiple table join

[davidsimmonds]

I have the same problem with multiple joins. Here is the query:

            Data data = (from scr in entities.SponsoredCharitableReportsSet
                         from company in entities.CompanySet.Where(c => c.ID == scr.CompanyID)
                         from user in entities.UserSet.Where(u => u.MemberID == scr.MemberID)
                         where scr.ID == DetailsID
                         orderby scr.DateRequested, company.CompanyName, user.FullName
                         select new Data
                         {
                             ID = (company == null ? 0 : company.ID),
                             DetailsID = scr.ID,
                             CompanyName = (company == null ? String.Empty : company.CompanyName),
                             Type = (company == null) ? String.Empty : company.Type,
                             WhoReceives = scr.WhoReceives,
                             RequestedCompanyName = scr.RequestedCompanyName,
                             DateRequested = scr.DateRequested,
                             RequestedCompanyID = scr.RequestedCompanyID,
                             AmountCharged = scr.AmountCharged,
                             InvoiceNumber = scr.InvoiceNumber,
                             Comments = scr.Comments,
                             FullName = (user == null ? String.Empty : user.FullName),
                             Phone = (user == null ? String.Empty : user.Phone),
                             MemberID = (user == null ? String.Empty : user.MemberID),
                             SendToBusinessMembers = scr.SendToBusinessMembers,
                             SendToResidentialMembers = scr.SendToResidentialMembers,
                             SendToAllMembers = scr.SendToAllMembers,
                             SendToIndividualMembers = scr.SendToIndividualMembers,
                             SendToNonMember = scr.SendToNonMember,
                             NonMemberEmail = scr.NonMemberEmail,
                             IndividualMembers = scr.SendToMembersList
                         }).First();

The SponsoredCharitableReportsSet table has a valid record. The CompanySet has a matching ID for the CompanyID in SponsoredCharitableReportsSet. The UserSet table DOES NOT have a record with a matching MemberID. I want all records from SponsoredCharitableReportsSet to come back regardless of a record in company or user. The query only returns a match if I comment out the "from user" line and any references to user. Why is this happening? There must be something wrong with my query that I dont understand.

[Ionelul]

you need to do a left join with both your tables(as you want all records no matter if there is a match in company or/and user tables). Here is an example (it is in VB, but the Linq query is the same).
Other solution would be to put an additional condition in the where clause

where (c => c.ID == scr.CompanyID || c.ID == null)

Ionut

[davidsimmonds]

Here is how I did it and it works pretty well. I knew that I needed a left outer join which is what I thought I had in my sample that I included. But it did not work. Here's the code I use.

           Data data = (from scr in entities.SponsoredCharitableReportsSet
                         let company = entities.CompanySet.Where(c => c.ID == scr.CompanyID).FirstOrDefault()
                         let user = entities.UserSet.Where(u => u.MemberID == scr.MemberID).FirstOrDefault()
                         where scr.ID == DetailsID
                         select new Data
                         {
                             ID = (company == null ? 0 : company.ID),
                             DetailsID = scr.ID,
                             CompanyName = (company == null ? String.Empty : company.CompanyName),
                             Type = (company == null) ? String.Empty : company.Type,
                             WhoReceives = scr.WhoReceives,
                             RequestedCompanyName = scr.RequestedCompanyName,
                             DateRequested = scr.DateRequested,
                             RequestedCompanyID = scr.RequestedCompanyID,
                             AmountCharged = scr.AmountCharged,
                             InvoiceNumber = scr.InvoiceNumber,
                             Comments = scr.Comments,
                             FullName = (user == null ? String.Empty : user.FullName),
                             Phone = (user == null ? String.Empty : user.Phone),
                             MemberID = (user == null ? String.Empty : user.MemberID),
                             SendToBusinessMembers = scr.SendToBusinessMembers,
                             SendToResidentialMembers = scr.SendToResidentialMembers,
                             SendToAllMembers = scr.SendToAllMembers,
                             SendToIndividualMembers = scr.SendToIndividualMembers,
                             SendToNonMember = scr.SendToNonMember,
                             NonMemberEmail = scr.NonMemberEmail,
                             IndividualMembers = scr.SendToMembersList
                         }).First();