Title: GroupBy Date in LINQ C#
Tags: c#;linq;group-by
Question: I am trying to GROUPBY Date in a LINQ Query and display the output as shown below
startdates: [
startdate: “4/1/2014”,
users: [
{userId, …},
{userId, …}
startdate: “4/2/2014”, users: [
{userId, …}
The code is shown below
db.Users
.Where(x =&gt; (x.startDate &gt;= startDate) &amp;&amp; (x.startDate <= endDate))
.GroupBy(x =&gt; new { x.startDate.Day, x.startDate.Month, x.startDate.Year })
.ToList()
.Select(y =&gt; new
startdates = y.Select(k =&gt;
new {startdate = (k.startDate.Month.ToString() + "/" + k.startDate.Day.ToString() + "/" + k.startDate.Year.ToString()),
users = y.Select(z =&gt;
new {userId = z.userId,
userName = z.userName})})});
Even though the Users are Grouped by StartDate, the output contains the startDate multiple times the same number of times as the number of Users.The output is shown below. I tried putting .Distinct() but it still repeats the startdate. Can someone please help?
[{"startdates":
[{"startdate":"04/01/2014",
"users":[
{"userId":1},"userName":"John"}
{"userId":2},"userName":"Mike"}],
[{"startdate":"04/01/2014",
"users":[
{"userId":1},"userName":"John"}
{"userId":2},"userName":"Mike"}],
[{"startdate":"04/02/2014",
"users":[
{"userId":3},"userName":"AL"}
{"userId":4},"userName":"Test"}],
[{"startdate":"04/02/2014",
"users":[
{"userId":3},"userName":"AL"}
{"userId":4},"userName":"Test"}]
Comment: Why are you grouping by an anonymous type rather than just by `startDate`?

Here is the accepted answer: The problem is your selection part, here:
.Select(y =&gt; new
startdates = y.Select(k =&gt;
new {startdate = (k.startDate.Month.ToString() + "/" + k.startDate.Day.ToString() + "/" + k.startDate.Year.ToString()),
users = y.Select(z =&gt;
new {userId = z.userId,
userName = z.userName})})});
You've got far too much nesting there. You're creating a startdate part for each element within the group.
It's unclear why you're using grouping by three separate parts at all, but I suspect this will do what you want:
db.Users
.Where(x =&gt; (x.startDate &gt;= startDate) &amp;&amp; (x.startDate <= endDate))
.GroupBy(x =&gt; x.startDate.Date) // Or just x.startDate
.AsEnumerable() // Do the rest of the query locally
.Select(group =&gt; new
startdate = group.Key.ToString("MM/dd/yyyy"),
users = group.Select(z =&gt; new { z.userId, z.userName })
If you need to wrap that in a startdates field, you can then use:
var result = new { startdates = query.ToArray() };
Comment for this answer: @user2515186: Hence my use of `x.startDate.Date`, which removes the time part.
Comment for this answer: I am adding Grouping by 3 different parts as the StartDate field contains the time which fails the Groupby day.
Comment for this answer: I am able to use it now. Thanks a lot for your help


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 1, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2014:00:04/08/14', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<c#><linq><group-by>', 'Title': 'GroupBy Date in LINQ C#', 'ViewCount': 149, 'id': 22928416, 'user_AboutMe': None, 'user_AccountId': 2936397.0, 'user_DisplayName': 'user2515186', 'user_Reputation': 2017.0, 'user_Views': 45.0, 'user_id': 2515186.0}}