具有多个连接的Linq查询

本文关键字：Linq 查询 连接 | 更新日期: 2023-09-27 18:07:33

我正在使用实体框架与我的DB工作，我需要帮助形成一个LINQ查询，这将帮助我获得列LoginId和AccNumber。我只需要1 LoginId为每一个AccNumber，不管它是哪个LoginId。就目前而言，有大约800K个loginid和大约3000个AccNumber。

因此，最后我需要1 AccNumber与1 LoginId相关联。所以我应该有~3000行和2列

这些是我需要连接的表:

<<p> 项/strong>

ItemId, AccNumber，其他不相关的列

Block_Item

ItemId, BlockId，其他不相关的列

Bookversion_Block

BookversionId, BlockId，其他不相关的列

Sudent

LoginId, BookversionId，其他不相关的列

理想情况下，我想使用LINQ复制这个SQL查询(只是试图提供尽可能多的信息)

select max(StudentId) as StudentId, BookletVersionId into #ST from Student
group by BookletVersionId
select #ST.BookletVersionId, LoginId into #T1 from #ST
join Student ST on #ST.StudentId = ST.StudentId
select BookletVersionId, BlockId into #BVB from BookletVersion_Block
where
                BookletVersionId in (select BookletVersionId from #ST)
select #T1.BookletVersionId, #BVB.BlockId, LoginId into #T2 from #T1
join BookletVersion_Block #BVB on #T1.BookletVersionId = #BVB.BookletVersionId
select max(BlockId) as BlockId, ItemId into #BI from Block_Item
where
                BlockId in (select BlockId from #T2)
group by ItemId
select BookletVersionId, #T2.BlockId, ItemId, LoginId into #T3 from #T2
join #BI on #T2.BlockId = #BI.BlockId
select max(LoginId) as LoginId, AccessionNumber from #T3
join Item I on #T3.ItemId = I.ItemId
group by AccessionNumber order by LoginId

这就是我所尝试的，然而，我得到的结果是不正确的，我得到了183,000条记录。很明显，因为我的LINQ查询是不正确的，这就是为什么我寻求帮助。

var q = (from items in context.Items
         join context.Block_Item
         on items.ItemId equals bi.ItemId into bi
         join context.BookletVersion_Block
         on bi.Select(x => x.BlockId).FirstOrDefault() equals BVB.BlockId into BVB
         join context.Students
         on BVB.Select(x => x.BookletVersionId).FirstOrDefault() equals st.BookletVersionId into st
          //'VH098334'
          select new { LoginId = st.Select(x => x.LoginId).FirstOrDefault().ToString(), AccNum = items.AccessionNumber.ToString() });

具有多个连接的Linq查询

您可以在select语句中使用FirstOrDefault的子查询，以获得LoginId 

var query =
   from items in context.Items
   select new 
   {
      AccNum = items.AccessionNumber,
      LoginId = (
         from bi in context.Block_Item  
         join bb in context.BookletVersion_Block on bi.BlockId equals bb.BlockId
         join st in context.Students on bb.BookversionId equals st.BookversionId
         where items.ItemId == bi.ItemId
         select st.LoginId
      ).FirstOrDefault()
   };
相关文章：