c# – 将两个查询的左外连接转换为LINQ

发布时间：2020-12-15 20:55:20 所属栏目：百科来源：网络整理

导读：我有下面的SQL查询,我想转换为LINQ以获得完全相同的结果,并通过下面的查询返回 select * from ( select distinct DocID from UserViewDoc where UserViewDoc.UVID in (102558)) a left outer join ( select distinct UserViewDoc.DocID from UserViewDoc inn

我有下面的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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!