How can i group by a datetime field only using the date without the time?

Question

User-540818677 posted

I have a Date property with a Datetime Datatype currently i need to count the number of records in each day regardless of the time i wrote the following:-

public IQueryable<VisitSearch> visitsearch(DateTime? datefrom, DateTime? dateto, int countryid)

            {              var vs = (from v in entities.Visits
                          where ((v.Date >= datefrom || datefrom == null) && (v.Date <= dateto || dateto == null) && (v.Patient.NationalityID == countryid || countryid == 0))
                          group v by v.Date into groupvisit
                          select new VisitSearch
                          {
                              VisitDate = groupvisit.Key,
                              count = groupvisit.Count()       });
                return vs;

But currently the result will contain different groups that have the same Date, for example if i have Five objects with 1/April/2012 date then i will get five different records with a count of 1 ,,, instead of getting one record with a count of 5.

I tried to add .Date to the V.Date in the above method, but i got the following error :-

The specified type member 'Date' is not supported in LINQ to Entities

var vs = (from v in entities.Visits
                          where ((v.Date.Date >= datefrom || datefrom == null) && (v.Date.Date <= dateto || dateto == null) && (v.Patient.NationalityID == countryid || countryid == 0))
                          group v by v.Date into groupvisit
                          select new VisitSearch
                          {
                              VisitDate = groupvisit.Key,
                              count = groupvisit.Count()

                          });
                return vs;

Answer 1

User301971655 posted

Try your code, in the following way. I tried it in a sample and it works.

(Below code taken from my sample application.)

var tt =
                from t in db.Test1s
                let dt = t.MyDate
                group t by new { y=dt.Year, m=dt.Month,d=dt.Day} into dtd
                select new
                {
                    dtgrp = dtd.Key,
                    dtcnt = dtd.Count()
                };

Answer 2

User-1825041450 posted

In case of linq to entities, and if you need to truncate time, try to use

System.Data.Objects.EntityFunctions.TruncateTime(v.Date)

Answer 3

User-540818677 posted

In case of linq to entities, and if you need to truncate time, try to use

System.Data.Objects.EntityFunctions.TruncateTime(v.Date)

thanks for the reply,, i tried the below but i got the following error "Error    12    Argument 1: cannot convert from 'System.DateTime?' to 'System.DateTime' " on VisitDate = groupvisit.Key,:-

 public IEnumerable<VisitSearch> visitsearch(DateTime? datefrom, DateTime? dateto, int countryid)
            {
                
                var vs = (from v in entities.Visits
                          where ((System.Data.Objects.EntityFunctions.TruncateTime(v.Date) >= datefrom || datefrom == null) && (System.Data.Objects.EntityFunctions.TruncateTime(v.Date) <= dateto || dateto == null) && (v.Patient.NationalityID == countryid || countryid == 0))
                          group v by System.Data.Objects.EntityFunctions.TruncateTime(v.Date) into groupvisit
                          select new VisitSearch
                          {
                              VisitDate = groupvisit.Key,
                              count = groupvisit.Count()

                          });
                return vs;
                     
            
            }

so what might be the problme ,, baring in mind that the V.Date is of type Datetime and not Datetime?...

Updated:-

i checked the following documentation http://msdn.microsoft.com/en-us/library/dd395596.aspx ,,and it mentioned that the return type for the EntityFunctions.TruncateTime is

Return Value

Type: System.Nullable<DateTime>
The input date with the time portion cleared.

BR

Answer 4

User-1825041450 posted

Maybe it should be grouped by value of that nullable datetime

group v by System.Data.Objects.EntityFunctions.TruncateTime(v.Date).Value

something like this

or

VisitDate = groupvisit.Key.Value

Answer 5

User348806598 posted

Hi,

Check this-

http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/cb58ae5f-5db9-4054-92b6-a1bf63764574