LINQ to Entities - Multiple Joins on Same Table

Question

I currently have some stored procedures in T-SQL that I would like to translate to LINQ-To-Entities for greater maintainability. However, no matter how I seem to construct the LINQ query, when I inspect the generated code, it's an abhorrent terribly-performing monstrosity. I've investigated into various combinations of "let" clauses, joins, shifting around the "where" clause to both inside and out of the anonymous type selection, or even using the extension ".Where<>()" method piecemeal in the other indiciations themselves, and nothing seems to generate code anywhere as close to what I would expect or need.

The difficulties seem threefold:

The joins that I need to do are on combinations of booleans, whereas LINQ-To-Entities seems to only have join functionality for equijoins. How can I translate these joins?
I need to join on the same table multiple different times with different join/where clauses, so that I can select a different value for each record. How do I accomplish this in LINQ-To-Entities?
How do I prevent my joins on entity collections become nested messes?

The T-SQL query I'm attempting to translate is this (where the hardcoded numbers are specific hardcoded types):

SELECT Transport.*,
   [Address].Street1,
   Carrier1Insurance.InsuranceNumber,
   Carrier2Insurance.InsuranceNumber,
   Carrier3Insurance.InsuranceNumber
 FROM Transport
 INNER JOIN Appoint ON Transport.AppointKEY = Appoint.AppointKEY
 INNER JOIN Patient ON Appoint.PatientKEY = Patient.PatientKEY
 LEFT OUTER JOIN [Address] ON [Address].AddressFKEY = Patient.PatientKEY AND [Address].AddressTypeByTableKEY = 1
 LEFT OUTER JOIN PatientInsurance Carrier1Insurance ON Carrier1Insurance.PatientKEY = Patient.PatientKEY AND Carrier1Insurance.CarrierKEY = 7
 LEFT OUTER JOIN PatientInsurance Carrier2Insurance ON Carrier2Insurance.PatientKEY = Patient.PatientKEY AND Carrier2Insurance.CarrierKEY = 8
 LEFT OUTER JOIN PatientInsurance Carrier3Insurance ON Carrier3Insurance.PatientKEY = Patient.PatientKEY AND (Carrier3Insurance.CarrierKEY <> 7 AND Carrier3Insurance.CarrierKEY = 8)
 WHERE (Transport.TransportDate >= '07-01-2013' AND Transport.TransportDate <= '07-31-2013')
 AND EXISTS (SELECT TOP 1 *
              FROM Remit
              WHERE Remit.CarrierKEY = 8
                    AND Remit.AppointKEY = Transport.AppointKEY
                    AND Remit.PaidAmt > 0)

And the latest in many, many attempts at LINQ is this:

var medicareTransportList = from transportItem in ClientEDM.Transports
                        join patientAddress in ClientEDM.Addresses on transportItem.Appoint.PatientKEY equals patientAddress.AddressFKEY
                        join carrier1Insurance in ClientEDM.PatientInsurances on transportItem.Appoint.PatientKEY equals carrier1Insurance.PatientKEY
                        join carrier2Insurance in ClientEDM.PatientInsurances on transportItem.Appoint.PatientKEY equals carrier2Insurance.PatientKEY
                        join otherInsurance in ClientEDM.PatientInsurances on transportItem.Appoint.PatientKEY equals otherInsurance.PatientKEY
                        where (transportItem.TransportDate > fromDate ** transportItem.TransportDate <= toDate) && transportItem.Appoint.Remits.Any(remit => remit.CarrierKEY == 0 && remit.PaidAmt > 0.00M) &&
                                (carrier1Insurance.CarrierKEY == 7)  &&
                                (carrier2Insurance.CarrierKEY == 8 ) &&
                                (otherInsurance.CarrierKEY != 7 &&
                                otherInsurance.CarrierKEY != 8 ) &&
                                (patientAddress.AddressTypeByTableKEY == 1)
                        select new
                        {
                            transport = transportItem,
                            patient = patientAddress,
                            medicare = medicareInsurance,
                            medicaid = medicaidInsurance,
                            other = otherInsurance
                        };

What is wrong with your latest iteration? Also see http://stackoverflow.com/questions/3725032/greater-than-condition-in-linq-join for non equal joins. — Vulcronos, Jun 05 '14 at 17:15
Does it make sense to just load the entities hanging off of Transports on an as-needed basis instead, or do you really need all that data up front? — Eric Scherrer, Jun 05 '14 at 17:22
@Vulcronos the problem with the most recent iteration is it generates a SQL statement many hundreds of lines long that take >30 seconds to produce a result, whereas the in-SQL query is obviously just a few dozen and accomplishes the task in just a few. Additionally, the problem isn't so much that it's a non-equal join, but on multiple criteria. The additional "from" statement would generate a cross join for many thousands of rows. — Mejwell, Jun 05 '14 at 17:41
@EricScherrer I'm investigating this now, but as it is later in my program (too in-depth to go into here, I think) I "iterate" over the set and run queries on it, and not loading some this information up front from the SQL database means that it hits the database once for every one of the rows (many of thousands) in the result. — Mejwell, Jun 05 '14 at 17:42
How much data is in these tables, hundreds, thousands, or hundreds of thousands of records? — Eric Scherrer, Jun 05 '14 at 17:46
You can use multiple criteria in a join using anonymous types. See http://stackoverflow.com/questions/373541/how-to-do-joins-in-linq-on-multiple-fields-in-single-join Does your current row return the same data as the sql? — Vulcronos, Jun 05 '14 at 17:49
Hi everyone, I appreciate all the help, but it turns out that the performance issues were actually completely unrelated to this query hitting this database, and instead a problem in the business logic that I don't have access to, which means this question is no longer helpful to anyone. How should I handle this? Should I request to close? — Mejwell, Jun 05 '14 at 19:39

score 0 · Answer 1 · answered Jun 05 '14 at 17:58

The LINQ .Join() operator is equivalent to the INNER JOIN SQL operator.

For any other case, use the .GroupJoin() operator.

But do you really need to use join? In many case, using LINQ, SQL JOIN (inner or outer) can be expressed using navigation properties between entities.

Please explains your conceptual data model for a precise answer.

LINQ to Entities - Multiple Joins on Same Table

1 Answers1