Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

LINQ query troubles

Posted on 2009-05-05
4
Medium Priority
?
720 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 13

Expert Comment

by:copyPasteGhost
ID: 24314557
0
 
LVL 15

Accepted Solution

by:
mohan_sekar earned 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

604 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