How to rewrite SQL query to LINQ (using GroupBy, Distinct)

Question

I have a question on mind for a few long days. Finally, I made a SQL query, which I want to show in MVC4 View. I build following SQL query:

select distinct date, max(priority) from Timetables where date between '2013-12-01' and '2013-12-31' group by date

which returns me a collection of dates and max priorities of that dates. That is OK for me. I am totaly okay, when this query returns me a whole Timetables record. But I need to pass result of this query to View engine of MVC4. I had some tries, but I am nowhere near finding a result for that. If you have some other possibilities, how to do that, I am eager to hear :)

Also, I am using an Entity Framework.

Thanks!

e: The database looks like that:

id  | doctor_id  | nurse_id  | date    | start_time  | end_time |time_for_pacient| priority |comments
-----------------------------------------------------------------------------------------
1   |     5      |   4       | 13-12-01| 07:00       | 11:30    |   00:20        |    1     |Normal
2   |     5      |   4       | 13-12-02| 07:00       | 11:30    |   00:20        |    1     |Normal
3   |     5      |   4       | 13-12-01| 08:00       | 10:30    |   00:20        |    2     |Shorten
4   |     5      |   4       | 13-12-02| 06:00       | 10:30    |   00:10        |    3     |Extra

the result I want to achieve in View:

id  | doctor_id  | nurse_id  | date    | start_time  | end_time |time_for_pacient| priority |comments
-----------------------------------------------------------------------------------------------------
3   |     5      |   4       | 13-12-01| 08:00       | 10:30    |   00:20        |    2     |Shorten
4   |     5      |   4       | 13-12-02| 06:00       | 10:30    |   00:10        |    3     |Extra

I just want to show a single record for each day with highest priority.

well the distinct is redundant for starters, group by takes care of that. — Tony Hopkinson, Dec 12 '13 at 22:59

score 0 · Answer 1 · edited May 23 '17 at 12:19

0

With EntityFramework and other ORM tools, don't think of selecting specific fields or columns. Rather, you're selecting specific obejcts.

To translate your exact query to LINQ would actually end up returning a collection of anonymous objects which is rarely what you want.

In C#, it might look like this:

var date1 = new Date(2013, 12, 1);
var date2 = new Date(2013, 12, 31);
var timetables = DbContext.Timetables
                     .Where(x => x.Date.CompareTo(date1) >= 0 && x.Date.CompareTo(date2) <= 0)
                     .Distinct();

edited May 23 '17 at 12:19

Community

1
1

answered Dec 12 '13 at 23:14

Rowan Freeman

15,724
11
69
100

Pretty nice thought. If I can introduce you more deeply into the problem, I show you an example: I want to show records of days in range from 2013-12-01 to 2013-12-31. But in every day, they could be more records, but with different priority. I want to select only one record for each day (that one with highest priority). See edit on my question, there is a look on database, how it does look like and how I want to see that. – Michal Stepan Dec 13 '13 at 09:51

How to rewrite SQL query to LINQ (using GroupBy, Distinct)

1 Answers1