score:224

Accepted answer

If you do not call ToList() and your final mapping to the DTO type, you can add Where clauses as you go, and build the results at the end:

var query = from u in DataContext.Users
   where u.Division == strUserDiv 
   && u.Age > 18
   && u.Height > strHeightinFeet
   select u;

if (useAge)
   query = query.Where(u => u.Age > age);

if (useHeight)
   query = query.Where(u => u.Height > strHeightinFeet);

// Build the results at the end
var results = query.Select(u => new DTO_UserMaster
   {
     Prop1 = u.Name,
   }).ToList();

This will still only result in a single call to the database, which will be effectively just as efficient as writing the query in one pass.

score:0

Assuming following parameter,

Int? Age = 18;

Simply using && and || conditional operators we can have another version.

(from u in DataContext.Users
where u.Division == strUserDiv 
    && (Age == null || u.Age > Age)
    && (Param1 == null || u.param1 == Param1)
    && u.Height > strHeightinFeet
select new DTO_UserMaster
{
    Prop1 = u.Name,
}).ToList();

Like Param1 you can add any number of parameters for search condition.

score:1

I just came across this looking for something else, but thought I'd throw in lambda version.

First, I'd create a class like this to pass parameters into a data layer:

   public class SearchParameters() {
       public int? Age {get; set;}
       public string Division {get;set;}
       etc
    }

Then, in my data layer, something like this:

public IQueryable<User> SearchUsers(SearchParameters params) 
{
    var query = Context.Users;
    if (params.Age.HasValue)
    {
         query = query.Where(u => u.Age == params.Age.Value);
    }
    if (!string.IsNullOrEmpty(params.Division)
    {
        query = query.Where(u => u.Division == params.Division);
    }
    etc
    return query;
}

Where you materialize the query is up to you. Might have a layer between the app and data which converts db-specific representations to db-agnostic (maybe you query multiple data sources). That layer might get multiple types of queryables from these sources and map them to a common POCO representation, for example.

score:2

Just to add to the above accepted answer here, if you are doing a dynamic search on a join, consider to return a new object with both tables (t1,t2) in the initial linq query so you can access them individually to do the conditional search.

var query = from t1 in _context.Table1
            join t2 in _context.Table2 on t1.Table1Id equals t2.Table1IdId
            select new { t1, t2 };

        if (!string.IsNullOrEmpty(searchProperty1))
        {
            query = query.Where(collection => collection.t1.TableColumn == searchProperty1);
        }
        if (!string.IsNullOrEmpty(searchProperty2))
        {
            query = query.Where(collection => collection.t2.TableColumn == searchProperty2);
        }
        ....etc.

I got the answer i was looking for here in regards to joining two tables and querying specific columns on either of the tables

score:4

Here is my code to do a similar thing. This is a method on my WCF SOAP Web Service api.

    public FruitListResponse GetFruits(string color, bool? ripe)
    {
        try
        {
            FruitContext db = new FruitContext();
            var query = db.Fruits.Select(f => f);
            if (color != null)
            {
                query = query.Where(f => f.Color == color);
            }
            if (ripe != null)
            {
                query = query.Where(f => f.Ripe == ripe);
            }
            return new FruitListResponse
            {
                Result = query.Select(f => new Fruit { Id = f.FruitId, Name = f.Name }).ToList()
            };
        }
        catch (Exception e)
        {
            return new FruitListResponse { ErrorMessage = e.Message };
        }
    }

The base query is Select(f => f) which means basically EVERYTHING, and the Where clauses are optionally attached to it. The final Select is optional. I use to convert the database rows objects into result "Fruit" objects.

score:5

Based on certain condtion add the where condition ...

from u in DataContext.Users
where u.Division == strUserDiv 
&& u.Age != null ? u.Age > 18 : 1== 1
&& u.Height != null ? u.Height > 18 : 1== 1
&& u.Height != null ? u.Height > 18 : 1== 1
 select new DTO_UserMaster
       {
         Prop1 = u.Name,
       }).ToList();

score:7

Simply I am using it in my where clause as

    public IList<ent_para> getList(ent_para para){
     db.table1.Where(w=>(para.abc!=""?w.para==para.abc:true==true) && (para.xyz!=""?w.xyz==para.xyz:true==true)).ToList();
}

score:23

one option.

bool? age = null

(from u in DataContext.Users
           where u.Division == strUserDiv 
           && (age == null || (age != null && u.Age > age.Value))
           && u.Height > strHeightinFeet  
           select new DTO_UserMaster
           {
             Prop1 = u.Name,
           }).ToList();

or you could switch to the method syntax for linq and use if conditions to attach expressions to the where clause.

score:29

I usually use method chaining but have the same problem. And here is extension I use

public static IQueryable<T> ConditionalWhere<T>(
        this IQueryable<T> source, 
        Func<bool> condition,
        Expression<Func<T, bool>> predicate)
    {
        if (condition())
        {
            return source.Where(predicate);
        }

        return source;
    }

It helps to avoid chain breaks. Also the same ConditionalOrderBy and ConditionalOrderByDescending are helpful.


Related Query