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

Answer 1

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