EF Core with '&&' and '||' inside lamda expression-CodePudding

The following query is not returning the proper results, it will return properly for Company but not the other two parameters. For clarification this is inside a post method of a page taking the user's input for company, name, and/or state

var transporters = await _db.TransporterProfiles
                            .Include(x => x.TransportState)
                            .Where(x => x.Company == company || company == null &&
                                   x => x.LastName == name || name == null &&
                                   x => x.TransportState.Name == state || state == null)
                            .ToListAsync();

I've tried adding parantheses around each part of the query such as

.Where((x => x.Company == company || company == null) &&
       (x => x.LastName == name || name == null) &&
       (x => x.TransportState.Name == state || state == null))

but this produces an error of "Operator '&&' cannot be applied to operands of type 'lambda expression'

CodePudding user response：

There's no reason to include company == null in the query. If you don't want a search term, don't include it at all. You can build AND conditions by adding Where clauses to a query as needed, eg :

if(value1 != null)
{
    query=query.Where(x=>x.Property1 == value1);
}
if(value2 != null)
{
    query=query.Where(x=>x.Property2 == value2);
}

In the question's case you can write something like this:

var query=_db.TransporterProfiles.Include(x => x.TransportState);
if(company!=null)
{
    query=query.Where(x => x.Company == company);
}
if(name!=null)
{
    query=query.Where(x => x.LastName == name);
}
if(state!=null)
{
    query=query.Where(x => x.TransportState.Name == state);
}

var transporters=await query.ToListAsync();

You don't need to include TransportState to use x.TransportState.Name in the Where clause. Include is used to eagerly load related data, not tell EF to JOIN between related tables.

If you don't want Include you can start the query with :

var query=_db.TransporterProfiles.AsQueryable();

CodePudding user response：

The issue with your syntax is you have multiple lambdas that should be one.

.Where(x => (x.Company == company || company == null) &&
   (x.LastName == name || name == null) &&
   (x.TransportState.Name == state || state == null))

That said the actual solution is to do what @PanagiotisKanavos posted as an answer, generate the query dynamically based on the input values. The generated sql will be easier to read as well as less chance of an issue with using an inefficient plan.