How to write Entity Framework for the following SQL Server Query

Question

I need to write the following SQL Server query in Entity Framework but have been unsuccessful so far. Can someone please help with converting to Entity Framework syntax?

SQL table

username, date,      value
--------------------------
brad,     1/2/2010,  1.1
fred,     1/3/2010,  1.0
bob,      8/4/2009,  1.5
brad,     2/2/2010,  1.2
fred,     12/2/2009, 1.3

SQL Server Query

select t.username, t.date, t.value
from MyTable t
inner join (
    select username, max(date) as MaxDate
    from MyTable
    group by username
) tm on t.username = tm.username and t.date = tm.MaxDate

The original question is here: https://stackoverflow.com/a/2411703/1355975

@PaulKaram if he would know an answer on your question - don't you think that he would ever posted a question here? :) — Maris, Apr 17 '18 at 12:40

score 1 · Answer 1 · answered Apr 17 '18 at 12:03

Assuming your database base class is DatabaseContext and you have a table named MyTable, you can select records from it this way:

using (var context = new DatabaseContext()) 
{ 
    var myTableRecords = context.MyTable.SqlQuery("your query goes in here").ToList(); 
}

You can run any RAW SQL this way.

score 1 · Answer 2 · answered Apr 17 '18 at 12:10

I think this is what I'm looking for - I'll need to tailor it slightly but should provide me with what I need:

DateTime utcDate = DateTime.UtcNow;

var result =
    entries // e.g. context.tableName
    .GroupBy(e => e.username)
    .Select(gr =>
        gr
        .Where(x => x.date < utcDate)
        .OrderByDescending(x => x.date)
        .FirstOrDefault()) // This will give us null for devices that
                           // don't have a status entry before the date
    .AsEnumerable()
    .ToList();

https://stackoverflow.com/a/37757982/1355975

How to write Entity Framework for the following SQL Server Query

2 Answers2