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
– Maris, Commented Apr 17, 2018 at 12:40

Ctznkane525 · Accepted Answer · 2018-04-17 12:03:08Z

1

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.

answered Apr 17, 2018 at 12:03

Ctznkane525

7,5453 gold badges19 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adrian Reid · Accepted Answer · 2018-04-17 12:10:19Z

1

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

answered Apr 17, 2018 at 12:10

Adrian Reid

857 bronze badges

Collectives™ on Stack Overflow

How to write Entity Framework for the following SQL Server Query

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related