C# - Dynamic Linq - Generic Searching - DateTime

Question

Good morning everyone, I have a question about linq to sql. Specifically "Dynamic Linq". I'm trying to create a search function where the user can enter any string and it'd filter all fields including DateTime fields.

So say the DateTime stored is "10/11/2015", when the user types in "10" I would like the record to return.

So something like:

x=> x.dateTime.ToString("dd/mm/yyyy").Contains("10")

but that doesn't work because sql doesn't have a ToString method.

To make this problem more interesting, all of this is generic. So I receive the properties that must be searched as a list of strings, I receive the type as T and I receive the search string as a string.

So heres an example of how I would write the search expression if I were just looking for "10" in the property "dateTime", that is, if T were a generic Type.

ConstantExpression searchArgument = Expression.Constant("10");
ParameterExpression param = Expression.Parameter(typeof(T), "x");

// Get Property, even if nested property.
Expression property = "dateTime".Split('.').Aggregate<string, Expression>(param, Expression.Property);

// Get Contains method for property type
MethodInfo containsMethod = typeof(String).GetMethod("Contains");

// Convert the property if necessary.
MethodInfo convertMethod = null;
if (TypeExtensions.IsNumericType(property.Type))
{
    convertMethod = typeof(SqlFunctions).GetMethod("StringConvert", new[] { typeof(double?) });
    if (convertMethod != null)
    {
        property = Expression.Call(convertMethod, Expression.Convert(property, typeof(double?)));
    }
}
else if (property.Type == typeof(DateTime))
{
    throw new NotImplementedException();
    // TODO - How do I write a convertion method here to convert the DateTime property to a string as "dd/mm/yyyy"?
    convertMethod = null;
    if (convertMethod != null)
    {
        property = Expression.Call(convertMethod, Expression.Convert(property, typeof(DateTime?)));
    }
}

MethodCallExpression fieldExpression = Expression.Call(property, containsMethod, searchArgument);

// Create the contains expression
Expression<Func<T, bool>> searchExpression = Expression.Lambda<Func<T, bool>>(fieldExpression, param);

This works for strings and numbers but not for DateTimes, can anyone help? Thanks in advance!

Have you tried using [`SqlFunctions.StringConvert`](https://msdn.microsoft.com/en-us/library/dd466166.aspx)? — vgru, Nov 12 '15 at 11:10
@Jcl: thanks, yes, crap, I just actually clicked the link to see its overloads. :).. Perhaps some `SqlFunctions.DateName` might help then. The one-liner explanation for this question should then simply be: "How to convert datetime to string inside a LINQ to Entities query". — vgru, Nov 12 '15 at 11:17
@Groo, I have yes, but StringConvert does not support DateTime. — Rian Mostert, Nov 12 '15 at 11:17
@RianMostert: `SqlFunctions.DateName` would likely help, like shown in [this answer](http://stackoverflow.com/a/16364548/69809). You would have to check [individual parts of the date](https://msdn.microsoft.com/en-us/library/ms174395.aspx), though. Possibly a duplicate of [this question](http://stackoverflow.com/q/5370402/69809). — vgru, Nov 12 '15 at 11:19
I think, that kind of a search operation is risky. Did you check out the generated sql query? When your table's row count reached to a few hundred thousands, then your query can be really slow. If you must search in that date column, perhaps you should add one more column (string representation of datetime) to the table for search operations. — Kemal Kefeli, Nov 12 '15 at 11:49

Jcl · Answer 1 · 2015-11-12T11:27:32.873

1

I'd need to doublecheck this, but according to https://msdn.microsoft.com/en-us/library/bb738681.aspx , Day, Month and Year should work, so instead of doing one comparison, you could do something like:

(x.dateTime.Day.ToString().Contains("10") || 
 x.dateTime.Month.ToString().Contains("10") ||
 x.dateTime.Year.ToString().Contains("10"))

I recall EF understands ToString() on integers, though I'm not sure, but if it doesn't, use SqlFunctions.StringConvert above, instead of ToString()

Or you could make the string yourself using SqlFunctions instead of calling ToString, something like:

(SqlFunctions.DateName("dd", x) + "/" +
 SqlFunctions.StringConvert((double)SqlFunctions.DatePart("m", x)).Trim() + "/" +
 SqlFunctions.DateName("yyyy", x)
).Contains("10")

edited Nov 12 '15 at 11:27

answered Nov 12 '15 at 11:20

Jcl

27,696
5
61
92

1

Regarding the last remark, `"1/12/2015"` would then be concatenated to the same string as `"11/2/2015"`, and it would match positively for `112`, `22`, `220` etc., which is probably not what OP wants. – vgru Nov 12 '15 at 11:26
@Groo that is right, I was thinking of a "find a 10 in the string no matter where is it" (which is weird enough), but true that, I'll remove the remark :-) – Jcl Nov 12 '15 at 11:27

score 0 · Answer 2 · answered Nov 12 '15 at 11:19

You can also compare by components, check if the day, month or year is equal to the variable your are passing. I dont know if you would also like. for example, if the user puts like "1" return all records with a date that contains 1, but that i dont advise either, you would be returning a lot of records if your database has some dimension

C# - Dynamic Linq - Generic Searching - DateTime

2 Answers2