How to add second JOIN clause in Linq To Sql?

Posted by Refracted Paladin on Stack Overflow See other posts from Stack Overflow or by Refracted Paladin
Published on 2010-04-28T17:15:26Z Indexed on 2010/04/28 17:23 UTC
Read the original article Hit count: 269

Filed under:
|
|
|
|

I am having a lot of trouble coming up with the Linq equivalent of this legacy stored procedure. The biggest hurdle is it doesn't seem to want to let me add a second 'clause' on the join with tblAddress. I am getting a Cannot resolve method... error.

Can anyone point out what I am doing wrong? Below is, first, the SPROC I need to convert and, second, my LINQ attempt so far; which is FULL OF FAIL!

Thanks

SELECT dbo.tblPersonInsuranceCoverage.PersonInsuranceCoverageID, 
    dbo.tblPersonInsuranceCoverage.EffectiveDate, 
    dbo.tblPersonInsuranceCoverage.ExpirationDate, 
    dbo.tblPersonInsuranceCoverage.Priority, 
    dbo.tblAdminInsuranceCompanyType.TypeName AS CoverageCategory, 
    dbo.tblBusiness.BusinessName, 
    dbo.tblAdminInsuranceType.TypeName AS TypeName,
    CASE WHEN dbo.tblAddress.AddressLine1 IS NULL THEN '' ELSE dbo.tblAddress.AddressLine1 END 
    + ' ' + 
    CASE WHEN dbo.tblAddress.CityName IS NULL THEN '' ELSE '<BR>' + dbo.tblAddress.CityName END 
    + ' ' + 
    CASE WHEN dbo.tblAddress.StateID IS NULL THEN '' 
         WHEN dbo.tblAddress.StateID = 'ns' THEN '' 
         ELSE dbo.tblAddress.StateID END AS Address
FROM      
    dbo.tblPersonInsuranceCoverage 
        LEFT OUTER JOIN dbo.tblInsuranceCompany 
            ON dbo.tblPersonInsuranceCoverage.InsuranceCompanyID = dbo.tblInsuranceCompany.InsuranceCompanyID 
                LEFT OUTER JOIN dbo.tblBusiness     
                    ON dbo.tblBusiness.BusinessID = dbo.tblInsuranceCompany.BusinessID 
                        LEFT OUTER JOIN dbo.tblAddress 
                            ON dbo.tblAddress.BusinessID = dbo.tblBusiness.BusinessID and tblAddress.AddressTypeID = 'b' 

        LEFT OUTER JOIN dbo.tblAdminInsuranceCompanyType 
            ON dbo.tblPersonInsuranceCoverage.InsuranceCompanyTypeID = dbo.tblAdminInsuranceCompanyType.InsuranceCompanyTypeID  

        LEFT OUTER JOIN dbo.tblAdminInsuranceType 
            ON dbo.tblPersonInsuranceCoverage.InsuranceTypeID = dbo.tblAdminInsuranceType.InsuranceTypeID   

WHERE tblPersonInsuranceCoverage.PersonID = @PersonID

var coverage = 
                from insuranceCoverage in context.tblPersonInsuranceCoverages
                where insuranceCoverage.PersonID == personID

                join insuranceCompany in context.tblInsuranceCompanies 
                    on insuranceCoverage.InsuranceCompanyID equals insuranceCompany.InsuranceCompanyID

                join address in context.tblAddresses 
                    on insuranceCompany.tblBusiness.BusinessID equals address.BusinessID
                    where address.AddressTypeID = 'b'

                select
                    new
                        {
                                insuranceCoverage.PersonInsuranceCoverageID,
                                insuranceCoverage.EffectiveDate,
                                insuranceCoverage.ExpirationDate,
                                insuranceCoverage.Priority,
                                CoverageCategory = insuranceCompany.tblAdminInsuranceCompanyType.TypeName,
                                insuranceCompany.tblBusiness.BusinessName,
                                TypeName = insuranceCoverage.InsuranceTypeID,
                                Address = 
                            };

© Stack Overflow or respective owner

Related posts about c#

Related posts about .NET