bool column in where clause - linq/c#

Question

 Expression<Func<Employee, bool>> employeeWhere = R => true;
 employeeWhere = R => R.PositionCode == "M";
 employeeWhere = R => R.IsActive; //want only ones which are true

Will the above build me a query of this :

SELECT * FROM EMPLOYEE 
WHERE POSITIONCODE = "M" && IsActive = 1

This is what I want to return

 var result = _db.Employees
              .Where(employeeWhere)
              .Select(p => new { p.EmployeeID, p.FName, p.MName, p.LName })
              .AsEnumerable()
              .ToDictionary(kvp => kvp.EmployeeID, kvp => kvp.FName + " " + kvp.MName + " " + kvp.LName);

        return new SelectList(result, "Key", "Value");

JoshJordan · Accepted Answer · 2009-08-12T14:37:02.437

2

The code above does not build the SQL query you posted, if that's what you're asking.

Unless I'm mistaken, you overwrite the value of employeeWhere twice. I think you need:

Expression<Func<Employee, bool>> employeeWhere = R => R.PositionCode == "M" && R.IsActive;

If you had input parameters (say bool myBoolParam and decimal myDecimalParam) to whatever method this is in, you could do something like this:

Expression<Func<Employee, bool>> employeeWhere =
    (R => R.PositionCode == "M" && R.IsActive && myBoolParam && R.Salary > myDecmialParam);

edited Aug 12 '09 at 14:37

answered Aug 12 '09 at 14:26

JoshJordan

12,676
10
53
63

I see your point. Begs me to ask this ? If I had to check if input params had values, how would i build the where clause dynamically? – Aug 12 '09 at 14:30
Expression> is a type that holds a Lambda function, which is just regular code. You can put anything you want in there, as long as it takes an Employee as a parameter and returns a boolean value. I'll put an example above. – JoshJordan Aug 12 '09 at 14:35
Thanks Josh. IF i have to do this: 1.Check if PositionCode is enetered, if Y then add expression 2.check if salary value is entered, if Y then add to expression is there a way to keep appending based on the conditions more like Dictionary.Add..ie. Expression.Add? – Aug 12 '09 at 14:40
I think you're starting to get into a second, complicated question here. :) Please post this more verbosely in another question and put the link here, and I'll hit it, if you don't mind :) – JoshJordan Aug 12 '09 at 14:51
Josh. Marking your answer as the one and starting new question – Aug 12 '09 at 14:52
For anyone who is following the thread and is interested, the new question is here: http://stackoverflow.com/questions/1266742/append-to-an-expression-linq-c – JoshJordan Aug 12 '09 at 15:12

score 0 · Answer 2 · answered Aug 12 '09 at 14:30

I think you want something like this:

var query = from e in _db.Employees
            where e.PositionCode == "M" && e.IsActive
            select new { e.EmployeeID, 
                Value = e.FName + " " + e.MName + " " + e.LName                 
            };

return new SelectList(query.ToDictionary(x => x.EmployeeID, "Key", "Value");

bool column in where clause - linq/c#

2 Answers2