EF6 Query Criteria using object properties that aren't null

Question

I have a viewmodel class coming in from an MVC submission, and I want to get a result set from EF6 based upon values that the user has filled out, but ignore those items in the model that are null:

public class SearchFilterVM
{
    public int? ID { get; set; }
    public bool? Active { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string MiddleInitial { get; set; }
    public DateTime? DateOfBirth { get; set; }
    public string Phone { get; set; }
    public string Email { get; set; }
}

Essentially what I'm looking for is something like this psuedo-code:

var results = context.Members
    .Where(x => x.Active == vm.Active.Value)    // but only if vm prop is not null
    .Where(x => x.FirstName == vm.FirstName)    // but only if vm prop is not null
    .Where(x => x.LastName == vm.LastName)      // but only if vm prop is not null
    .ToList();

(e.g., if the filter model properties are null, ignore them altogether)

Naturally I don't want to use something like the above, as it would query based on the first criteria, then requery based on the second applicable criteria, etc. until completed (I'm working with 500k+ rows).

I can't think of a way to use LINQ query expressions for this either.

What I could do is build a parameterized SQL statement based on the presence of a value and append the criteria and finally pass it through EF6's RawSQLQuery, which would work performance-wise (and give me nice control over the order of indexed fields for better tuning), but I'm wondering if there's a "natural" way to accomplish the same thing via LINQ.

Answer 1

My Suggested Method

Remember that EF uses deferred execution and you don't actually execute any query until you materialise it with ToList() or iterating over it for example. That means you can do this:

var results = context.Members;

if(vm.Active.HasValue)
{
    results = results.Where(x => x.Active == vm.Active.Value);
}

if(!string.IsNullOrEmpty vm.FirstName))
{
    results = results.Where(x => x.FirstName == vm.FirstName);
}

//and so on until...

return results.ToList();

The Other Methods

I thought I'd add this extra as a freebie to understand why you may not want to use the techniques mentioned in the other answers. Lets say you did this:

string name = "bob";

var users = context.Users.Where(u => name == null || u.Name == name).ToList();

This looks pretty similar to my version and will give the same results, but the SQL query is quite different. You will end up with something like this:

DECLARE @p__linq__0 NVarChar(1000) = 'bob'
DECLARE @p__linq__1 NVarChar(1000) = 'bob'

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name], 
    /* snip */
    FROM [dbo].[Users] AS [Extent1]
    WHERE @p__linq__0 IS NULL OR [Extent1].[Name] = @p__linq__1

Note that the null check is now done in the database, and that you also sent the parameter in twice. OK, it's probably not going to be noticeably slower, but it's something you probably want to keep in mind for the future.

Answer 2

You could just check your VM variables for NULL inside where statements:

var results = context.Members
    .Where(x => vm.Active == null || x.Active == vm.Active.Value)    // but only if it's not null
    .Where(x => vm.FirstName == null || x.FirstName == vm.FirstName)    // but only if it's not null
    .Where(x => vm.LastName == null || x.LastName == vm.LastName)      // but only if it's not null
    .ToList();

In that case, for example, if vm.FirstName would be null, the first statement vm.FirstName == null would be true (and if not null, then it would actually check for first name equality) and so on for other conditions.