无法SQL查询转换为LINQ查询为左外连接 [英] Unable to convert SQL Query to LINQ Query for Left Outer Join

问题描述

问题陈述：

我想转换我的SQL到LINQ查询之一，但我无法得到所需的输出，我需要。任何人都可以建议我什么，我应该怎么办？

I'm trying to convert one of my Sql to linq query, but I'm unable to get the desired output which i need. Can anyone suggest me what i should do?

SQL查询：

SELECT AssetTagging.AssetID, AssetTagging.AssetDescription, [Return].RequestStatus
FROM  AssetTagging
LEFT OUTER JOIN [Return] ON AssetTagging.AssetID = [Return].AssetID
LEFT OUTER JOIN Issue ON AssetTagging.AssetID = Issue.AssetID
WHERE (Issue.AssetID IS NULL) OR ([Return].RequestStatus = 'Approved')

LINQ查询我使用的：

var result = (from at in db.AssetTagging.AsEnumerable()
                  join r in db.Return on at.AssetID equals r.AssetID
                  orderby at.AssetID
                  where !db.Issue.Any(issue=>issue.AssetID==at.AssetID) || r.RequestStatus=="Approved"
                  select new globalTestModel
                  {
                    model1=at
                  }).ToList();


//I know that in Linq query I'm using Inner join instead of Left Join,but i'm getting error if i use left join instead of inner join?

我在做什么错了？

What am I doing wrong??

的任何建议得到所需的如SQL查询中的LINQ？的

资产标签表：

发行表：

返回表：

所需的输出：

推荐答案

您需要做的是这样的：

 var result = from at in db.AssetTagging
              join r in db.Returns on at.AssetID equals r.AssetID into a
              from returns into a.DefaultIfEmpty()
              join i in db.Issues on at.AssetID equals I.AssetID into b
              from issues into b.DefaultIfEmpty()
              where issues.AssetID != null || returns.RequestStatus == "Approved"
              select new
                    {
                      AssetID = at.AssetID,
                      AssetDescription = at.AssetDescription,
                      Status = returns != null ? returns.RequestStatus : null
                    }.ToList();

这篇关于无法SQL查询转换为LINQ查询为左外连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！