Adding optional where parameter to query based linq query

Question

I am trying to use the answer from LINQ to SQL Where Clause Optional Criteria

I like my linq to use query based syntax. Not sure how to use the whereif.

my query will look something like this

var result = (from tran in _ctx.Transactions where tran.Id == transactionId select tran);

and sometimes projecting it

var result = (from tran in _ctx.Transactions where tran.Id == transactionId
     select new Abstract
           {
               tran.Date,
               tran.Key
           });

I can do an optional filter using method syntax

var result = _ctx.Transactions
             .where(t=>t.Id == transactionId)
             .whereIf(tran.Dept!= "AllDept", x => x.Dept== deptName);

Not sure how to use the WhereIf in a query based linq query.

See https://stackoverflow.com/questions/279701/extension-methods-syntax-vs-query-syntax for a discussion about which syntax is better particularly when you are extending it. — Ian Mercer, Apr 03 '18 at 05:58
That syntax is pretty crippled, so best you can do is: `from tran in _ctx.Transactions.WhereIf(..., tran => tran.Id == transactionId) select tran` (so a mix of two syntaxes). — Evk, Apr 03 '18 at 06:27

Pranay Rana · Accepted Answer · 2018-04-03T17:21:34.103

1

Read : Dynamic query with Linq

I suggest make use of PredicateBulder and add predicate to you query , like as below , this will build query dynamically with variable predicate

var predicate = PredicateBuilder.True<Transaction>();  
predicate = predicate.And(t=>t.Id == transactionId);  
if (!string.IsNullOrEmpty(department))  
{  
    predicate = predicate.And(tran => tran.Dept!= "AllDept");  
}
var result = _ctx.Transactions.where(predicate);

Dynamically Composing Expression Predicates

edited Apr 03 '18 at 17:21

answered Apr 03 '18 at 05:50

Pranay Rana

175,020
35
237
263

What do I do incase of projections? Say I am using joins in the query for example `var tran = (from t in _ctx.transactions join type in _ctx.TranType on t.key equals type.key select new Abstract {t.date, type.name}); ` now I want to filter based on an optional filter on tranType.? – Navyseal Apr 03 '18 at 06:08
@Navyseal - if in given query you want to add filter based on tranType , is trantype is coming from query ?? – Pranay Rana Apr 03 '18 at 06:11
tranType is another table which I wish to join with transaction. Actually I have multiple joins. But for brevity, lets say I have two table transaction and transactionType and I am basing my filter on an Id which is in Transaction and I want to add an optional filter on TransactionType.Name only if say the function argument doesnt say "AllTypes" and if the argument says "AllTypes", I leave out the filter. All this needs to be projected into another "AbstractTransaction" class. How do I do this using predicate builder? – Navyseal Apr 03 '18 at 06:16
@Navyseal - not geting actual view of your code but need to do like this `if (transtype == "AllTypes") { predicate = predicate.And(tran => condition you want); }` – Pranay Rana Apr 03 '18 at 06:21

Adding optional where parameter to query based linq query

1 Answers1