SQL to Linq translation with inner join and 2 left joins (with sub-query)

Question

Hi there! I have the following query which works fine in SSMS. Im using LinqPad (C#) but really puzzling to succeed with the left outer join in LinqToSql:

SELECT DISTINCT 
  A.LocID, 
  V1.PrfValue AS pID, 
  V2.PrfValue AS sID,
  D.DivisionManager, 
  A.IsApproved, 
  A.DateCreated
FROM         
  dbo.Locations AS A 
INNER JOIN
  dbo.Divisions AS D 
  ON A.DivisionID = D.DivisionID 
LEFT OUTER JOIN
  dbo.ValuesInLocations AS V1 
  ON A.LocID = V1.LocID 
 AND 
 V1.PrfID IN (SELECT 
   PrfID 
              FROM 
   dbo.PrfTag 
       WHERE 
   (LevelTypeID = 1)) 
LEFT OUTER JOIN
   dbo.ValuesInLocations AS V2 
   ON A.LocID = V2.LocID 
 AND 
 V2.PrfID IN (SELECT 
   PrfID 
       FROM 
   dbo.PrfTag 
       WHERE 
   (LevelTypeID = 2))

As you can see, this isn't the most elegant query to begin work, and I agree that the subquery in both left joins could be improved. However, could you please help me with this translation??

Answer 1

There may be some tricky way to do this in LINQ, but LINQ JOINs are painful for anything other than an inner join. For maintainability, I would discourage the use of linq for this query. I know this doesn't exactly answer your question, but I don't think you're going to get an answer you like that's any better than that query.

Answer 2

Hi,

Following are 2 possible translations of your query. I uses 3 separate queries in the first translation to make it more readable. I hope you find them useful.

var query1 =
    from prfTag in DataContext.PrfTag
    where prfTag.LevelTypeID = 1
    select PrfID;

var query1 =
    from prfTag in DataContext.PrfTag
    where prfTag.LevelTypeID = 2
    select PrfID;

var query = (
from A in DataContext.Locations
join D in DataContext.Divisions
    on A.DivisionID equals D.DivisionID
join V1 in DataContext.ValueInLocations
    on A.LocID equals V1.LocID
    into VGroup1
from V1 in VGroup1.DefaultIfEmpty()
join V2 in DataContext.ValueInLocations
    on A.LocID equals V2.LocID
    into VGroup2
from V2 in VGroup2.DefaultIfEmpty()
where (V1 == null || (V1 != null && query1.Contains(V1.PrfID)))
    && (V2 == null || (V2 != null && query2.Contains(V2.PrfID)))
select new
{
    A.LocID,
    pID = V1 != null ? V1.PrfValue : "",
    sID = V2 != null ? V2.PrfValue : "",
    D.DivisionManager,
    A.IsApproved,
    A.DateCreated
}).Distinct();

Here is the second possible translation:

var query = (
from A in DataContext.Locations
join D in DataContext.Divisions
    on A.DivisionID equals D.DivisionID
join V1 in DataContext.ValueInLocations
    on A.LocID equals V1.LocID
    into VGroup1
from V1 in VGroup1.DefaultIfEmpty()
join prfTag1 in DataContext.PrfTag
    on V1.PrfID equals prfTag1.PrfID
join V2 in DataContext.ValueInLocations
    on A.LocID equals V2.LocID
    into VGroup2
from V2 in VGroup2.DefaultIfEmpty()
join prfTag2 in DataContext.PrfTag
    on V2.PrfID equals prfTag2.PrfID
select new
{
    A.LocID,
    pID = V1 != null ? V1.PrfValue : "",
    sID = V2 != null ? V2.PrfValue : "",
    D.DivisionManager,
    A.IsApproved,
    A.DateCreated
}).Distinct();