Dynamic LINQ (to entities) Where with nullable DateTime column

Question

I have been banging my head on this problem for sometime. There are some similar cases, but the solutions weren't applicable on my case.

I have a method that returns filter query in string format. The method has logic for different data types, sets correct values, column names etc.

string filterQuery = GetFilterQuery(params);
rows = rows.Where(filterQuery);

My problem is that I have Nullable DateTime in the database and I have String representation in the code side.

I have tried following queries (String representation might be wrong currently):

"BirthDate.ToString() = \"16.2.2012 22:00:00\""

Result: Methods on type 'DateTime?' are not accessible

"BirthDate.Value.ToString() = \"16.2.2012 22:00:00\""

Result: LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression.

"BirthDate == null ? 1=1 : (DateTime)BirthDate.ToString() = \"16.2.2012 22:00:00\""

Result: '.' or '(' expected

Any ideas how to solve the problem?

Update (more source code added about query generation)

var filterQueries = query.GridFilteringOptions.filters
    // remove filters that doesn't have all the required information
    .Where(o => o.name != string.Empty && o.value != string.Empty && !string.IsNullOrEmpty(o.type))
    // remove filters that are filtering other tables than current
    .Where(o => o.table == tableName) 
    .Select(filter => filter.ResolveQuery()).ToList();

if (filterQuery.Any())
{
    var filterQuery = string.Join(" And ", filterQueries);
    rows = rows.Where(filterQuery);
}

And here is a class Filter and methods are related to this context

public string ResolveQuery()
{
    if (type == "Int64")
    {
        return ResolveInteger();
    }
    else if(type == "String")
    {
        return ResolveString();
    }
    else if(type == "DateTime")
    {
        return ResolveDateTime();
    }
    else
    {
        return string.Empty;
    }
}

private string ResolveDateTime()
{
    DateTime result = new DateTime();
    if (DateTime.TryParse(this.value, out result))
    {
        return string.Format("{0}.ToString() = \"{1}\"", this.name, result.ToUniversalTime());
    }
    return string.Empty;
}

private string ResolveString()
{
    switch (@operator)
    {
        default:
            return string.Format(@"{0}.StartsWith(""{1}"")", this.name, this.value);
    }            
}

private string ResolveInteger()
{
    string tmp = this.name;
    switch (@operator)
    {
        case -1:
            return string.Empty;
        case 0:
            tmp += "<";
            break;
        case 1:
            tmp += "=";
            break;
        case 2:
            tmp += ">";
            break;
        default:
            return string.Empty;
    }
    tmp += value;
    return tmp;
}

Answer 1

I know my answer is a bit late but for every one that is still struggling with the same case you can try this solution

DateTime.Parse(BirthDate.ToString()).Date = DateTime.Parse(\"16.2.2012 22:00:00\"")

This way it will work wether BirthDate is DateTime or DateTime?. It is better to check if BirthDate is not null before

BirthDate != null && DateTime.Parse(BirthDate.ToString()).Date = DateTime.Parse(\"16.2.2012 22:00:00\""

The method .ToString() works for both datetime and nullable dateTime

Answer 2

I currently solve this with a try/catch block. That adds some overhead and ugliness that I would rather find an alternative for, but it does work very solidly:

try
{
    // for strings and other non-nullables
    records = records.Where(rule.field + ".ToString().Contains(@0)", rule.data);
}
catch (System.Linq.Dynamic.ParseException)
{
    // null types
    records = records.Where(rule.field + ".Value.ToString().Contains(@0)", rule.data);
}

Note: Accepting those fields from input variables (the rule.field and rule.data) is dangerous, and may open you up to a (difficult) blind SQL Injection. The first value (rule.field) can be checked against a whitelist of values, to prevent this.

Answer 3

LINQ to Entities doesn't recognize the ToString() method. You would have to evaluate it before inserting the resulting string into your query.

To create the examples in your question you might handle it like this:

// "BirthDate.ToString() = \"16.2.2012 22:00:00\""
string birthdate = BirthDate.ToString();
string query = String.Format("{0}  = \"16.2.2012 22:00:00\"", birthdate);

// "BirthDate.Value.ToString() = \"16.2.2012 22:00:00\""
string birthdate = BirthDate.Value.ToString();
string query = String.Format("{0}  = \"16.2.2012 22:00:00\"", birthdate);

"BirthDate == null ? 1=1 : (DateTime)BirthDate.ToString() = \"16.2.2012 22:00:00\"" probably doesn't work because LINQ to EF doesn't recognize the ternary operator ( ? : )

Edit: I understand from your comment that BirthDate is a column in your table, not a variable. In this case you can retrieve all entries, convert them to a list and then apply the filter using LINQ to Objects like this (although you would have to modify your filterQuery accordingly):

string filterQuery = GetFilterQuery(params);
var filteredRows = rows.ToList().Where(filterQuery);

Untested: It might be possible to use your database's CONVERT function:

string query = "CONVERT(varchar(20), BirthDate) = \"16.2.2012 22:00:00\"";

Answer 4

My problem is that I have Nullable DateTime in the database and I have String representation in the code side.

Why?

Change the code side, parse the string representation and give it the proper object in the LINQ query.

"BirthDate.ToString() = \"16.2.2012 22:00:00\""

BAD - IF you have to do string manipulatzion, AWLWAYS use InvariantCulture. This code is fragile and will break in any other country.