Effectively merging the last record from another table in the Entity Framework Core

Question

I am coming to ASP.NET Core from PHP with MySQL.

---

Problem:

To illustrate, suppose the following two tables: T: {ID, Description, FK}and States: {ID, ID_T, Time, State}. Between them there is a 1: n ratio ( ID_Tlinks T.ID).

I need all entries from Twith some specific value FK(say, 1) along with the corresponding latest entry in States(if any).

---

In terms of SQL, it can be written as:

SELECT T.ID, T.Description, COALESCE(s.State, 0) AS 'State' FROM T
LEFT JOIN (
    SELECT ID_T, MAX(Time) AS 'Time'
    FROM States
    GROUP BY ID_T
) AS sub ON T.ID = sub.ID_T
LEFT JOIN States AS s ON T.ID = s.ID_T AND sub.Time = s.Time
WHERE FK = 1

I'm struggling to write an efficient equivalent query in LINQ (or a free API). The best working solution I've received so far:

from t in _context.T
where t.FK == 1
join s in _context.States on t.ID equals o.ID_T into _s
from s in _s.DefaultIfEmpty()
let x = new
{
    id = t.ID,
    time = s == null ? null : (DateTime?)s.Time,
    state = s == null ? false : s.State
}
group x by x.id into x
select x.OrderByDescending(g => g.time).First();

When I check the received SQL query in the output window at runtime, it looks like this:

SELECT [t].[ID], [t].[Description], [t].[FK], [s].[ID], [s].[ID_T], [s].[Time], [s].[State]
FROM [T] AS [t]
LEFT JOIN [States] AS [s] ON [T].[ID] = [s].[ID_T]
WHERE [t].[FK] = 1
ORDER BY [t].[ID]

, ( ). , ( States ), / .

---

:

?

• API LINQ/Fluent?
• , ?
  • Raw SQL , Entity Framework Core (, , ).
  • - , Entity Framework Core (, , ).

Answer 1

OK. ( ), Smit , , EF Core . SQL , ( ) . ( SQL- ). LINQ- Linqpad SQL-.

EF6 . . .First() .FirstOrDefault() LINQ SQL-, . , .

NetMage ( ), , SQL- SQL- ( , COALESCE).

var latestState = from s in _context.States
                  group s by s.ID_T into sg
                  select new { ID = sg.Key, Time = sg.Time.Max() };

var ans = from t in _context.T
          where t.FK == 1
          join sub in latestState on t.ID equals sub.ID into subj
          from sub in subj.DefaultIfEmpty()
          join s in _context.States
              on new { ID_T = t.ID, sub.Time } equals new { s.ID_T, s.Time }
              into sj
          from s in sj.DefaultIfEmpty()
          select new { t.ID, t.Description, State = (s == null ? false : s.State) };

LINQ , SQL-, , .

EF6 SQL- AFAIK .

, .NET , EF6 .NET Core.

Answer 2

, LINQ?

var latestState = from s in _context.States
                  group s by s.ID_T into sg
                  select new { ID_T = sg.Key, Time = sg.Time.Max() };

var ans = from t in _context.T
          where t.FK == 1
          join sub in latestState on t.ID equals sub.ID_T into subj
          from sub in subj.DefaultIfEmpty()
          join s in _context.States on new { t.ID, sub.Time } equals new { s.ID, s.Time } into sj
          from s in sj.DefaultIfEmpty()
          select new { t.ID, t.Description, State = (s == null ? 0 : s.State) };

-, ?? COALESCE , select :

          select new { t.ID, t.Description, State = s.State ?? 0 };