Clever Geek Handbook

Use the optional OR clause in Linq.Table.Where ()

Is there a way to make the ProjectID validation below the extra block part? I am a recent .Net converter from Java EE and I am looking for something similar to the Hibernate API. I would like to simplify the block below and only need to call Where () once. I'm also not sure about the performance associated with executing the Where () function with lambdas since I just started working with .Net a week ago.

public IQueryable<Project> FindByIdOrDescription(string search)
    {
        int projectID;
        bool isID = int.TryParse(search, out projectID);

        IQueryable<Project> projects;

        if (isID)
        {
            projects = dataContext.Projects.Where(p => p.ProjectDescription.Contains(search) || p.ProjectID == projectID);
        }
        else
        {
            projects = dataContext.Projects.Where(p => p.ProjectDescription.Contains(search));
        }

        return projects;
    }
+2


source to share


3 answers


If you are looking for an optional addition AND xyz

, you can simply call the calls Where

:

var projects = dataContext.Projects.Where(p => p.ProjectDescription.Contains(search));
if (isID)
    projects = projects.Where(p => p.ProjectID == projectID);

Unfortunately, things are not that easy when you want to do OR xyz

. For this to work, you will need to manually create the predicate expression. It's not pretty. One way to do this is

Expression<Func<Project, bool>> predicate = p => p.ProjectDescription.Contains(search);
if (isID)
{
    ParameterExpression param = expr.Body.Parameters[0];
    predicate = Expression.Lambda<Func<Project, bool>>(
        Expression.Or(
            expr.Body,
            Expression.Equal(
                Expression.Property(param, "ProjectID"),
                Expression.Constant(projectID))),
        param);
}
var projects = dataContext.Projects.Where(predicate);

Note that adding a condition to an existing predicate is much more than creating the original expression because we need to completely rebuild the expression. (The predicate must always use one parameter, declaring two expressions using lambda syntax will create two separate parameter expression objects, one for each predicate.) Note that the C # compiler does roughly the same thing behind the scenes when you use the lambda syntax for the initial predicate.



Note that this may sound familiar if you are used to the Hibernate Criteria API, a little more verbose.

Note, however, that some LINQ implementations are quite smart, so the following may work as well:

var projects = dataContext.Projects.Where(
    p => p.ProjectDescription.Contains(search)
      || (isID && p.ProjectID == projectID));

YMMV, however, needs to check the generated SQL.

+3


source


The query is not parsed or executed until the first time you access the IQueryable elements. So no matter how many places you add (which you don't even do here anyway), you don't have to worry about hitting the DB too often.



+1


source


Delegates / expressions can be "chained" like this (untested pseudocode):

Expression<Predicate<Project>> predicate = p => p.ProjectDescription.Contains(search);
if ( isID ) 
    predicate = p => predicate(p) || p.ProjectID == projectId;

return dataContext.Where(predicate);
0


source






More articles:


All Articles