linq .Where() issue "Type of condition expression cannot be determined"

Question

I want to be able to pass different variables in a Linq query depending on if a string is null.

string site = null;
int q = a number;

var data = db.tbl_table12345
             .Where(site == null
                      ? d => d.stuff_id == org 
                             && d.Date.Month == q
                             && d.Date.Year == year 
                             && d.Q1 != (int?)null
                      : d => d.stuff_id == org
                             && d.Service == site
                             && d.Date.Month == q 
                             && d.Date.Year == year
                             && d.Q1 != (int?)null)
             .GroupBy(d => d.Q1)
             .Select(d => new
                          {
                              q1 = d.Key,
                              total = d.Count()
                          });

So in the above example if site == null then we perform a .Where search without the d.Service == site parameter. Else the service parameter is used in addition to the rest of the query. Is this possible?

Answer 1

Breaking up the expressions will let you visualize your logic better.

string site = null;
int month = a number;

Expression<Func<SomeType, bool>> nullExpression =
    d => d.stuff_id == org 
         && SqlFunctions.DatePart("MONTH", d.Date) == month
         && SqlFunctions.DatePart("YEAR", d.Date) == year
         && d.Q1 != (int?)null;

Expression<Func<SomeType, bool>> notNullExpression =
    d => d.stuff_id == org
         && SqlFunctions.DatePart("MONTH", d.Date) == month
         && SqlFunctions.DatePart("YEAR", d.Date) == year
         && d.Q1 != (int?)null
         && d.Service == site;

var expression = site == null ? nullExpression : notNullExpression

var data = db.tbl_table12345
             .Where(expression)
             .GroupBy(d => d.Q1)
             .Select(d => new { q1 = d.Key, total = d.Count() });

Or using Expression trees:

var expression = BuildWhere(org, month, year, site);
var data = db.tbl_table12345
         .Where(expression)
         .GroupBy(d => d.Q1)
         .Select(d => new { q1 = d.Key, total = d.Count() });

And here is a method that will build up your where Expression<Func<SomeType, bool>.

public Expression BuildWhere(int org, int month, int year, string service = null)
{
    var datePartMethod =
        typeof(SqlFunctions)
            .GetMethod("DatePart",
                       new[]
                       {
                           typeof(string),
                           typeof(DateTime?)
                       });

    // Variable d
    var variable =
        Expression.Variable(typeof(SomeType));

    var orgConstant =
        Expression.Constant(org);
    // d.stuff_id
    var stuffId =
        Expression.Property(variable, "stuff_id");

    // d.stuff_id == org
    var stuffIdEquals =
        Expression.Equal(stuffId, orgConstant);

    // d.Date cast into Nullable DateTime
    var date =
        Expression.Convert(
            Expression.Property(variable, "Date"),
            typeof(DateTime?));

    var monthPartConstant =
        Expression.Constant("MONTH");
    // month cast to nullable int
    var monthConstant =
        Expression.Convert(
            Expression.Constant(month),
            typeof(int?));
    var yearPartConstant =
        Expression.Constant("YEAR");
    // year cast to nullable int
    var yearConstant =
        Expression.Convert(
            Expression.Constant(year),
            typeof(int?));

    // SqlFunctions.DatePart("MONTH", d.Date)
    var invokeDatePartMonthPart =
        Expression.Call(
            datePartMethod,
            monthPartConstant,
            date);
    // SqlFunctions.DatePart("YEAR", d.Date)
    var invokeDatePartYearPart =
        Expression.Call(
            datePartMethod,
            yearPartConstant,
            date);

    // SqlFunctions.DatePart("MONTH", d.Date) == month
    var dateMonthEquals =
        Expression.Equal(
            invokeDatePartMonthPart,
            monthConstant);

    // SqlFunctions.DatePart("MONTH", d.Date) == year
    var dateYearEquals =
        Expression.Equal(
            invokeDatePartYearPart,
            yearConstant);

    // d.Q1
    var q1 = Expression.Property(variable, "Q1");
    var nullConstant =
        Expression.Constant((int?) null);
    // d.Q1 != (int?) null
    var q1NotEquals =
        Expression.NotEqual(
            q1,
            nullConstant);

    // d.stuff_id == org
    // && SqlFunctions.DatePart("MONTH", d.Date) == month
    // && SqlFunctions.DatePart("YEAR", d.Date) == year
    // && d.Q1 != (int?) null
    var andExpression = 
        Expression.AndAlso(stuffIdEquals,
            Expression.AndAlso(dateMonthEquals,
            Expression.AndAlso(dateYearEquals,
                q1NotEquals)));

    // Add d.Service only when not null
    if(service != null)
    {
        // d.Service
        var serviceConstant =
            Expression.Constant(service);
        var serviceProperty =
            Expression.Property(
                variable,
                "Service");

        // d.Service == service
        var serviceEquals =
            Expression.Equal(
                serviceProperty,
                serviceConstant);

        andExpression =
            Expression.AndAlso(
                andExpression,
                serviceEquals);
    }

    // Creates a lambda to represent the logic
    var parameter = Expression.Parameter(typeof(SomeType));
    return Expression
        .Lambda<Func<SomeType, bool>>(
            andExpression,
            parameter);
}

Answer 2

Your syntax is wrong

 .Where(d => site == null 
          ? d.stuff_id == org 
                 && d.Date.Month == q && d.Date.Year == year 
                 && d.Q1 != (int?)null
          : d.stuff_id == org
                 && d.Service == site && d.Date.Month == q 
                 && d.Date.Year == year && d.Q1 != (int?)null)

Answer 3

From the way your code looks, it appears to me that you're using Entity Framework for your query. If so, you aren't allowed things like d.Date.Month, since EF doesn't know how to properly translate that into SQL by itself. You need to use the SqlFunctions class (specifically the DatePart method) in order to get this query working. Using @Servy's solution as a start:

var query = db.tbl_table12345
    .Where(d => d.stuff_id == org
             && SqlFunctions.DatePart("MONTH", d.Date) == q
             && SqlFunctions.DatePart("YEAR", d.Date) == year
             && d.Q1 != (int?)null);
if (site != null)
    query = query.Where(d => d.Service == site);

var data = query.GroupBy(d => d.Q1)
    .Select(d => new
    {
        q1 = d.Key,
        total = d.Count()
    });

Another good reason to use this approach is that all of the LINQ clauses above (Where, GroupBy, and Select) all employ deferred execution (see here for a list of deferred vs. immediate executed methods), which means that only one query will be sent to your database for the final data, and only when you actually use that variable in some way.

Answer 4

If you want to add an additional filter to the query when a condition is met then that construct should be outside of the query itself, which LINQ makes quite easy to do:

var query = db.tbl_table12345
    .Where(d => d.stuff_id == org
                    && d.Date.Month == q
                    && d.Date.Year == year
                    && d.Q1 != (int?)null);
if (site != null)
    query = query.Where(d => d.Service == site);
var data = query.GroupBy(d => d.Q1)
    .Select(d => new
    {
        q1 = d.Key,
        total = d.Count()
    });

Answer 5

It should work if the ternary operator was "inside" the lambda.

string site = null;
int q = a number;

var data = db.tbl_table12345
             .Where(d => site == null 
                    ? d.stuff_id == org 
                      && d.Date.Month == q 
                      && d.Date.Year == year 
                      && d.Q1 != (int?)null 
                    : d.stuff_id == org 
                      && d.Service == site 
                      && d.Date.Month == q 
                      && d.Date.Year == year 
                      && d.Q1 != (int?)null)
             .GroupBy(d => d.Q1)
             .Select(d => new 
                    { 
                         q1 = d.Key,
                         total = d.Count() 
                    });