Split a entity collection into n parts

Question

I have a database table.

First, I want to group by date time.
Then I want to only select the groups with n items.

My class looks like this:

public class VisitDate
{
  public int Id {get;set;}
  public int VisitMeDate {get;set;}
  .....
  .....
}

My SQL table is like below:

Id   Date
--- -----------------------
136 2012-05-09 10:00:00.000
167 2012-05-09 12:00:00.000
137 2012-05-10 10:00:00.000
168 2012-05-10 12:00:00.000
194 2012-05-10 14:00:00.000
138 2012-05-11 10:00:00.000
169 2012-05-11 12:00:00.000
195 2012-05-11 14:00:00.000
139 2012-05-12 10:00:00.000
170 2012-05-12 12:00:00.000
196 2012-05-12 14:00:00.000
140 2012-05-13 10:00:00.000
171 2012-05-13 12:00:00.000
197 2012-05-13 14:00:00.000
141 2012-05-14 10:00:00.000
142 2012-05-15 10:00:00.000
172 2012-05-15 12:00:00.000
143 2012-05-16 10:00:00.000
173 2012-05-16 12:00:00.000
144 2012-05-17 10:00:00.000
174 2012-05-17 12:00:00.000
198 2012-05-17 14:00:00.000

I want to transform like below:

List<List<MyEntity>> = ?;

137 2012-05-10 10:00:00.000
168 2012-05-10 12:00:00.000
194 2012-05-10 14:00:00.000

138 2012-05-11 10:00:00.000
169 2012-05-11 12:00:00.000
195 2012-05-11 14:00:00.000

139 2012-05-12 10:00:00.000
170 2012-05-12 12:00:00.000
196 2012-05-12 14:00:00.000

140 2012-05-13 10:00:00.000
171 2012-05-13 12:00:00.000
197 2012-05-13 14:00:00.000

144 2012-05-17 10:00:00.000
174 2012-05-17 12:00:00.000
198 2012-05-17 14:00:00.000

I tried this:

List<List<VisitDate>> dates = dbContext.VisitDates.GroupBy(f => new { f.VisitMeDate }).ToList();

But it doesn't compile and I don't know how to say that I only want groups with three elements.

I've edited your question to try to make it more clear. If I've made a mistake in understanding your question then please edit it again to fix the error. — Mark Byers, May 09 '12 at 20:58

Mark Byers · Accepted Answer · 2012-05-09T21:34:03.407

4

Try something like this:

var result = dbContext.VisitDates
                      .GroupBy(x => x.VisitMeDate.Date)
                      .Where(g => g.Count() == 3)
                      .Select(g => g.ToList())
                      .ToList();

edited May 09 '12 at 21:34

answered May 09 '12 at 20:51

Mark Byers

811,555
193
1,581
1,452

it didnt work because VisitMeDate column not formatted like this from 2012-05-09 10:00:00.000 to 2012-05-09 0:00:00.000 i googled and i find a solution but it works on pure t-sql syntax SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date)) how i can convert this syntax to linq syntax? – tobias May 09 '12 at 21:31
@tobias: I added `.Date` to my answer. – Mark Byers May 09 '12 at 21:34
ohh,its so simple,why i am thinking so complicated :) – tobias May 09 '12 at 21:36
i tried ur code and i got an error, The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported. – tobias May 10 '12 at 08:30
@tobias: It seems that EF doesn't support `DateTime.Date`. This [thread](http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/84d4e18b-7545-419b-9826-53ff1a0e2a62/) suggests using `EntityFunctions.TruncateTime(x.VisitMeDate)` instead. Haven't tried it though. If that doesn't work, you could try grouping by year, month, day. – Mark Byers May 10 '12 at 08:56
i tried and then change the error to LINQ to Entities does not recognize the method 'System.Collections.Generic.List`1[Domain.VisitDate] ToList[VisitDate](System.Collections.Generic.IEnumerable`1[Domain.VisitDate])' method, and this method cannot be translated into a store expression. – tobias May 10 '12 at 09:39

Split a entity collection into n parts

1 Answers1

Linked