Extracting the Date from a DateTime in Entity Framework 4 and LINQ
- by Ken Cox [MVP]
In my current ASP.NET 4 project, I’m displaying dates in a GridDateTimeColumn of Telerik’s ASP.NET Radgrid control. I don’t care about the time stuff, so my DataFormatString shows only the date bits:
<telerik:GridDateTimeColumn FilterControlWidth="100px" DataField="DateCreated" HeaderText="Created" SortExpression="DateCreated" ReadOnly="True" UniqueName="DateCreated" PickerType="DatePicker" DataFormatString="{0:dd MMM yy}">
My problem was that I couldn’t get the built-in column filtering (it uses Telerik’s DatePicker control) to behave. The DatePicker assumes that the time is 00:00:00 but the data would have times like 09:22:21. So, when you select a date and apply the EqualTo filter, you get no results. You would get results if all the time portions were 00:00:00.
In essence, I wanted my Entity Framework query to give the DatePicker what it wanted… a Date without the Time portion. Fortunately, EF4 provides the TruncateTime function. After you include
Imports System.Data.Objects.EntityFunctions
You’ll find that your EF queries will accept the TruncateTime function. Here’s my routine:
Protected Sub RadGrid1_NeedDataSource _ (ByVal source As Object, _ ByVal e As Telerik.Web.UI.GridNeedDataSourceEventArgs) _ Handles RadGrid1.NeedDataSource Dim ent As New OfficeBookDBEntities1 Dim TopBOMs = From t In ent.TopBom, i In ent.Items _ Where t.BusActivityID = busActivityID _ And i.BusActivityID And t.ItemID = i.RecordID _ Order By t.DateUpdated Descending _ Select New With {.TopBomID = t.TopBomID, .ItemID = t.ItemID, _ .PartNumber = i.PartNumber, _ .Description = i.Description, .Notes = t.Notes, _ .DateCreated = TruncateTime(t.DateCreated), _ .DateUpdated = TruncateTime(t.DateUpdated)} RadGrid1.DataSource = TopBOMs End Sub
Now when I select March 14, 2011 on the DatePicker, the filter doesn’t stumble on time values that don’t make sense.
Full Disclosure: Telerik gives me (and other developer MVPs) free copies of their suite.
