Returning dates without times in a LINQ query

Question

I am writing a query where I want to count the number of times our call center gets contacted by date. Seems easy enough, but since the contact date field is a datetime field I get the time, so when I group by contact date(time) each contact date instance has a count of '1'. So, I want to group by just the date without the time. Below is the expression I use to query the database:

MyDataContext db = new MyDataContext();
var items = (from h in db.ContactHistories
             group h by h.contact_dt into g
             orderby g.Key
             select new
             {
                 contact_dt = g.Key,
                 item_count = g.Count()
             }).Take(50);

I've tried to use

     h.contact_dt.ToShortDateString()

But that doesn't work since SQL does know about ToShortDateString(). Any thoughts are greatly appreciated.

Thanks!

EDIT:

The .Take(50) is in place to limit the number of records I get until I add the date pickers and a where clause so I don't get all 400K records. ;)

Answer 1

Use the Date property:

group h by h.contact_dt.Date into g

LINQ to SQL knows about that - see this handy page of "understood" methods and properties.

EDIT: Okay, so grouping directly doesn't work... how about we try to give it a bit of help:

var items = (from h in db.ContactHistories
             select h.Date into date
             group date by date into g
             orderby g.Key
             select new
             {
                 contact_dt = g.Key,
                 item_count = g.Count()
             }).Take(50);

I don't know if it'll work, but it's worth a try...

Answer 2

Use the .Date property of the DateTime object.