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

  • 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;



    Thursday, May 10, 2012 5:35 PM

Answers

All replies

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

    Friday, May 11, 2012 3:03 AM
  • 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)



    Friday, May 11, 2012 3:44 AM
  • 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

    Friday, May 11, 2012 8:58 PM
  • 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




    Monday, May 14, 2012 2:39 AM
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 14, 2012 6:56 AM