CODEWITHSUNDEEP
sqllinq
shresthaal
shresthaal

Reputation: 685

how to do inner join and right outer join in the same query

I have these 3 tables:

  1. FeatureTbl (FeatureId (PK),FeatureName)
  2. ParameterTbl (ParameterId (PK),ParameterName)
  3. Map_Parameter_To_Feature (MapId (PK),FeatureId(FK),ParameterId(FK))

I want to convert the following SQL to LINQ:

SELECT 
    FeatureTbl.FeatureId,
    FeatureTbl.FeatureName,
    Map_Parameter_To_Feature.ParameterId,
    ParameterTbl.ParameterName
FROM ParameterTbl
INNER JOIN Map_Parameter_To_Feature
    ON ParameterTbl.ParameterId = Map_Parameter_To_Feature.ParameterId
RIGHT OUTER JOIN FeatureTbl
    ON Map_Parameter_To_Feature.FeatureId = FeatureTbl.FeatureId

The above query returns the following result

FeatureId,FeatureName,ParameterId,ParameterName
    1       Feat A      NULL        NULL
    2       Feat B       10         Param X
    3       Feat B       10         Param Y
    4       Feat C      NULL        NULL

I wrote the following LINQ:

(from p in context.ParameterTbls
join mp2f in context.Map_Parameter_To_Feature 
    on p.ParameterId equals mp2f.ParameterId
join f in context.FeatureTbls 
    on mp2f.FeatureId equals f.FeatureId
into desiredresult
from r in desiredresult.DefaultIfEmpty()
select new { 
    r.FeatureId,
    r.FeatureName, 
    mp2f.ParameterId, 
    p.ParameterName
});

But I get this result

FeatureId,FeatureName,ParameterId,ParameterName
    2       Feat B       10         Param X
    3       Feat B       10         Param Y

How can I convert the above SQL to LINQ?

Upvotes: 0

Views: 451

Answers (1)

MattW
MattW

Reputation: 4628

LINQ doesn't have a right join operator, but you can rewrite it as a left join:

from f in context.FeatureTbls
join mp in (
    from p in context.ParameterTbls
    join mp2f in context.Map_Parameter_To_Feature on p.ParameterId equals mp2f.ParameterId
    select new { mp2f.FeatureId, p.ParameterId, p.ParameterName }
) on f.FeatureId equals mp.FeatureId into ps
from p in ps.DefaultIfEmpty()
select new { f.FeatureId, f.FeatureName, null == p.ParameterId ? (int?)null : p.ParameterId, null == p.ParameterName ? null : p.ParameterName }

Upvotes: 1

Related Questions