Entity Framework 6 ToString(), formatting (DateTime format), query intercept

Question

I`m not found correct way to search with linq2sql in DateTime (DateTime?) fields.

db.Items.Where(x => x.DateTime1.ToString().Contains("2014.08"))

Not work, because in linq2sql create CAST([XXXX.DateTime1] AS NVARCHAR(MAX)) = '04 Aug 2014' NOT 2014.08

I try use custom function mapping, but no result

Yuliam Chandra · Accepted Answer · 2014-08-08T18:12:29.410

4

Why don't you just use the Year and Month property? You should be able to convert the string input into Year and Month number. Then you do something like:

db.Items.Where(x => 
   x.DateTime1.Value.Year == 2014 
   && x.DateTime1.Value.Month == 8)

It will simply be converted to:

WHERE (2014 = (DATEPART (year, [Extent1].[Date]))) 
AND     (8 = (DATEPART (month, [Extent1].[Date])))

update

You can use SqlFunctions.DatePart and DbFunctions.Right to produce following format yyyy.mm.dd.

db.Items.Where(x => 
    (SqlFunctions.DatePart("yyyy", x.DateTime) + "."
    + DbFunctions.Right("0" + SqlFunctions.DatePart("m", x.DateTime1), 2) + "."
    + DbFunctions.Right("0" + SqlFunctions.DatePart("d", x.DateTime1), 2))
    .Contains("2014.08"));

edited Aug 08 '14 at 18:12

answered Aug 08 '14 at 13:36

Yuliam Chandra

14,494
12
52
67

User type string "2014.08" or "08.08" or "14.08", etc. I need search this in datetime fields. – Alexandr Sulimov Aug 08 '14 at 14:42
@AlexandrSulimov, I updated my answer. It should produce the format you wanted. You can use this, just in case you want to prevent using interceptor because it's applied globally. – Yuliam Chandra Aug 08 '14 at 18:13
There are problem. User filter is "12.12" it`s may be "2012.12.01" or may be "2010.12.12". Or user filter is "12" it`s any 12 in any datatime. – Alexandr Sulimov Aug 08 '14 at 18:23
Hmm that should work, I tried with your sample `2012.12.01` and `2012.12.12`, and the filters are `12.12` and `12`, and both of the datetimes match both filters, because in the database those will be converted to `2012.12.01` and `2012.12.12` and `Contains` will do string filter – Yuliam Chandra Aug 09 '14 at 04:08
Thanks, you way is fine. But I can`t crete this query with Expression (http://stackoverflow.com/questions/25290720/conversion-in-linq-and-entityframework-expressions-type-without-expression-conv) – Alexandr Sulimov Aug 13 '14 at 15:54
@AlexandrSulimov, the `DatePart` accepts nullable parameter, and nullable can accept non nullable – Yuliam Chandra Aug 14 '14 at 02:25

score 0 · Answer 2 · answered Aug 07 '14 at 10:40

Function in MS SQL

CREATE FUNCTION [dbo].[ToString](@P sql_variant)
RETURNS NVARCHAR(20)
AS
BEGIN
    IF (sql_variant_property(@P, 'BaseType') = 'datetime')
        RETURN CONVERT(NVARCHAR(10), @P, 102) + ' ' + CONVERT(NVARCHAR(8), @P, 108);

RETURN CAST(@P as NVARCHAR(max));
END

Create sql execution Interceptor

public class DbCommandInterceptor  : IDbCommandInterceptor 
{
    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        if (command.CommandText.IndexOf("CAST") != -1)
        {
            command.CommandText = command.CommandText.Replace("CAST(", "dbo.ToString(");
            command.CommandText = command.CommandText.Replace("] AS nvarchar(max))", "])");

        }
    }

}

Add Interceptor to DbContext

public class DB : DbContext
{
    public DB(): base(@"Data Source=localhost\SQLEXPRESS;Initial Catalog=EFTest")
    {
        DbInterception.Add(new DbCommandInterceptor ());
    }
}

It is good as sample for study but it can't be recommended: replace each CAST( with dbo.ToString( - generally would not work since EF use CAST( by its own initiative also... — Roman Pokrovskij, Jun 20 '15 at 12:59

Entity Framework 6 ToString(), formatting (DateTime format), query intercept

2 Answers2

update

Linked