LINQ Dynamic (to entities) Where the DateTime column is nullable

c# dynamic-linq linq linq-to-entities

Question

I've been struggling with this issue for a while. Although there are several examples that are comparable to mine, the answers weren't relevant.

I have a function that gives back filter queries as strings. The technique uses accurate values, column names, and logic for various data formats.

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

The issue I'm having is that INullable DateTime inside the database, and IString coding-side representation

I've tried the following searches (String portrayal may not be accurate right now):

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

32: 37: Zzz-32: Zzz-32

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

ZZZ 44 ZZZ ZZZ ZZZ 49 ZZZ

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

56 zzzz, 61 zzzz

Any suggestions for resolving the issue?

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);
}

Here is a class, too. Methods and filters are connected 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;
}
1
5
2/14/2012 9:08:46 AM

Accepted Answer

The doesn't recognize the LINQ to EntitiesToString() method. Prior to entering the generated string into your query, you would need to assess it.

You may approach it in the following way to develop the examples in your question:

// "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\"" LINQ to EF doesn't recognize the ternary operator, thus it probably won't work.? : )

Edit: I take your statement to mean thatBirthDate is not a variable but a column in your database. Using LINQ to Objects, you may obtain all elements in this scenario, turn them into a list, and then apply the filter (although you would need to change yourfilterQuery accordingly):

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

Untested: The CONVERT function in your database could be useful here:

string query = "CONVERT(varchar(20), BirthDate) = \"16.2.2012 22:00:00\"";
2
2/14/2012 9:38:41 AM

Popular Answer

I presently use a try/catch block to resolve this. It does function pretty well, however there is some overhead and uglyness that I would prefer find a replacement for:

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);
}

Observation: Taking those fields from the input variables (therule.field and rule.data ) is risky and can expose you to a challenging blind SQL Injection. The first number is (rule.field ) may be compared to a whitelist of acceptable values to avoid this.



Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow