Efficient joining the most recent record from another table in Entity Framework Core

I am comming to ASP .NET Core from PHP w/ MySQL.

The problem:

For the illustration, suppose the following two tables: T: {ID, Description, FK} and States: {ID, ID_T, Time, State}. There is 1:n relationship between them (ID_T references T.ID).

I need all the records from T with some specific value of FK (lets say 1) along with the related newest record 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 am struggling to write an efficient equivalent query in LINQ (or the fluent API). The best working solution I've got so far is:

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 resulting SQL query in the output window when executed it is just like:

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]

Not only it selects more columns than I need (in the real scheme there are more of them). There is no grouping in the query so I suppose it selects everything from the DB (and States is going to be huge) and the grouping/filtering is happening outside the DB.

The questions:

What would you do?

• Is there an efficient query in LINQ / Fluent API?
• If not, what workarounds can be used?
  • Raw SQL ruins the concept of abstracting from a specific DB technology and its use is very clunky in current Entity Framework Core (but maybe its the best solution).
  • To me, this looks like a good example for using a database view - again, not really supported by Entity Framework Core (but maybe its the best solution).