将两个查询的左外连接转换为LINQ
本文关键字:连接 转换 LINQ 查询 两个 | 更新日期: 2023-09-27 18:11:36
我有下面的sql查询,我想转换成LINQ,以获得完全相同的结果,并返回下面的查询
select *
from (
select distinct DocID
from UserViewDoc
where UserViewDoc.UVID in (102558)) a
left outer join
(
select distinct UserViewDoc.DocID
from UserViewDoc
inner join UserViewHeader on UserViewDoc.UVID = UserViewHeader.UVID
where UserViewDoc.UVID not in (102558)
and UserViewHeader.IsLock = 1) b on a.DocID = b.DocID
where b.DocID is null
)
到目前为止我所尝试的是下面的LINQ语句
var v = (from uvd in this.ViewSelectorControl.LDReviewContext.GetTable<UserViewDoc>()
where IDs.Contains(uvd.UVID)
select new { uvd.DocID, uvd.UVID });
var c = ((from uvd in this.ViewSelectorControl.LDReviewContext.GetTable<UserViewDoc>()
join uvh in this.ViewSelectorControl.LDReviewContext.GetTable<UserViewHeader>() on uvd.UVID equals uvh.UVID
where !IDs.Contains(uvh.UVID) && uvh.IsLock == true
select new { uvd.DocID, uvd.UVID } ));
var d = (from id in v
join ids in c on id.UVID equals ids.UVID into vc
from sub in vc.DefaultIfEmpty()
where sub == null
select id);
我面临的问题是运行SQL查询返回30583条记录,它的LINQ版本返回所有30613条记录
我重写的查询是完全像sql查询,我相信这是方式:
var firstQuery = (from u in this.ViewSelectorControl.LDReviewContext.GetTable<UserViewDoc>()
where IDs.Contains(u.UVID)
select u.DocID).Distinct();
var innerQuery = (from u in this.ViewSelectorControl.LDReviewContext.GetTable<UserViewDoc>()
join uvh in this.ViewSelectorControl.LDReviewContext.GetTable<UserViewHeader>() on uvh.UVID equals u.UVID
where IDs.Contains(u.UIVD) == false
&& uvh.IsLock
select u.DocID).Distinct();
var resultQuery = from f in firstQuery
join i in innerQuery on i equals f into lout
from i in lout.DefaultIfEmpty()
where i == null
select f;