Solved

LINQ query troubles

Posted on 2009-05-05
4
706 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

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…
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 extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

808 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