Solved

LINQ query troubles

Posted on 2009-05-05
4
705 Views
Last Modified: 2013-12-17
Experts,
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 M_Client 

 

    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 

 

LEFT OUTER JOIN M_Company 

 

	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 

 

LEFT OUTER JOIN LU_Group 

 

	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

0
Comment
Question by:Ken Fayal
  • 2
4 Comments
 
LVL 13

Expert Comment

by:copyPasteGhost
ID: 24314557
0
 
LVL 15

Accepted Solution

by:
mohan_sekar earned 500 total points
ID: 24318325
Linqer can help you write LINQ statements - download from http://www.sqltolinq.com/
0
 
LVL 9

Author Comment

by:Ken Fayal
ID: 24318445
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!
0
 
LVL 9

Author Comment

by:Ken Fayal
ID: 24318478
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.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now