• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 723
  • Last Modified:

LINQ query troubles

I have a TRANSACT SQL query defined which is working properly for me and I am having a heck of a time getting the LINQ query to match it. The SQL Query is first in the code window below followed by my LINQ query which doesn't work. THe SQL Query has nested JOINs.  I have read that nested JOINs don't make any difference in LINQ, but I'm starting to think otherwise.  Can anyone point out what's wrong with my LINQ query?

// SQL Query
SELECT     M_LOC_Policy_Detail.LOC_Policy_Detail_ID, M_Client.Client_ID, M_Client.Last_Name, LU_Client_Type.Client_Type, 
                      LU_Line_of_Coverage.LOC, M_LOC_Policy_Detail.Policy_Number, LU_Carrier.Carrier, LU_Carrier_1.Carrier AS TPA, M_LOC_Policy_Detail.Effective_Date, 
                      LU_Group.Group_Name, M_Company.Company_Name
FROM         LU_Carrier 
    INNER JOIN LU_Client_Type ON M_Client.Client_Type_ID = LU_Client_Type.Client_Type_ID 
    INNER JOIN M_Master_LOC ON M_Client.Client_ID = M_Master_LOC.Client_ID 
    INNER JOIN M_LOC_Policy_Detail ON M_Master_LOC.Master_LOC_ID = M_LOC_Policy_Detail.Master_LOC_ID 
    INNER JOIN LU_Line_of_Coverage ON M_Master_LOC.Line_of_Coverage_ID = LU_Line_of_Coverage.Line_of_Coverage_ID 
ON LU_Carrier.Carrier_ID = M_LOC_Policy_Detail.Carrier_ID 
INNER JOIN LU_Carrier AS LU_Carrier_1 ON M_LOC_Policy_Detail.Administrator_ID = LU_Carrier_1.Carrier_ID 
	INNER JOIN A_Company_Client ON M_Company.Company_ID = A_Company_Client.Company_ID 
ON M_Client.Client_ID = A_Company_Client.Client_ID 
	INNER JOIN A_Client_Group ON LU_Group.Group_ID = A_Client_Group.Group_ID 
ON M_Client.Client_ID = A_Client_Group.Client_ID
// LINQ Query (Doesn't work!)
           var policyQuery = from carrier in dc.LU_Carriers
                join policyDetail in dc.M_LOC_Policy_Details on carrier.Carrier_ID equals policyDetail.Carrier_ID
                join masterLOC in dc.M_Master_LOCs on policyDetail.Master_LOC_ID equals masterLOC.Master_LOC_ID
                join client in dc.M_Clients on masterLOC.Client_ID equals client.Client_ID
                join clientType in dc.LU_Client_Types on client.Client_Type_ID equals clientType.Client_Type_ID
                join lineOfCovg in dc.LU_Line_of_Coverages on masterLOC.Line_of_Coverage_ID equals lineOfCovg.Line_of_Coverage_ID
                join carrier1 in dc.LU_Carriers on policyDetail.Administrator_ID equals carrier1.Carrier_ID
                join companyClient in dc.A_Company_Clients on client.Client_ID equals companyClient.Client_ID into companyClients
                    from a in companyClients.DefaultIfEmpty()
                join company in dc.M_Companies on a.Company_ID equals company.Company_ID
                join clientGroup in dc.A_Client_Groups on client.Client_ID equals clientGroup.Client_ID into groupClients
                    from b in groupClients.DefaultIfEmpty()
                join groupList in dc.LU_Groups on b.Group_ID equals groupList.Group_ID
                select new
                    Policy_Detail_ID = policyDetail.LOC_Policy_Detail_ID,
                    Client_ID = client.Client_ID,
                    Client_LastName = client.Last_Name,
                    Client_Type = clientType.Client_Type,
                    Company_Name = company.Company_Name,
                    Group_Name = groupList.Group_Name,
                    Policy_Number = policyDetail.Policy_Number,
                    LOC = lineOfCovg.LOC,
                    Carrier = carrier.Carrier,
                    TPA = carrier1.Carrier,
                    Status = "Active",
                    PolicyDate = policyDetail.Effective_Date

Open in new window

Ken Fayal
Ken Fayal
  • 2
1 Solution
Linqer can help you write LINQ statements - download from http://www.sqltolinq.com/
Ken FayalCTOAuthor Commented:
The Linqer tool seems promising, but when I run it on my SQL statement it says

SQL cannot be converted to LINQ: Field [LU_Line_of_Coverage.Line_of_Coverage_ID ON LU_Carrier].[Carrier_ID = M_LOC_Policy_Detail.Carrier_ID] not found in the current Data Context.

I think it gets confused about the nested join on line 13 and 15.  However, I will put this in my toolbox!
Ken FayalCTOAuthor Commented:
After much trouble on this, I have decided to write this as a stored procedure and then call it using ISingleResult<> I've wasted too much time on this.

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now