This is a migrated thread and some comments may be shown as answers.

Datagrid filtering on Date Field with Entity Framework

4 Answers 510 Views

Grid

This is a migrated thread and some comments may be shown as answers.

Kevin Kess asked on 24 Aug 2011, 08:48 PM

I'm having lots of difficulty implementing date filtering in a radgrid with entity framework. When reading the gridActions.MasterTableView.FilterExpression value for a date field on my page I get the following filter back : (ActionDate = DateTime.Parse("8/10/2011 12:00:00 AM")). However entity framework is giving me an error for that type of syntax. It doesn't like the DateTime.Parse. It's a LINQ to Entity limitation. I'm trying to manually setup the filter, and I've had some success doing it, but I can't seem to figure out how to properly do it. Right now what I have is working to limit by date if it is the same date. But if the user picks greater then or less then (etc;) I don't know how to handle that correctly. Can you give me some advice on either a better approach, or how to fully correctly implement it. Again I'm using Entity Framework 4.0 on the backend, and I'm calling a DAL layer for information.

The error I'm getting if trying to use the direct filter expression is:
"LINQ to Entities does not recognize the method 'System.DateTime ToDateTime(System.String)' method, and this method cannot be translated into a store expression."

I'll include the code that I'm using below as well.

Thanks so much,
Kevin

Dashboard.aspx page:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="dashboard.aspx.cs" Inherits="SearchDesk.billing.Dashboard" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <telerik:RadGrid Width="700px" runat="server" ID="gridActions" OnItemDataBound="GridActionsItemDataBound"
               OnNeedDataSource="GridActionsNeedDataSource" AutoGenerateColumns="False" OnItemCommand="getitem"
               AllowPaging="True" GridLines="None" Skin="Windows7" OnSortCommand="gridActionSort"
            AllowFilteringByColumn="True" AllowSorting="True" CellSpacing="0">

                  <telerik:GridTemplateColumn AllowFiltering="false" UniqueName="buttons" HeaderText="Actions" ItemStyle-Width="200">
                  <ItemTemplate>
                     <asp:Panel runat="server" ID="CreateInvoice" Visible="false">
                     <asp:ImageButton runat="server" ID="button1" Height="16" Width="16" ImageUrl="~/Images/newIcon.jpg" title="Create New Invoice" AlternateText="Create New Invoice" PostBackUrl='<%# "~/CreateNew.aspx?AID=" + DataBinder.Eval(Container.DataItem,"ActionID") %>'     />

                    </asp:Panel>
                  </ItemTemplate>
                  </telerik:GridTemplateColumn>
                </Columns>

Backend aspx.cs page:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using SearchDeskEntityFrameworkDAL;
using SearchDeskEntityFrameworkDAL.Repositories;
using Telerik.Web.UI;
using System.Linq.Dynamic;

namespace SearchDesk.billing
{
public partial class Dashboard : SearchDesk.BasePage
{
      private string sortActions;
      private DateTime filterDateTime;
    protected void Page_Load(object sender, EventArgs e)
    {

}

      protected void GridActionsItemDataBound(object sender, GridItemEventArgs e)
      {
          if (e.Item is GridDataItem)
          {
              var item = (GridDataItem)e.Item;
              int actionTypeID = Convert.ToInt16(item["ActionTypeID"].Text);
              if (actionTypeID == (int)ActionTypes.CreateInvoice)
              {
                  item["buttons"].Controls[1].Visible = true;
              }
          }
      }

      protected void GridActionsNeedDataSource(object sender, GridNeedDataSourceEventArgs e)
      {
         var searchDeskBillingRepository = new SearchDeskBillingRepository();
          var filter = gridActions.MasterTableView.FilterExpression;

          if (string.IsNullOrEmpty(sortActions))
          {
              sortActions = "ActionDate";
          }
          if (string.IsNullOrEmpty(filter))
          {
              gridActions.DataSource =
                  searchDeskBillingRepository.GetActionsAssignedToUser(CurrentUser.ObjectID).OrderBy(sortActions);
          }
          else
          {
              filter = filter.Replace(".ToString()", "");
              if (filter.Contains("DateTime.Parse"))
              {
                  gridActions.DataSource =
                      searchDeskBillingRepository.GetActionsAssignedToUser(CurrentUser.ObjectID).Where(
                          s => s.ActionDate == filterDateTime);
              }
              else
              {
                  gridActions.DataSource =
                      searchDeskBillingRepository.GetActionsAssignedToUser(CurrentUser.ObjectID).Where(filter);

              }

          }

}

      public override string PageName
      {
          get { return "DashBoard.aspx"; }
      }

      protected void gridActionSort(object sender, GridSortCommandEventArgs e)
      {
          if (e.NewSortOrder == GridSortOrder.None)
          {
              sortActions = e.SortExpression;
          }
          else
          {
              sortActions = e.SortExpression + " " + e.NewSortOrder;
          }
      }

      protected void getitem(object sender, GridCommandEventArgs e)
      {
         if (e.Item is GridFilteringItem){
              GridFilteringItem filteringItem = e.Item as GridFilteringItem;
              filterDateTime = Convert.ToDateTime(((RadDatePicker)filteringItem["ActionDate"].Controls[0]).DbSelectedDate);

          }
      }
}
}

4 Answers, 1 is accepted

answered on 11 Mar 2015, 09:32 AM

string FinalQuery = "";

if (filter.Contains("DateTime.Parse("))

{

var filters = filter.Split(new string[] { "DateTime.Parse(" }, StringSplitOptions.RemoveEmptyEntries);

for (int i = 0; i < filters.Count(); i++)

{

if (i != 0)

{

var oldDate = filters[i].Substring(1, 22);

var newDate = DateTime.Parse(oldDate);

filters[i] = filters[i].Replace('\"' + oldDate + "\")", "DateTime(" + newDate.Year + ',' + newDate.Month + ',' + newDate.Day + ',' + newDate.Hour + ',' + newDate.Minute + ',' + newDate.Second + ")");

}

FinalQuery = FinalQuery + filters[i];

}

else

FinalQuery = filter;

This is my way to fix that issue before passing this query for DB request.