How to select a select using the ado.net Entity Framework

Question

I’m developing a system in ASP.NET MVC which uses as standard the Entity Framework for data access, but I am in need of a select on another select, and I don’t know how to do this with Entity. The code SQL which I would like to use is the following:

select * from pedidos as p
                 inner join agendamentos as a
                 on a.pedidoID = p.pedidoID
                 where a.data = (select max(data) from (select a.data from pedidos as p
                 inner join agendamentos as a
                 on a.pedidoID = p.pedidoID
                 where a.realizado = 0) as dataagendamentos);

If anyone can help me, I’d be grateful!

Answer 1

You should not use the Entity Framework as SQL because it is not SQL. The sentence needs to be rethought. It should start with scheduling including your orders, not orders including scheduling.

That is to say:

var pedidos = db.Agendamentos   
                .Include(a => a.Pedidos)
                .Where(a => a.Realizado == 0)
                .OrderByDescending(a => a.Data)
                .FirstOrDefault()
                .SelectMany(a => a.Pedidos)
                .ToList();