0

I tried to find answer for my problem, but I couldn't. I have table with ID, WorkerID, Function(int), and DateTime. Each worker has multiple records for each day. I need to get only first record for each day, for all workers. This query gives the results I wanted:

SELECT WorkerID, MIN(DateTime) AS DateTime
FROM Records
GROUP BY WorkerID, LEFT(DateTime, 10)   
ORDER BY WorkerID

But I want to select all columns (ID, WorkerID, Function, and DateTime). How can I do that? Also, I need this query for ASP.NET MVC3 application, so if anyone could write this for me in LINQ, and explain how to transform that query into IEnumerable(Record) or IQuerible(Record), I would be very thankful. Thanks in advance guys, hope you can save me.

Edit: Your LINQ query worked, but I had a lot of issues regarding perfomance, because of using a lot of Views, not Tables. Now I switched to Table, and I want do query directly on Table. Structure is the same, just instead of WorkerID, I have ETAG, which is the same. But I have problem now. SQL query mentioned above, gives me about 50k results on table, which is correct, but now your LINQ query gives me only 9k, so something is wrong. Here is query:

from d in Records
group d by new { d.ETAG, Date = d.DateTime.Value.Year + d.DateTime.Value.Month +            d.DateTime.Value.Day } into g
orderby g.Key.ETAG
select g.OrderBy(e => e.DateTime).FirstOrDefault()

Please, can you help me with this one...

dodjavola
  • 177
  • 1
  • 12

1 Answers1

2
from r in Records
group r by new { WorkerId = r.WorkerId, Date = r.DateTime.Value.Date } into g
orderby g.Key.WorkerId
select g.OrderBy(e => e.DateTime).First()

Update: added orderby clause, and use g.OrderBy() to enforce selecting the first record for each day, rather than an arbitrary element in the group.

Todd Li
  • 3,209
  • 21
  • 19
  • I'm getting this "The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported." – dodjavola Aug 30 '12 at 16:52
  • This may help you http://stackoverflow.com/questions/7083871/date-is-not-supported-in-linq-to-entities-only-initializers-entity-members – Todd Li Aug 30 '12 at 16:55
  • It worked with Date= d.DateTime.Value.Year + d.DateTime.Value.Month + d.DateTime.Value.Day, but it's slow as hell, it takes about 20 sec to proccess 10k rows, and table has about 130k :/ – dodjavola Aug 30 '12 at 17:55
  • You probably need to add some indexes. Get the equivalent SQL query, go to Management Studio and see the query plan, and see what is taking so long. If you put all this information together I can try to help you in another question. – Todd Li Aug 30 '12 at 17:58
  • Thanks man, you saved my day. I'll figure out something tommorow, and if I need your help, I'll contact you. Thanks again. – dodjavola Aug 30 '12 at 18:34
  • I have some problems now, I listened to your advice, and add indexes like query plan suggested to me, and now sql query works, but your LINQ query doesn's :( – dodjavola Aug 31 '12 at 23:35