How to compare only date part with linq expression?

Question

I just want to make Column Filter for grid view. Simple I just want to filter grid view column with some extra stuff. Here I have created one IQueryable that returns queryable result.

Here is my code :

------------------------------------------Updated------------------------------------------------

public static IQueryable<T> FilterForColumn<T>(this IQueryable<T> queryable, string colName, string searchText)
{
    if (colName != null && searchText != null)
    {
        var parameter = Expression.Parameter(typeof(T), "m");
        var propertyExpression = Expression.Property(parameter, colName);
        System.Linq.Expressions.ConstantExpression searchExpression = null;
        System.Reflection.MethodInfo containsMethod = null;
        System.Linq.Expressions.MethodCallExpression body = null;
        System.Nullable<DateTime> nextDate = null;
        Expression ex1 = null;
        Expression ex2 = null;
        switch (colName)
        {
            case "JobID":
            case "FileSize":
            case "TotalFileSize":
                Int64? size = Convert.ToInt64(searchText);
                searchExpression = Expression.Constant(searchText);
                containsMethod = typeof(Int64?).GetMethod("Equals", new[] { typeof(Int64?) });
                body = Expression.Call(propertyExpression, containsMethod, searchExpression);
                break;
            case "PublishDate":
            case "Birth_date":
            case "Anniversary_date":
            case "Profile_Updated_datetime":
            case "CompletedOn":
                DateTime? currentDate = DateTime.ParseExact(searchText, "dd/MM/yyyy", null);
                nextDate = currentDate.Value.AddDays(1);
                ex1 = Expression.GreaterThanOrEqual(propertyExpression, Expression.Constant(currentDate));
                ex2 = Expression.LessThan(propertyExpression, Expression.Constant(nextDate));
                body = Expression.AndAlso(ex1, ex2);
                break;
            case "Created_datetime":
            case "Reminder_Date":
            case "News_date":
            case "thought_date":
            case "SubscriptionDateTime":
            case "Register_datetime":
            case "CreatedOn":
                DateTime dt1 = DateTime.ParseExact(searchText, "dd/MM/yyyy", null);
                nextDate = currentDate.Value.AddDays(1);
                ex1 = Expression.GreaterThanOrEqual(propertyExpression, Expression.Constant(currentDate));
                ex2 = Expression.LessThan(propertyExpression, Expression.Constant(nextDate));
                body = Expression.AndAlso(ex1, ex2);
                break;
            default :
                searchExpression = Expression.Constant(searchText);
                containsMethod = typeof(string).GetMethod("Contains", new[] { typeof(string) });
                body = Expression.Call(propertyExpression, containsMethod, searchExpression);
                break;
        }
        var predicate = Expression.Lambda<Func<T, bool>>(body, new[] { parameter });
        return queryable.Where(predicate);
    }
    else
    {
        return queryable;
    }
}

here this solution gives some compile time error :

Error   9   Cannot implicitly convert type 'System.Linq.Expressions.BinaryExpression' to 'System.Linq.Expressions.MethodCallExpression' F:\EasyWeb\App_Code\Helper.cs   47  28  F:\EasyWeb\

Here at case of DateTime I just want to filter DateTime column with only Date not whole DATETIME value.

Here I just put required things :

SELECT [t8].[Id], [t8].[Title], [t8].[value] AS [Publisher], [t8].[value2] AS [ToUser], [t8].[Sent_Datetime] AS [PublishDate], [t8].[IsFileAttached] AS [IsFile], [t8].[Category_name] AS [CategoryName], [t8].[value3] AS [status_name], [t8].[value4] AS [Group_name], [t8].[TotalFileSize] AS [FileSize]
FROM (
    SELECT [t0].[Id], [t0].[Title], (
        SELECT TOP (1) [t3].[value]
        FROM (
            SELECT ([t2].[First_name] + ' ') + [t2].[Last_name] AS [value], [t2].[Id]
            FROM [dbo].[tbl_User_master] AS [t2]
            ) AS [t3]
        WHERE [t3].[Id] = [t0].[User_id]
        ) AS [value], (
        SELECT TOP (1) [t5].[value]
        FROM (
            SELECT ([t4].[First_name] + ' ') + [t4].[Last_name] AS [value], [t4].[Id]
            FROM [dbo].[tbl_User_master] AS [t4]
            ) AS [t5]
        WHERE ([t5].[Id]) = [t0].[ToUser_id]
        ) AS [value2], [t0].[Sent_Datetime], [t0].[IsFileAttached], [t1].[Category_name], (
        SELECT TOP (1) [t6].[status_name]
        FROM [dbo].[tbl_status_master] AS [t6]
        WHERE ([t6].[Id]) = [t0].[status_id]
        ) AS [value3], (
        SELECT TOP (1) [t7].[Group_name]
        FROM [dbo].[tbl_Group_master] AS [t7]
        WHERE ([t7].[Id]) = [t0].[group_id]
        ) AS [value4], [t0].[TotalFileSize], [t0].[ToUser_id], [t0].[User_id]
    FROM [dbo].[tbl_Post_History] AS [t0]
    INNER JOIN [dbo].[tbl_Category_master] AS [t1] ON [t0].[Category_id] = [t1].[Id]
    ) AS [t8]
WHERE (CAST(CONVERT(CHAR(10), [t8].[Sent_Datetime], 102) AS DATETIME) = '12/24/2013' ) AND (([t8].[ToUser_id] = 3) OR ([t8].[ToUser_id] IS NULL)) AND ([t8].[User_id] <> 3)
ORDER BY [t8].[Sent_Datetime] DESC

Here this shows required output or logic that we will do with queryable.

But, here one drawback occurs while this method Equals check whole Date Time. Is that possible with this method to force only check Date Part from column?

------------------------------------2-Updated-----------------------------------------

for overwhelming this problem i use this technique :

 public static IQueryable<T> FilterForColumn<T>(this IQueryable<T> queryable, string colName, string searchText)
    {
        if (colName != null && searchText != null)
        {
            var parameter = Expression.Parameter(typeof(T), "m");
            var propertyExpression = Expression.Property(parameter, colName);
            System.Linq.Expressions.ConstantExpression searchExpression = null;
            System.Reflection.MethodInfo containsMethod = null;
            System.Linq.Expressions.BinaryExpression body = null;
            DateTime? currentDate = null;
            DateTime? nextDate = null;
            Expression ex1 = null;
            Expression ex2 = null;
            switch (colName)
            {
                case "JobID":
                case "FileSize":
                case "TotalFileSize":
                    Int64? size = Convert.ToInt64(searchText);
                    searchExpression = Expression.Constant(searchText);
                    containsMethod = typeof(Int64?).GetMethod("Equals", new[] { typeof(Int64?) });
                    //body = Expression.Call(propertyExpression, containsMethod, searchExpression);
                    break;
                case "PublishDate":
                case "Birth_date":
                case "Anniversary_date":
                case "Profile_Updated_datetime":
                case "CompletedOn":
                    currentDate = DateTime.ParseExact(searchText, "dd/MM/yyyy", null);
                    nextDate = currentDate.Value.AddDays(1);
                    ex1 = Expression.GreaterThanOrEqual(propertyExpression, Expression.Constant(currentDate));
                    ex2 = Expression.LessThan(propertyExpression, Expression.Constant(nextDate));
                    body = Expression.AndAlso(ex1, ex2);
                    break;
                case "Created_datetime":
                case "Reminder_Date":
                case "News_date":
                case "thought_date":
                case "SubscriptionDateTime":
                case "Register_datetime":
                case "CreatedOn":
                    currentDate = DateTime.ParseExact(searchText, "dd/MM/yyyy", null);
                    nextDate = currentDate.Value.AddDays(1);
                    ex1 = Expression.GreaterThanOrEqual(propertyExpression, Expression.Constant(currentDate));
                    ex2 = Expression.LessThan(propertyExpression, Expression.Constant(nextDate));
                    body = Expression.AndAlso(ex1, ex2);
                    break;
                default :
                    searchExpression = Expression.Constant(searchText);
                    containsMethod = typeof(string).GetMethod("Contains", new[] { typeof(string) });
                    //body = Expression.Call(propertyExpression, containsMethod, searchExpression);
                    break;
            }
            var predicate = Expression.Lambda<Func<T, bool>>(body, new[] { parameter });
            return queryable.Where(predicate);
        }
        else
        {
            return queryable;
        }
    }

it's give me run time error like :

Server Error in '/EasyWeb' Application.

The binary operator GreaterThanOrEqual is not defined for the types 'System.Nullable`1[System.DateTime]' and 'System.DateTime'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.InvalidOperationException: The binary operator GreaterThanOrEqual is not defined for the types 'System.Nullable`1[System.DateTime]' and 'System.DateTime'.

Source Error: 


Line 44:                     currentDate = DateTime.ParseExact(searchText, "dd/MM/yyyy", null);
Line 45:                     nextDate = currentDate.Value.AddDays(1);
Line 46:                     ex1 = Expression.GreaterThanOrEqual(propertyExpression, Expression.Constant(currentDate));
Line 47:                     ex2 = Expression.LessThan(propertyExpression, Expression.Constant(nextDate));
Line 48:                     body = Expression.AndAlso(ex1, ex2);

Source File: f:\EasyWeb\App_Code\Helper.cs    Line: 46 

Stack Trace: 


[InvalidOperationException: The binary operator GreaterThanOrEqual is not defined for the types 'System.Nullable`1[System.DateTime]' and 'System.DateTime'.]
   System.Linq.Expressions.Expression.GetUserDefinedBinaryOperatorOrThrow(ExpressionType binaryType, String name, Expression left, Expression right, Boolean liftToNull) +752213
   System.Linq.Expressions.Expression.GetComparisonOperator(ExpressionType binaryType, String opName, Expression left, Expression right, Boolean liftToNull) +221
   System.Linq.Expressions.Expression.GreaterThanOrEqual(Expression left, Expression right, Boolean liftToNull, MethodInfo method) +53
   System.Linq.Expressions.Expression.GreaterThanOrEqual(Expression left, Expression right) +8
   Helper.FilterForColumn(IQueryable`1 queryable, String colName, String searchText) in f:\EasyWeb\App_Code\Helper.cs:46
   Admin_Post_History.FillGrid(String CommandName, String ColumnName, String SearchText) in f:\EasyWeb\Admin\Post_History.aspx.cs:79
   Admin_Post_History.btnsearch_Click(Object sender, EventArgs e) in f:\EasyWeb\Admin\Post_History.aspx.cs:2375
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +110
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

Version Information: Microsoft .NET Framework Version:2.0.50727.3053; ASP.NET Version:2.0.50727.3053

-------------------------------- 3 Update-------------------------------------------

here is what i'm querying :

case "Inbox":
                        lbl_disply.Text = "Inbox";
                        lbut_showinbox.Font.Bold = true;
                        lbut_showoutbox.Font.Bold = false;
                        lbut_showdraffs.Font.Bold = false;
                        lbut_showtrash.Font.Bold = false;
                        var query1 = db.Posts.Where(p => (p.ToUser_id.Equals(user_id) || p.ToUser_id.Equals(null)) && p.User_id != user_id).OrderByDescending(p=>p.Sent_Datetime).Select(p => new
                        {
                            Id = p.Id,
                            Title = p.Title,
                            Publisher = db.Users.Where(u => u.Id.Equals(p.User_id)).Select(u => u.First_name + ' ' + u.Last_name).FirstOrDefault(),
                            ToUser = db.Users.Where(u => u.Id.Equals(p.ToUser_id)).Select(u => u.First_name + ' ' + u.Last_name).FirstOrDefault(),
                            PublishDate = p.Sent_Datetime,
                            IsFile = p.IsFileAttached,
                            CategoryName = db.Categories.Where(c => c.Id.Equals(p.Category_id)).Select(c => c.Category_name).FirstOrDefault(),
                            status_name = db.Status.Where(s => s.Id.Equals(p.status_id)).Select(s => s.status_name).FirstOrDefault(),
                            Group_name = db.Groups.Where(g => g.Id.Equals(p.group_id)).Select(g => g.Group_name).FirstOrDefault(),
                            FileSize = p.TotalFileSize
                        }).FilterForColumn(ColumnName, SearchText).ToList();

at grid view filling

DataSet myDataSet = new DataSet();
                        DataTable dt = new DataTable();
                        dt.Columns.Add(new DataColumn("Id", typeof(int)));
                        dt.Columns.Add(new DataColumn("IsRead", typeof(bool)));
                        dt.Columns.Add(new DataColumn("IsImp", typeof(bool)));
                        dt.Columns.Add(new DataColumn("Title", typeof(string)));
                        dt.Columns.Add(new DataColumn("Publisher", typeof(string)));
                        dt.Columns.Add(new DataColumn("ToUser", typeof(string)));
                        dt.Columns.Add(new DataColumn("PublishDate", typeof(DateTime?)));
                        dt.Columns.Add(new DataColumn("IsFile", typeof(bool)));
                        dt.Columns.Add(new DataColumn("CategoryName", typeof(string)));
                        dt.Columns.Add(new DataColumn("status_name", typeof(string)));
                        dt.Columns.Add(new DataColumn("Group_name", typeof(string)));
                        dt.Columns.Add(new DataColumn("FileSize", typeof(string)));
                        foreach (var item in query1)

i declared this PublishDate as typeof(DateTime?) but this gives me run time error like :

Server Error in '/EasyWeb' Application.

DataSet does not support System.Nullable<>.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.NotSupportedException: DataSet does not support System.Nullable<>.

Source Error: 


Line 101:                        dt.Columns.Add(new DataColumn("Publisher", typeof(string)));
Line 102:                        dt.Columns.Add(new DataColumn("ToUser", typeof(string)));
Line 103:                        dt.Columns.Add(new DataColumn("PublishDate", typeof(DateTime?)));
Line 104:                        dt.Columns.Add(new DataColumn("IsFile", typeof(bool)));
Line 105:                        dt.Columns.Add(new DataColumn("CategoryName", typeof(string)));

Source File: f:\EasyWeb\Admin\Post_History.aspx.cs    Line: 103 

Stack Trace: 


[NotSupportedException: DataSet does not support System.Nullable<>.]
   System.Data.DataColumn..ctor(String columnName, Type dataType, String expr, MappingType type) +4826536
   System.Data.DataColumn..ctor(String columnName, Type dataType) +12
   Admin_Post_History.FillGrid(String CommandName, String ColumnName, String SearchText) in f:\EasyWeb\Admin\Post_History.aspx.cs:103
   Admin_Post_History.Page_Load(Object sender, EventArgs e) in f:\EasyWeb\Admin\Post_History.aspx.cs:59
   System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
   System.Web.UI.Control.OnLoad(EventArgs e) +99
   System.Web.UI.Control.LoadRecursive() +50
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +627

Version Information: Microsoft .NET Framework Version:2.0.50727.3053; ASP.NET Version:2.0.50727.3053

---------------------------------4 Update----------------------------------------

i check my dbml file and this is screen shot for verify that it's nullable

Killigrew

You could employ functionalities offered in the SqlFunctions class, see: msdn.microsoft.com/en-us/library/… – Midian

In such cases doing a range check usually is the best option (date <= datetimeColumn < date + 1 day). Truncating the database value before filtering disables the use of indexes. – Mestas

@Midian - which function provide this feature. – Killigrew

@GertArnold - can u set ur range check logic to my question or give me brief solution with explanation. how ever this is important feature for me filtering date value with only date part. – Killigrew

Answer 1

To achieve what you want you can use predicate

WHERE Sent_Datetime >= '20131224' AND Sent_Datetime < '20131225'

so you can use this expression

DateTime currentDate = DateTime.ParseExact("06/01/2008", "dd/MM/yyyy", null);
DateTime nextDate = currentDate.AddDays(1);

Expression ex1 = Expression.GreaterThanOrEqual(
                       propertyExpression, Expression.Constant(currentDate));
Expression ex2 = Expression.LessThan(
                       propertyExpression, Expression.Constant(nextDate));
Expression body = Expression.AndAlso(ex1, ex2);

var predicate = Expression.Lambda<Func<T, bool>>(body, new[] { parameter });

Of course, here you have sargable predicate.

Update

I've created the complete example for you:

public static IQueryable<T> FilterForColumn<T>(this IQueryable<T> queryable, string colName, string searchText)
{
   if (colName != null && searchText != null)
   {
       var parameter = Expression.Parameter(typeof(T), "m");
       var propertyExpression = Expression.Property(parameter, colName);
       System.Linq.Expressions.ConstantExpression searchExpression = null;
       System.Reflection.MethodInfo containsMethod = null;
       // this must be of type Expression to accept different type of expressions
       // i.e. BinaryExpression, MethodCallExpression, ...
       System.Linq.Expressions.Expression body = null;
       Expression ex1 = null;
       Expression ex2 = null;
       switch (colName)
       {
           case "JobID":
           case "FileSize":
           case "TotalFileSize":
               Int64? size = Convert.ToInt64(searchText);
               searchExpression = Expression.Constant(searchText);
               containsMethod = typeof(Int64?).GetMethod("Equals", new[] { typeof(Int64?) });
               body = Expression.Call(propertyExpression, containsMethod, searchExpression);
               break;
           // section for DateTime? properties
           case "PublishDate":
           case "Birth_date":
           case "Anniversary_date":
           case "Profile_Updated_datetime":
           case "CompletedOn":
               DateTime currentDate = DateTime.ParseExact(searchText, "dd/MM/yyyy", null);
               DateTime nextDate = currentDate.AddDays(1);
               ex1 = Expression.GreaterThanOrEqual(propertyExpression, Expression.Constant(currentDate, typeof(DateTime?)));
               ex2 = Expression.LessThan(propertyExpression, Expression.Constant(nextDate, typeof(DateTime?)));
               body = Expression.AndAlso(ex1, ex2);
               break;
           // section for DateTime properties
           case "Created_datetime":
           case "Reminder_Date":
           case "News_date":
           case "thought_date":
           case "SubscriptionDateTime":
           case "Register_datetime":
           case "CreatedOn":
               DateTime currentDate = DateTime.ParseExact(searchText, "dd/MM/yyyy", null);
               DateTime nextDate = currentDate.AddDays(1);
               ex1 = Expression.GreaterThanOrEqual(propertyExpression, Expression.Constant(currentDate));
               ex2 = Expression.LessThan(propertyExpression, Expression.Constant(nextDate));
               body = Expression.AndAlso(ex1, ex2);
               break;
           default :
               searchExpression = Expression.Constant(searchText);
               containsMethod = typeof(string).GetMethod("Contains", new[] { typeof(string) });
               body = Expression.Call(propertyExpression, containsMethod, searchExpression);
               break;
       }
       var predicate = Expression.Lambda<Func<T, bool>>(body, new[] { parameter });
       return queryable.Where(predicate);
   }
   else
   {
       return queryable;
   }
}

Noncombatant

here i'm set ur logic to my code but there is compile time error occured. just see my updated question. – Killigrew

and of course i can't use this technique with Nullable Date Time column it's give me error. see second updated question. – Killigrew

The exception Cannot implicitly convert type 'System.Linq.Expressions.BinaryExpression' to 'System.Linq.Expressions.MethodCallExpression' is caused, because you have declared body as MethodCallExpression but it is BinaryExpression. – Noncombatant

You have correct solution for nullable in you first example. Just declare currentDate and nextDate as nullable. The problem is that the Expression.GreaterThanOrEqual requires consistency on nullablility. – Noncombatant

Declare currentDate and nextDate in non-nullable section as non-null, and in nullable section as nullable. – Noncombatant

ok this secnario works. here my body variable is common for all cases and this can be System.Linq.Expressions.BinaryExpression or System.Linq.Expressions.MethodCallExpression. the problem here is how i declared body common for others. – Killigrew

ya. but still problem occurs from section for DateTime? properties. like: The binary operator GreaterThanOrEqual is not defined for the types 'System.Nullable`1[System.DateTime]' and 'System.DateTime'. – Killigrew

If you have such error you must examine your case section. There is property listed in nullable section which is non-nullable. I think the Birth_date isn't nullable. – Noncombatant

all case that i included as nullable they all column has been checked as Allow Nulls – Killigrew

Allow nulls in DB doesn't mean that in model they are declared as nullable. You must check your model. – Noncombatant

In my LINQTOSQL classes all fields that are include in DateTime? cases are Nullable ="True" i check it again. – Killigrew

OK, but all properties in nullable section (PublishDate, Birth_date, Anniversary_date, Profile_Updated_datetime, CompletedOn) are declared as DateTime?. – Noncombatant

ok ok. i got you at grid view filling i have to manually set Datetime? like here dt.Columns.Add(new DataColumn("PublishDate", typeof(DateTime?))); which is i declared as dt.Columns.Add(new DataColumn("PublishDate", typeof(DateTime))); – Killigrew

If you want that PublishDate to be nullable, then you must declare it is. – Noncombatant

Why you change your DataTable structure? No need to do this. You must look at you dbml file. – Noncombatant

i have some problem if i declared it typeof(Datetime?) at grid view data column declaration. – Killigrew

And why you create DataTable` for your query result. You can create enumerabe of anonymous type and pass it as datasource to datagrid. – Noncombatant

ok. let focus on main problem here i just include my dbml screen shot that show you nullable confirmation. – Killigrew

can u give me ur suggestion with sample code for passing query result as enumerable. – Killigrew

From you screenshot I see that the Sent_Datetie is nullable, but it isn't in any section of your case. You must check properties in your cases by dbml to identify which property is nullable and which is not. – Noncombatant

i know it's good practice with directly passing query result here i just want to myDataView for allowing sorting facility. – Killigrew

this is actual properties of DB. And at select new section i use it like PublishDate = p.Sent_Datetime. – Killigrew

I don't understand. Waht you filter by this extension method? What is you IQeryable<T> ? Why you don't apply this method before projection? – Noncombatant

just see my 3rd updated section. you definitely understood my problem. – Killigrew

Can you print query1.GetType().ToString() and show it? – Noncombatant

System.Collections.Generic.List1[<>f__AnonymousType510[System.Int32,System.String,System.String,System.String,System.Nullable1[System.DateTime],System.Boolean,System.String,System.String,System.String,System.Nullable1[System.Int64]]] – Killigrew

I have figured out your problem. The Expression.Constant creates non-nullable constant expression on nullable. IMO it is because that the GetType() of nullable object returns Type of its underlying type. To get constant expression that holds nullable object you can use Expression.Constant(object, Type). I have updated my answer. – Noncombatant

thanks u helped me a lot. this filter with only date great feature for my project. – Killigrew

You are welcome. I have made minor changes in example. You can pass non-nullable date to Expression.Constant in case if you explicitly pass the type. – Noncombatant

then i don't panic of nullable and non-nullable datetime value – Killigrew

Answer 2

You can use the SqlFunctions.DateDiff function specifying "day" value as datepart parameter.

var result = entities
    .Where(item =>
        SqlFunctions.DateDiff("day", item.DateProperty, dateToCompare) == 0);

Afterclap

can u set this logic to my question. – Killigrew

Here my Sql column is date time now date time value with time which is hide by grid view at user view. now just want to make one feature that user can type only date part and then it's searched out of only date value without checking time part value. – Killigrew

@shal No, it is will not work in .NET 3.5. Also it is not sargable predicate. – Noncombatant