c# - 如何使用 Entity Framework 和 Linq 创建此查询

Question

在使用 LinqKit 的 LinqPad 中，我正在执行以下操作:

var topPredicate = PredicateBuilder.True<LandRecord>();
topPredicate = topPredicate.And(a=>a.InstrumentType == "DEED");
topPredicate = topPredicate.And(a=>a.BookType == "OFFICIAL RECORD");

var subPredicate = PredicateBuilder.True<LandRecord>();
subPredicate = subPredicate.And(a=>a.Parties.Any(b=>b.LastName == "SMITH"));
subPredicate = subPredicate.And(a=>a.Parties.Any(b=>b.FirstName == "John"));

LandRecords.AsExpandable().Include(a=>a.Parties).Where(topPredicate).Where(subPredicate).ToList();

这是它正在创建的 SQL:

SELECT 
    [Extent1].[LandRecordID] AS [LandRecordID], 
    [Extent1].[DocumentID] AS [DocumentID], 
    [Extent1].[InstrumentNo] AS [InstrumentNo], 
    [Extent1].[BookType] AS [BookType], 
    [Extent1].[BookNo] AS [BookNo], 
    [Extent1].[PageNo] AS [PageNo], 
    [Extent1].[DateFiled] AS [DateFiled], 
    [Extent1].[DateInstrument] AS [DateInstrument], 
    [Extent1].[InstrumentType] AS [InstrumentType], 
    [Extent1].[MortgageAmount] AS [MortgageAmount]
    FROM [LAND].[LandRecord] AS [Extent1]
    WHERE (N'DEED' = [Extent1].[InstrumentType]) AND (N'OFFICIAL RECORD' = [Extent1].[BookType]) AND ( EXISTS (SELECT 
        1 AS [C1]
        FROM [LAND].[Party] AS [Extent2]
        WHERE ([Extent1].[LandRecordID] = [Extent2].[LandRecordID]) AND (N'SMITH' = [Extent2].[LastName])
    )) AND ( EXISTS (SELECT 
        1 AS [C1]
        FROM [LAND].[Party] AS [Extent3]
        WHERE ([Extent1].[LandRecordID] = [Extent3].[LandRecordID]) AND (N'John' = [Extent3].[FirstName])
    ))

我想让它创建的 SQL 如下所示，其中 LastName 和 FirstName 将组合/加入同一个 exists 语句。

在生产环境中，该子查询可以包含 1 个或多个条件，每个条件都是起始符、终止符、包含或完全匹配。所以我需要能够一次手动构建一个子查询。

SELECT 
    [Extent1].[LandRecordID] AS [LandRecordID], 
    [Extent1].[DocumentID] AS [DocumentID], 
    [Extent1].[InstrumentNo] AS [InstrumentNo], 
    [Extent1].[BookType] AS [BookType], 
    [Extent1].[BookNo] AS [BookNo], 
    [Extent1].[PageNo] AS [PageNo], 
    [Extent1].[DateFiled] AS [DateFiled], 
    [Extent1].[DateInstrument] AS [DateInstrument], 
    [Extent1].[InstrumentType] AS [InstrumentType], 
    [Extent1].[MortgageAmount] AS [MortgageAmount]
    FROM [LAND].[LandRecord] AS [Extent1]
    WHERE (N'DEED' = [Extent1].[InstrumentType]) AND (N'OFFICIAL RECORD' = [Extent1].[BookType]) AND ( EXISTS (SELECT 
        1 AS [C1]
        FROM [LAND].[Party] AS [Extent2]
        WHERE ([Extent1].[LandRecordID] = [Extent2].[LandRecordID]) AND (N'SMITH' = [Extent2].[LastName] AND (N'John' = [Extent2].[FirstName])
    ))

Answer 1

您可以单独构建子谓词，如果它包含任何内容，则将其添加到顶级谓词:

var topPredicate = PredicateBuilder.True<LandRecord>();
topPredicate = topPredicate.And(a=>a.InstrumentType == "DEED");
topPredicate = topPredicate.And(a=>a.BookType == "OFFICIAL RECORD");

var subPredicate = PredicateBuilder.True<Party>();
if (!string.IsNullOrWhiteSpace(firstName)
{
    subPredicate = subPredicate.And(b => b.FirstName == firstName);
}
if (!string.IsNullOrWhiteSpace(lastName)
{
    subPredicate = subPredicate.And(b => b.LastName == lastName);
}

// If the subPredicate's body is still just PredicateBuilder.True<Party>(), ignore it.
if (!(subPredicate.Body is ConstantExpression))
{
    topPredicate = topPredicate.And(lr => lr.Parties.AsQueryable().Any(subPredicate));
}

这里使用lr.Parties.AsQueryable()是因为Parties可能是一个ICollection，即IEnumerable。由于 IEnumerable.Any 接受 Func，而不是 Expression，如果没有 AsQueryable()，代码将无法编译.

顺便说一下，我的代码不包含 AsExpandable() 因为我使用 Universal PredicateBuilder .