How to group by with only Time format in Linq

Question

i want to know that how to using the group by with only time format about datetime type.

select CONVERT(VARCHAR(10), a.tempDay, 108) as returnDay, b.qty from table1     as a inner join table2 as b on (a.id = b.id)
where a.id = 100
group by CONVERT(VARCHAR(10), a.tempDay, 108), b.qty

Actually, tempDay is DateTime type.(yyyy-MM-dd HH:mm:ss) in this case, i just want to groupping with only "HH:mm:ss" format with Linq.

Don't convert to strings when you want to compare *times*. Use `cast(tempday as time)` to get the time value. You could also create a separate column for the time that you could add to an index. — Panagiotis Kanavos, Oct 11 '16 at 07:07

QuietNaN · Answer 1 · 2016-10-11T07:06:19.873

1

var datesByTime = from d in dates
                  group d by d.TimeOfDay.ToString(@"hh\:mm\:ss") into g
                  select new { Time = g.Key, Dates = g.ToList() };

dates is collection of DateTime, DateTime has property TimeOfDay which returns TimeSpan of that date, all you have to do Group By TimeOfDay. You need also .ToString(@"hh:mm:ss") to remove milliseconds.

edited Oct 11 '16 at 07:06

answered Oct 11 '16 at 07:01

QuietNaN

371
1
14

The original SQL statement should be rewritten to reflect *this* LINQ statement - group by `cast(tempday as time)` and avoid text conversions – Panagiotis Kanavos Oct 11 '16 at 07:06
var datesByTime = from d in dates group d by d.ToLongTimeString() into g select new { Time = g.Key, Dates = g.ToList() }; – Cogent Oct 11 '16 at 07:12
Have you noticed the `entity-framework` tag? While this would be a good LINQ to Objects solution, in LINQ to Entities it simply generates the famous `NotSupportedException` - *"The specified type member 'TimeOfDay' is not supported in LINQ to Entities."* – Ivan Stoev Oct 11 '16 at 08:20
Yes. i have using the entity-framework. and, it still not work. group new { a, b } by new { a.TempDay.Value.TimeOfDay.ToString("HH:mm"), b.qty } into grp when i tried like that i am still have problem. => Invalid anonymous type member declarator. Anonymous type members must be declared with a member assignment, simple name or member access. – Song Oct 11 '16 at 08:48

How to group by with only Time format in Linq

1 Answers1