Solved

LINQ query troubles

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

706 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

16 Experts available now in Live!

Get 1:1 Help Now