How can I do this with LINQ?

Question

All I'm after doing is this:

SELECT CallTypeID, Count(CallTypeID) as NumberOfCalls
FROM [Helpdesk_HR].[dbo].[CallHeader]
WHERE CallHeader.DateOpened <= GETDATE()-7
GROUP BY CallTypeID

in LINQ. But I can't work out a way of doing it and getting it to work. I would use Linqer, but my company won't pay for it at present.

Any help is greatly appreciated as I'm sure the answer is obvious. It's just been one of those days today.

Answer 1

Try this:

var query = from call in context.CallHeader
            where call.DateOpened <= DateTime.Today.AddDays(-7)
            group call by call.CallTypeId into grouped
            select new { CallTypeId = grouped.Key, Count = grouped.Count() };

The DateTime.Today bit may be slightly off - it depends on things like how your dates are stored in the database. You may want to work out the date before the query and then just use "where call.DateOpened <= limit" or something like that.

Answer 2

Something like this:

var callGroups = from ch in CallHeader
        where ch.YourDate <= DateTime.Now.AddDays(-7)
        group ch by ch.CallTypeID into grp
        select new { CallTypeID = grp.Key, Cnt = grp.Count() };

I haven't tested so syntax might be off.