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. ;)
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...
Use the .Date
property of the DateTime object.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With