时间戳

首页 > 解决方案 > 如何在 C# 中重用查询

c# - 如何在 C# 中重用查询

问题描述

我有一个要重新使用的查询，因为在这两种方法中，连接发生的方式有所不同。

查询1：

await (from div in Context.ILevelDataItemValue
join pc in Context.Pc
on div.PCIdequals pc.Id
join f in Context.Fund
on div.FundId equals f.Id
join di in Context.ildt
on div.ildtId equals di.Id
join p in Context.vp
on div.PeriodId equals p.Id
**let v = (from v in Context.valwhere v.pcid== pc.Id orderby v.Id select v).DefaultIfEmpty().Last()**
where p.PeriodYear == year && p.PeriodQuarter == quarter
select new TblA
{
pcid = pc.Id,
vid= (int?)v.Id,
fid= f.Id,
PeriodId = p.Id,
ildtid= di.Id,
Value = div.Value                                                                                                 }).ToArrayAsync(cancellationToken: cancellationToken);

查询 2：

await (from div in Context.ILevelDataItemValue
join pc in Context.Pc
on div.PCIdequals pc.Id
join f in Context.Fund
on div.FundId equals f.Id
join di in Context.ildt
on div.ildtId equals di.Id
join p in Context.vp
on div.PeriodId equals p.Id
**join v in Context.val.Where(val => val.ValuationPeriod.PeriodQuarter == quarter && val.vp.PeriodYear == year).DefaultIfEmpty()
on pc.Id equals v.pcid into val
from v in val.DefaultIfEmpty()**
where p.PeriodYear == year && p.PeriodQuarter == quarter
select new TblA
{
pcid = pc.Id,
vid= (int?)v.Id,
fid= f.Id,
PeriodId = p.Id,
ildtid= di.Id,
Value = div.Value                                                                                                   }).ToArrayAsync(cancellationToken: cancellationToken);

除了一个加入和其他检索所有值之外，一切都保持不变，所以重用此查询的最佳方法是什么，只需将那些 join 语句替换为let v = (from v in Context.valwhere v.pcid== pc.Id orderby v.Id select v).DefaultIfEmpty().Last()

我已经在以 ** 和以 ** 结尾的查询中显示了上述差异

标签： c#.netlinq

解决方案

您必须将查询分为两个投影

首先，选择与所需加入无关的所有内容

var commonQuery = 
   from div in Context.ILevelDataItemValue
   join pc in Context.Pc on div.PCIdequals pc.Id
   join f in Context.Fund on div.FundId equals f.Id
   join di in Context.ildt on div.ildtId equals di.Id
   join p in Context.vp on div.PeriodId equals p.Id
   where p.PeriodYear == year && p.PeriodQuarter == quarter
   select new TblA
   {
       pcid = pc.Id,
       fid = f.Id,
       PeriodId = p.Id,
       ildtid = di.Id,
       Value = div.Value,                                                  
   }

然后，您可以重用此查询来检索额外id的 . 还更正了您的查询。

var firstQuery = 
  from q in commonQuery
  from v in Context.val.Where(v => v.pcid == q.pcid).OrderByDescending(v => v.Id).Take(1)
  select new TblA
  {
      pcid = q.pcid,
      fid = q.fId,
      PeriodId = q.PeriodId,
      ildtid = q.ildtid,
      Value = q.Value,  

      vid = (int?)v.Id                                                
   };

var fsecondQuery = 
  from q in commonQuery
  join v in Context.val.Where(val => val.ValuationPeriod.PeriodQuarter == quarter   
     && val.vp.PeriodYear == year)
  on q.pcid equals v.pcid into val
  from v in val.DefaultIfEmpty()
  select new TblA
  {
      pcid = q.pcid,
      fid = q.fId,
      PeriodId = q.PeriodId,
      ildtid = q.ildtid,
      Value = q.Value,  

      vid = (int?)v.Id                                                
   };

推荐阅读