How to create this query using Entity Framework and Linq

Question

In LinqPad using LinqKit, I'm doing the following:

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();

This is the SQL that it is creating:

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])
    ))

The SQL that I would like to have it create would be the following where both LastName and FirstName would be combined / joined in the same exists statement.

In production, that sub query could contain 1 or more conditions with each being a startswith, endswith, contains, or exact match. So I need to be able to manually build that sub query 1 piece at a time.

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])
    ))

Why not define a predicate `b=>b.LastName == "SMITH" && b.FirstName == "John"`? — Gert Arnold, Aug 26 '15 at 13:22
That is all good if you know that both first name and last name have been provided. In my case, the user could enter the following: Smith or Smith, John or Smith, John L or Smith, Jo* L Basically until they search, I won't know if the predicate needs just the lastname, or lastname and firstname, etc. I need the ability to build this predicate based on what the user enters. — Lonnie, Aug 26 '15 at 14:16

score 0 · Answer 1 · answered Aug 27 '15 at 21:52

You can build the sub predicate separately and add it to the top predicate if it contains anything:

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));
}

Here, lr.Parties.AsQueryable() is used because Parties is probably an ICollection, i.e. IEnumerable. As IEnumerable.Any accepts a Func, not an Expression, the code wouldn't compile without AsQueryable().

By the way, my code doesn't contain AsExpandable() because I use Universal PredicateBuilder.

How to create this query using Entity Framework and Linq

1 Answers1