Mike R. Mike R. - 7 months ago 21
SQL Question

Entity Framework building Where clause on the fly using Expression

Using Entity Framework C# and have this query, I need the part where it says:

where x.Login_Status == "Submitted"


to be dynamic. There are different cases it could be "Submitted" or null or something else and instead of writing multiple if statement with different queries in it, want to have a Predicate in a where clause.

status = (from x in ctx.table
where x.Login_Status == "Submitted"
orderby x.SUB_DATE descending
select new Model_Table()
{
Id = x.ID,
Name = x.NAME,
Code = x.Code,
DateSubmitted = x.SUB_DATE
}).ToList<Model_Table>();


Is that possible?

Solution:

Inside the if statement when more parameters encountered use this

where_expression = x => x.Login_Status == "Submitted" || x.Login_Status == null;


Here is a complete code that worked for me, anything between square brackets replace to suit your code:

Expression<Func<[Replace with your Entity], bool>> where_submitted = x => x.Login_Status == "Submitted";

// Check if all selected
if (CheckBox_Show_All_Submitted.Checked)
{
where_submitted = x => x.Login_Status == "Submitted" || x.Login_Status == null;
}

status =
ctx.[Replace with your Entity Table]
.Where(where_submitted)
.OrderByDescending(x => x.SUB_DATE)
.Select(x => new Model_Table
{
Id = x.ID,
Name = x.NAME,
Code = x.Code,
DateSubmitted = x.SUB_DATE
}).ToList<Model_Table>();

Answer

You need an Expression<Func<Entity,bool>>, not a Predicate<Entity>. The difference is that a predicate is a compiled delegate, and an expression is code as data and thus can be translated to SQL.

Here is an example:

//You can have this expression have different values based on your logic
Expression<Func<Entity,bool>> where_expression = x => x.Login_Status == "Submitted";

var query =
    ctx.Table
    .Where(where_expression)
    .OrderByDescending(x => x.SUB_DATE)
    .Select(x => new Model_Table())
    {
        Id = x.ID,
        Name = x.NAME,
        Code = x.Code,
        DateSubmitted = x.SUB_DATE
   }).ToList();        

Please note that you need to replace Entity with the name of the real class.