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

Question

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

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;

context.SaveChanges();

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

It's because you are using .ToList() that your query is being executed , you can remove .ToList and call it after your if , both solutions are compatible anyway — Coder1409, Aug 27 '15 at 09:54
Just do `var usr = context.USERS` and you'll get only one query. And you can add, at the end, something like `return usr.ToList()` if you need an enumerated result. — Raphaël Althaus, Aug 27 '15 at 09:58

score 2 · Accepted Answer · answered Aug 27 '15 at 12:51

2

If you're just using...

var usr = context.USERS

or

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

answered Aug 27 '15 at 12:51

James Sinclair

397
5
16

First of all thank you, both you and @Sebastiano Roncato have been very clear. I have modified the question with a new part, i hope you can help me even there. – luke88 Aug 27 '15 at 14:25
I'm not if theres a nice way to update directly using linq. I've always done pretty much the same as the example in the original question. Retrieve the record I need from the db (or wherever), edit it in memory then context.SaveChanges() to push it back to storage. – James Sinclair Aug 27 '15 at 15:04
I don't think there is a better way either, as Entity Framework will start tracking the entity only at the moment it's fetched from the database. – BgrWorker Aug 27 '15 at 15:35
1

I made a few searches and it looks like you can set only one property as modified, then propagate the change to the db. You just need to create an entity of the same tipe, set the key appropriately, attach it to the context and set the property as modified. See here: http://stackoverflow.com/questions/15336248/entity-framework-5-updating-a-record – BgrWorker Aug 27 '15 at 15:46

score 1 · Answer 2 · answered Aug 27 '15 at 13:12

Using ToList() or FirstOrDefault() will load the entity from the context. This means thant untll you invoke those extensions you can keep working with the IQueriables you instanced without querying the database. You can even pass them around and use them in other methods (just be careful not to dispose your context or load entities already loaded in other contexts).

The correct way to do what you're asking is

var result = context.USERS.Where(u => 
    (!string.IsNullOrWhiteSpace(name)|| u.NAME == name) 
    && 
    (!string.IsNullOrWhiteSpace(password)|| u.PASSWORD == password));

Also, the way @James Sinclair answered is more readable, and I'd suggest you to code that way as much as you can, as it also allows to move that code in another method and add as many filters as you like.

If you have an unknow number of filters you could also use Expression Trees, that while being generally slower provides a great dynamic way of querying the database without breaking the general safety of LINQ to SQL.

https://msdn.microsoft.com/en-us/library/bb397951.aspx

Be wary that reusing your code and being able to understand how complex is an operation is fundamental to write "performant" code this way (it will still be heavier than writing your expression).

Anyway, as a general rule, remember that all LINQ to SQL methods will only translate to SQL basic methods (so no constructors with parameters or other weird things)

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

2 Answers2