LINQ query with multiple conditional where clauses

Question

I can't seem to figure out how to make a LINQ query with where clauses based on multiple nullable variables work. I've tried the "if" methods I've seen on this site, and I get the error:

The name I doesn't exist in the current context.

The "Point" fields are formatted as "POINT(num1, num2)" and work fine in a SQL query.

query = from i in _db.ILV
        join p in _db.PC on
             i.PostCode equals p.PostCode                        
        select i;

if (!string.IsNullOrEmpty(key))
    query = query.Where(i = i.Description.Contains(key));

if(!string.IsNullOrEmpty(rng))
    query = query.Where(i => STGeomFromText(i.Point).STDistance(q.Point) <= rng);

if (!string.IsNullOrEmpty(cat))
    query = query.Where(i = i.CategoryID.ToInt(cat));

if(!string.IsNullOrEmpty(sub))
    query = query.Where(i = i.SubCategoryID.ToInt(sub));

return query;

What flavor of LINQ are you using (EF/L2S)? Where is STGeomFromText and STDistance declared? where is q declared? — Jim Wooley
– Jim Wooley, Commented Mar 4, 2014 at 22:02
I'm using Visual Studio 2013 .Net Framework 4.5. STGeomFromText and STDistance are methods in the System.Spatial.Geography class, and q is declared prior to the linq query as: q = from z in _db.PC where(z.PostCode == zip) select z; — Bryan S
– Bryan S, Commented Mar 4, 2014 at 22:48

Andrew · Accepted Answer · 2014-03-04 19:30:18Z

2

That would work if you used i => instead of i = . You can put it all in one block however, like follows:

query = from i in _db.ILV
        join p in _db.PC on
            i.PostCode equals p.PostCode   
        where (!string.IsNullOrEmpty(key)) ? i.Description.Contains (key) : true &&
              (!string.IsNullOrEmpty(rng)) ? // rest of your where clauses follow the same pattern          
        select i;

answered Mar 4, 2014 at 19:30

Andrew

2,3253 gold badges27 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Robert Noack Over a year ago

I think it looks better like this: where (string.IsNullOrEmpty(key) || i.Description.Contains(key)) && (string.IsNullOrEmpty(rng) || .....) &&

Jim Wooley Over a year ago

As long as you are ANDing the conditions, I prefer the IO's option of using deferred execution to build the query rather than bloating the query with evaulations that the database may or may not need to do which can slow down the query at the database level with large tables. You are correct about using => vs = however.

Bryan S Over a year ago

That worked! Thank you, Andrew! BTW, I tried using i => and i == and it gave the same error as i =.

HQSantos Over a year ago

In case where i need to set multiple querys, for strings and Ids, how can I do that?

Collectives™ on Stack Overflow

LINQ query with multiple conditional where clauses

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related