LINQ delima将sql转换为LINQ [英] LINQ delima to convert sql into LINQ

问题描述

我是linq的新手，所以如果这是一个愚蠢的问题，我先向您道歉.我继承了以下查询，但为了解决它，它没有产生正确的结果，我必须了解它在做什么. 这是查询.

I am new to linq so I apologize in advance if it a dumb question. I inherited the following query and it is not producing correct results in order to fix it I have to understand what it is doing. Here is the query.

  using (var dbCtx = new TLMDbContext())
  {
    var dvps = dbCtx.tblDVPTests.Where(x => x.DVPID == 2176);
// these 2 following if conditions doesnt bring correct result sets
    if (dvpMasterPhaseId.HasValue)
    {
       dvps = dvps.Where(x => x.tblDVPPhases.All(p => p.DVPMasterPhaseID ==255));
    }
    if (dvpMasterVariantId.HasValue)
    {
       dvps = dvps.Where(x => x.tblDVPPhases.All(p => p.tblDVPVariants.All(v=>v.DVPMasterVariantID==681)));
    }
}

更新

我希望下面的查询用LINQ编写，如果你们中的一个告诉我上面的LINQ查询出了什么问题，那将很好.

I want this following query to be written in LINQ and if one of you guys tell me what was wrong with the LINQ query above that will be great.

以下表情

dvps = dvps.Where(x => x.tblDVPPhases.All(p => p.DVPMasterPhaseID == 255));

dvps = dvps.Where(x => x.tblDVPPhases.All(p => p.DVPMasterPhaseID ==255));

应等同于此SQL查询.

select * from tblDVPTest 
inner join tblDVPPhase on tblDVPTest.DVPTestID=tblDVPPhase.DVPTestID
where  dvpid=2176 and tblDVPPhase.DVPMasterPhaseID=255

这是两种情况 ** LINQ **

Here is with both condition ** LINQ**

dvps = dvps.Where(x => x.tblDVPPhases.All(p => p.tblDVPVariants.All(v => v.DVPMasterVariantID == 681)));

dvps = dvps.Where(x => x.tblDVPPhases.All(p => p.tblDVPVariants.All(v=>v.DVPMasterVariantID==681)));

应等同于此SQL查询.

select * from tblDVPTest 
inner join tblDVPPhase on tblDVPTest.DVPTestID=tblDVPPhase.DVPTestID
inner join tblDVPVariant on tblDVPPhase.DVPPhaseID=tblDVPVariant.DVPPhaseID
where  dvpid=2176 and tblDVPPhase.DVPMasterPhaseID=255 and tblDVPVariant.DVPMasterVariantID=681

推荐答案

此代码向原始查询添加了两个条件过滤器(在其中).每个额外的过滤器都会限制结果中包含的项目.

This code is adding two conditional filters (where) to the original query. Each extra filter limits the items included in the result.

如果变量dvpMasterPhaseId不为空，则查询仅包含所有相关DPVPhases的dvpMasterVariantId为255的项目

In case the variable dvpMasterPhaseId is non-null the query only includes the items for which All related DPVPhases have a dvpMasterVariantId of 255

如果变量dvpMasterVariantId为非空，则查询仅包含所有相关阶段的所有相关变体的DVPMasterVariantID为681的项目.

If the variable dvpMasterVariantId is non-null the query only includes the items for which All the related Variants of All the related Phases have a DVPMasterVariantID of 681.

SQL代码不仅会显示所有阶段/变体都满足这些条件的DPV测试，而且还会显示任何阶段/变体都满足这些条件的DPV测试.此外，对于满足这些条件的每个测试/阶段/变体，它将返回单独的一行

The SQL code will not just show the DPV Tests for which ALL Phases / Variants meet these conditions, but where ANY of the Phases / Variants meet these conditions. Moreover it will return a separate row for each Test / Phase / Variant that meets these conditions

这篇关于LINQ delima将sql转换为LINQ的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！