luke88 luke88 - 1 year ago 41
C# Question

Computational and stylistic optimal solution for the conditional where clause using Linq

I'm using for the first time Linq with for my new MVC project.
Until now i had no problem, but now I'm stuck.

I need to make a new interrogation with some conditional where clause.
Searching on the web I found some solution, and almost all are like this one: Conditional Linq Queries.

But, I don't think that it's computationally efficient.
indeed, if I have, for example, a table USERS and two different filters NAME and PASSWORD, doing something like this:

var usr = context.USERS.Select(u => u).ToList();

extract all the tables data, and only after that I'm filtering the result:

if (!string.IsNullOrWhiteSpace(name))
usr = usr.Where(u => u.NAME == name);

if (!string.IsNullOrWhiteSpace(password))
usr = usr.Where(u => u.PASSWORD == password);

The only way that this could be work good is in the case that the framework translates this in a single SQL command.
But by debugging, it seems that first we obtain a List and then in a different step we are filtering the List.

So I thought to do something like this:

var usr = context.USERS.Select(u => u)
.Where(u =>
((!string.IsNullOrWhiteSpace(name)) ? u.NAME == name : true)
((!string.IsNullOrWhiteSpace(password)) ? u.PASSWORD == password : true))

Now, It seems to execute all in one single shot.

I'd like to know if what I thought it's correct, if the second solution it's really better and if there are better solutions.

A similar problem happens when i try to update the table, if I need to update a specific row.
I'll try to explain better...
Always speaking about the previous example, if I need to update a specific row of USERS identified by an ID, I do this:

var user = context.USERS.Where(u => (u.ID == 1)).FirstOrDefault();

if (!string.IsNullOrWhiteSpace(name))
user.NAME == name;

if (!string.IsNullOrWhiteSpace(password))
user.PASSWORD == password;


Even in this case the execution is divided in two steps.

Answer Source

If you're just using...

var usr = context.USERS


var usr = context.USERS.Select(u => u).Where(u => u.NAME == name)

Then usr is IQueryable and isn't evaluated/executed until a .ToList() call (or similar).

So to answer your questions:

  • your thinking is correct
  • the second solution is better
  • as for a better solution, I'm not sure there's much improving that can be done as Linq will 'optimise' any queries. Personally I prefer seperating the .Where() clauses as I find it easier to read, ymmv.

so something like this

var usr = context.USERS.Select(u => u);    
if (!string.IsNullOrWhiteSpace(name))
    usr = usr.Where(u => u.NAME == name);    
if (!string.IsNullOrWhiteSpace(password))
    usr = usr.Where(u => u.PASSWORD == password);
return usr.ToList();