Left outer joins with Linq-To-Sql

Question

I want to use a linq-to-sql query which will use inner joins and left outer joins (using .DefaultIfEmpty()) but the query is not working. This is bugging me as its a simple task in T-SQL. here is my query :

  var results = from cy in _dc.Companies
                      join cyv in _dc.CompanyVersions
                          on cy.CompanyId equals cyv.CompanyId
                      join cyd in _dc.CompanyDetails
                          on cyv.CompanyVersionId equals cyd.Id
                      join cd in _dc.CustomerDetails
                          on cy.CompanyId equals cd.CompanyId
                      join d in _dc.Documents
                          on cd.CustomerId equals d.CustomerId
                      join di in _dc.DocumentItems
                          on d.DocumentId equals di.DocumentId
                      join dd in _dc.DocumentDetails
                          on di.DocDetailsId equals dd.DocumentDetailsId
                      join dt in _dc.DetailTypes
                          on dd.detailTypeId equals dt.detailTypeId
                      join vt in _dc.Vats
                          on dt.VATCode equals vt.VATCode
                      join v in _dc.Vouchers.DefaultIfEmpty()
                          on dd.DocumentDetailsId equals v.DocDetailID
                      join cc in _dc.CreditCards.DefaultIfEmpty()
                          on v.CCType equals cc.CCType
                      where
                          (d.DocTypeId == 1 || d.DocTypeId == 2) && (d.DocDate >= date)     
                        &&
                          (d.DocDate < date.AddDays(1)) &&
                          (d.CompanyVersionId == cyv.CompanyVersionId)
                          && (d.isDeleted == false || d.isDeleted == null)
                      orderby d.DocNumber ascending
                      select new
                                 {
                                     d.DocumentId,
                                     d.DocNumber,
                                     d.AutUser,
                                     d.GrossAmount,
                                     d.DocTypeId,
                                     cyd.Name,
                                     cyd.AddressLine1,
                                     cyd.AddressLine2,
                                     cyd.TelephoneLine,
                                     cyd.FaxLine,
                                     v.FromVoucNbr,
                                     d.DocDate,
                                     cd.CustomerName,
                                     cd.StreetAddress,
                                     cd.City,
                                     cd.Country,
                                     dt.DetDescr,
                                     cc.CCType,
                                     dd.FreeDescr,
                                     dd.NetAmount,
                                     dd.VATAmount,
                                     vt.VATDescr,
                                     cc.Description,
                                     vt.VATRate
                                 };

can anyone suggest how i can use left outer joins on the last 2 tables in this query?

Answer 1

yes the order of the joins was incorrect and i've fixed it now here is the working and compiled query:

P.s. thanks everyone for your help!

  var results =
            from cy in _dc.Companies
            join cyv in _dc.CompanyVersions on cy.CompanyId equals cyv.CompanyId
            join cyd in _dc.CompanyDetails on cyv.CompanyVersionId equals cyd.Id
            join cd in _dc.CustomerDetails on cy.CompanyId equals cd.CompanyId
            join d in _dc.Documents on cd.CustomerId equals d.CustomerId
            join di in _dc.DocumentItems on d.DocumentId equals di.DocumentId
            join dd in _dc.DocumentDetails on di.DocDetailsId equals    
            dd.DocumentDetailsId
            join dt in _dc.DetailTypes on dd.detailTypeId equals dt.detailTypeId
            join vt in _dc.Vats on dt.VATCode equals vt.VATCode
            join v in _dc.Vouchers on dd.DocumentDetailsId equals v.DocDetailID into 
            other1
            from o1 in other1.DefaultIfEmpty()
            join cc in _dc.CreditCards on o1.CCType equals cc.CCType into other2
            from o2 in other2.DefaultIfEmpty()
            where (d.DocTypeId == 1 || d.DocTypeId == 2)
                  && (d.DocDate >= date)
                  && (d.DocDate < date.AddDays(1))
                  && (d.CompanyVersionId == cyv.CompanyVersionId)
                  && (d.isDeleted == false || d.isDeleted == null)
            orderby d.DocNumber ascending
            select new
                       {
                           d.DocumentId,
                           d.DocNumber,
                           d.AutUser,
                           d.GrossAmount,
                           d.DocTypeId,
                           cyd.Name,
                           cyd.AddressLine1,
                           cyd.AddressLine2,
                           cyd.TelephoneLine,
                           cyd.FaxLine,
                           o1.FromVoucNbr,
                           d.DocDate,
                           cd.CustomerName,
                           cd.StreetAddress,
                           cd.City,
                           cd.Country,
                           dt.DetDescr,
                           o2.CCType,
                           dd.FreeDescr,
                           dd.NetAmount,
                           dd.VATAmount,
                           vt.VATDescr,
                           o2.Description,
                           vt.VATRate
                       };

Answer 2

You placed both .DefaultIfEmpty wrong. I think it is something like this (although I'm not sure about the where clause..).

var results = 
from cy in _dc.Companies
  join cyv in _dc.CompanyVersions on cy.CompanyId equals cyv.CompanyId
  join cyd in _dc.CompanyDetails on cyv.CompanyVersionId equals cyd.Id
  join cd in _dc.CustomerDetails on cy.CompanyId equals cd.CompanyId
  join d in _dc.Documents on cd.CustomerId equals d.CustomerId
  join di in _dc.DocumentItems on d.DocumentId equals di.DocumentId
  join dd in _dc.DocumentDetails on di.DocDetailsId equals dd.DocumentDetailsId
  join dt in _dc.DetailTypes on dd.detailTypeId equals dt.detailTypeId
  join vt in _dc.Vats on dt.VATCode equals vt.VATCode
  join v in _dc.Vouchers on dd.DocumentDetailsId equals v.DocDetailID into other1
  from o1 in other1.DefaultIfEmpty()
  join cc in _dc.CreditCards on v.CCType equals cc.CCType into other2
  from o2 in other2.DefautlIfEmtpy() 
  where (d.DocTypeId == 1 || d.DocTypeId == 2) 
                       && (d.DocDate >= date)     
                       && (d.DocDate < date.AddDays(1)) 
                       && (d.CompanyVersionId == cyv.CompanyVersionId)
                       && (d.isDeleted == false || d.isDeleted == null)
  orderby d.DocNumber ascending
  select new
            {
                d.DocumentId,
                d.DocNumber,
                d.AutUser,
                d.GrossAmount,
                d.DocTypeId,
                cyd.Name,
                cyd.AddressLine1,
                cyd.AddressLine2,
                cyd.TelephoneLine,
                cyd.FaxLine,
                o1.FromVoucNbr,
                d.DocDate,
                cd.CustomerName,
                cd.StreetAddress,
                cd.City,
                cd.Country,
                dt.DetDescr,
                o2.CCType,
                dd.FreeDescr,
                dd.NetAmount,
                dd.VATAmount,
                vt.VATDescr,
                o2.Description,
                vt.VATRate
            };