How to add second JOIN clause in Linq To Sql?
- by Refracted Paladin
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 =
};
