LINQ optional where clause

Question

I've used this example: Linq with optional WHERE options ...to make optional where clause on my LINQ, but the problem is i allways get null result for user. isverified is a bool value and it's true if master password is correct, in that case i don't need linq part (w.Password == model.Password)

when isVerified is true i need my linq to look like:

var user = _context.Users.SingleOrDefault(w => w.Name == model.Username);

I tried something like this:

var user = _context.Users.SingleOrDefault(w => w.Name == model.Username && (!isverified || w.Password == model.Password));

or

var user = _context.Users.SingleOrDefault(w => w.Name == model.Username && (isverified || w.Password == model.Password));

I believe your isverified expression is inverted. && (isVerified || w.Password == model.Password — hijinxbassist, Dec 16 '20 at 15:56
That's a *bad* example that can easily result in bad execution plans. If you don't want a `where` clause simply don't use it. — Panagiotis Kanavos, Dec 16 '20 at 15:59

Backs · Answer 1 · 2020-12-16T16:01:51.557

1

You should invert isverified condition:

var user = _context.Users.SingleOrDefault(w => w.Name == model.Username && (isverified || w.Password == model.Password));

But it generates not optimal query plans in sql server.

So, prefered way - create dynamic query:

var users = _context.Users.Where(w => w.Name == model.Username);

if (!isverified)
{
    users = users.Where(w=>w.Password == model.Password);
}

var user = users.SingleOrDefault();

edited Dec 16 '20 at 16:01

answered Dec 16 '20 at 15:56

Backs

24,430
5
58
85

1

Only the *second* option is good. The first one can result in bad execution plans like all catch-all queries. Both queries are dynamic anyway – Panagiotis Kanavos Dec 16 '20 at 16:00

LINQ optional where clause

1 Answers1