linq to sql query with multiple where parameters

Question

I'm currently writing a search function in ASP.NET MVC 4 with the Entity Framework. However, I've hit a roadblock for which I can only find "bad" solutions.

My search functions gets back a model which consists of 4 parameters:

String Name
String Street
String Code
String Province

List<Query> query = (from t in Model select t).ToList();

Now I'd like to filter on my search input. However, a user can decide to fill in as many search fields as possible. He can decide to use Name and Street, or Name, Street and Province, or ...

The only real solution I was able to find consists of making my query and IQueryable and check whether or not a field has been filled with an if, then use a .Where to update the query. As this would currently give m 5 queries, I'm wondering on whether or not there is a better solution that I'm missing here.

Thanks for helping me.

Arion · Accepted Answer · 2015-06-04 06:50:14Z

23

If I understand you correct. You might want something like this:

string Name;
string Street;
string Code;
string Province;
var query=(from t in Model select t);
if(Name!=null)
{
    query=query.Where (q =>q.Name==Name);
}
if(Street!=null)
{
    query=query.Where (q =>q.Street==Street);
}
if(Code!=null)
{
    query=query.Where (q =>q.Code==Code);
}
if(Province!=null)
{
    query=query.Where (q =>q.Province==Province);
}
List<Query> ls = query.ToList();

You will have a IQueryable when you add the where statements and when you do the ToList() that sql will execute.

Update

To answer the comment of Luis Hernández. So this is how it works. When you select from the model in this case the collection type is IQueryable. This means that it has not been executed against the database. For the query to execute you need to apply some of the final metod. To tell linq that it will actually do the database call. These are for example

ToList()
FirstOrDefault()
SingleOrDefault()
Single()
First()
ToDictionary()

So when we append the Where clauses without using ToList(). There is no execution of the query.

Please try the query in LinqPad

edited Jun 4, 2015 at 6:50

answered Apr 18, 2012 at 6:37

Arion

31.3k10 gold badges74 silver badges89 bronze badges

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

7 Comments

whodares Over a year ago

Yes, that's the solution I found online. I was wondering on whether or not there is a cleaner way to code this.

Arion Over a year ago

If you want to stay IQueryable. Then this is what i think the way to do it

mshsayem Over a year ago

May be there is more code, but this the most optimized (and probably the best) one. Check the resulting SQL query in linqpad

Arion Over a year ago

Why?? You are check if something is null (Not complicated). If the value is something then add a where statement (Not complicated).

Luis Hernández Over a year ago

I have a doubt about this solution. What if there is a big bunch of data being recovered from the DB? As far as can see (pardon me if im wrong) this will recover the whole colection of data from database, then filter the results given the specified criteria, am i right?

|

Steven · Accepted Answer · 2014-08-11 11:44:51Z

2

Use the Entity filter class you find here : https://servicelayerhelpers.codeplex.com/SourceControl/changeset/view/32810#537055

so first specify your filter and after that just apply it to your query.

Example:

var filter = EntityFilter
.Where(c => c.Name == came)
.Where(c => c.City == city);

var customers = FindCustomers(filter);

Customer[] FindCustomers(IEntityFilter filter)
{
var query = context.Customers;
query = filter.Filter(query);
return query.ToArray();
}

more info on: https://cuttingedge.it/blogs/steven/pivot/entry.php?id=66

edited Aug 11, 2014 at 11:44

Steven

174k26 gold badges355 silver badges453 bronze badges

answered Apr 18, 2012 at 6:54

dg90

1,2633 gold badges17 silver badges30 bronze badges

Comments

Likurg · Accepted Answer · 2012-04-18 06:53:53Z

0

You casn try some thing like this

from cars in tblCars
where (cars.colorID == 1) && (cars.Wieght > 500) && (cars.Active == true)
select cars;

answered Apr 18, 2012 at 6:53

Likurg

2,76019 silver badges22 bronze badges

Comments

Mithir · Accepted Answer · 2012-04-18 07:52:31Z

0

Arion's solution is of course very good, I tried to make it a little less repetitive using reflection, hope it helps.

        Type myType = myObject.GetType();
        IList<PropertyInfo> props = new List<PropertyInfo>(myType.GetProperties());

        foreach (PropertyInfo prop in props)
        {
            object propValue = prop.GetValue(myObject, null);
            if (propValue != null)
            {
                query = query.Where(q => prop.GetValue(q, null) == propValue);
            }
        }

EDIT:

I've edited it to work on all properties. of course you still need some things around for this to work, but once you understand how to work with it, you can use it as a utility for all of your code, instead of hardcoding it for every type

edited Apr 18, 2012 at 7:52

answered Apr 18, 2012 at 7:13

Mithir

2,4413 gold badges27 silver badges40 bronze badges

1 Comment

whodares Over a year ago

Thanks for the answer, but this only works for the Name value which you specified. As I have 3 other values as well, I don't see how this could work.

Collectives™ on Stack Overflow

linq to sql query with multiple where parameters

4 Answers 4

7 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related