Select Multi fields in Entity framework in c#

Question

I want to select 2 or more fields like this: for example we have a list of some people and now they say find people who is male and live in New York
we have 2 fields in here "City" and "Sexual".

I can do it like this

private List<tblTest> GetAll(string city, string sexual)
{
    var all = (from x in db.tblTest
               where x.city == city && x.sexual == sexual
               select x).ToList();
    return all;
}

and this one is not helping:

private List<tblTest> GetAll(string city, string sexual)
{
    var all = (from x in db.tblTest
               where x.city == city || x.sexual == sexual
               select x).ToList();
    return all;
}

with "&&" I can do it but if I want to just select "City" it is not working, and I want to search like this for 14 fields and we want to search for some fields not all fields we have and we don't know which fields we want to search

What should I do?

Are you saying you want to pass in an undetermined number of filters? So it might be just city, but next time could be city and sex? — DavidG, Nov 30 '15 at 13:54
If it's based on say the variables being `null` maybe something like `where (city == null || x.city == city) && (sexual == null || x.sexual == sexual)` — juharr, Nov 30 '15 at 13:56
You may find this [answer](http://stackoverflow.com/a/33154580/3110834) useful. — Reza Aghaei, Nov 30 '15 at 13:59
This would actually be inefficient because each query would hit the database again. Because there are 14 possible fields just return the whole set and then query the collection with a predicate. — Stephen Brickner, Nov 30 '15 at 13:59
@StephenBrickner No it wouldn't, deferred execution means it only hits the database when you materialise the query into a list or enumerate through it. — DavidG, Nov 30 '15 at 14:01
If the query changes it will force entity to go back to the database. It will only reuse the context when search the same data set. If all fields are returned and then you just filter the collection they are returned to you will only hit the DB once. — Stephen Brickner, Nov 30 '15 at 14:05

Michael Mairegger · Accepted Answer · 2015-11-30T14:05:52.237

2

The way I do is following:

private List<tblTest> GetAll(string city, string sexual)
{
    var query = db.tblTest.AsQueryable();
    if(!string.IsNullOrEmpty(city))
    {
        query = query.Where(x => x.city == city);
    }
    if(!string.IsNullOrEmpty(sexual ))
    {
        query = query.Where(x => x.sexual == sexual );
    }
    return all.ToList();
}

It is important to call AsQueryable otherwise it might be that, if you write IEnumerable<tblTest> query = ..., the result is queried more that 1 times.

edited Nov 30 '15 at 14:05

answered Nov 30 '15 at 14:01

Michael Mairegger

6,833
28
41

1

@MichaelMairegger Thanks for editing [answer](http://stackoverflow.com/a/33154580/3110834) hope you find it helpful and make it more useful for future readers +1 to your answer :) – Reza Aghaei Nov 30 '15 at 14:08

Select Multi fields in Entity framework in c#

1 Answers1