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