changing linq to sql query

Question

I have a question regarding a Linq to SQL query.

I have following situation:

I have a search with lots of options, like location, availability, name, language etc ...

For this options i have to execute a query to retrieve the results according to options selected, how can i best do it, i cannot write a linq query like for each possibility and combination of options, but i cannot write one for all of them as it will not work, for example:

from p in context.people where p.location==model.location && p.availability==model.availability .... select p

In this case imagine availability is not selected and should not be searched for, but in this case it will be passed as false, or if location is not set and is null so it will only search for empty locations, although i just need all.

So my question is how do people handle this kind of behaviour with queries?

Kenneth · Accepted Answer · 2014-02-05 10:16:42Z

2

As you long as you do not execute the linq query immediately you can just add where clauses to it. You can do this for example:

var query = from p in context.people;
if(searchOnLocation)
{
    query = query.where(p => p.location == model.location);
}
if(otherSearch)
{
    query = query.where(p => p.someOtherProperty == someotherValue);
}

var result = query.ToList();

As long you don't call ToList() on your IQueryable, the linq will not be translated into SQL. It's only in the last call, that the linq will be translated and executed against the database

answered Feb 5, 2014 at 10:16

Kenneth

28.8k7 gold badges65 silver badges85 bronze badges

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

Comments

Marc Gravell · Accepted Answer · 2014-02-05 10:15:38Z

1

IQueryable<Person> query = context.people;
if(model.location != null)
    query = query.Where(x => x.location == model.location);
if(model.availability != null)
    query = query.Where(x => x.availability == model.availability);
// etc

Basically, you can compose more and more restrictions as you go.

answered Feb 5, 2014 at 10:15

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Comments

nitish · Accepted Answer · 2014-02-05 11:36:57Z

0

If you want to implement query without if condition than you can use following syntax:

 var query = context.people.
                where(p => p.location == (model.location ?? p.location) 
                          && p.availability == (model.availability ?? p.availability))
               .ToList();

answered Feb 5, 2014 at 11:36

nitish

811 silver badge7 bronze badges

Collectives™ on Stack Overflow

changing linq to sql query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related