How to use the Contains method in a search using the Entityframework

Question

I’m trying to make the following consultation sql, using the Entityframework:

select * from pedidos
where pedidoID not in (select pedidoID from agendamentos);

I conducted some research and discovered that the method of Entityframework replacing the not in of sql is the Contains(). So I set up the following code:

var pedidos = Db.Pedidos.Where(x =>
                    Db.Pedidos.Where(y => y.PedidoId == x.Agendamentos.Select(z=>z.PedidoId).
                    FirstOrDefault()).Select(y => y.PedidoId).ToList().
                    Contains(
                        Db.Pedidos.Where(z=>z.PedidoId == x.PedidoId).
                        Select(y => y.PedidoId).FirstOrDefault())
                    ).
                    ToPagedList(1, RecordsPerPage);

With the Contains method I get the following error:

I believe that being a requested timeout, the research sql is taking too long to run, so I tried to put conditions to decrease the size of the list, and even so I keep giving the same error.

So the function works perfectly:

If you can help me with this problem, I’d be grateful!

Answer 1

John, a more practical way to do what you’re trying to do is by using the Any

an example would be:

var pedidos1 = db.Pedidos
                    .Where(a => !a.Agendamentos.Any());

The SQL generated was the following

SELECT 
    [Extent1].[PedidoId] AS [PedidoId]
    FROM [dbo].[Pedidoes] AS [Extent1]
    WHERE  NOT EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[Agendamentoes] AS [Extent2]
        WHERE [Extent1].[PedidoId] = [Extent2].[PedidoId]
    )

Is not a NOT IN but the result will be the same

However, your model has to have the navigational properties between the entities

public class Pedido
{
    public int PedidoId { get; set; }

    public ICollection<Agendamento> Agendamentos { get; set; }
}

public class Agendamento
{
    public int AgendamentoId { get; set; }
    public int PedidoId { get; set; }
    public virtual Pedido Pedido { get; set; }
}

Using the Contains:

var pedidos2 = db.Pedidos.Where(pedido => !db.Agendamentos.Select(agendamento => agendamento.PedidoId).Contains(pedido.PedidoId));

The SQL was exactly the same.

The way to get a NOT IN would first save all the Request Ids that are in scheduling and then perform the query using the Contains

var agendamentos = db.Agendamentos.Select(a => a.PedidoId).ToList();
var pedidos3 = db.Pedidos.Where(pedido => !agendamentos.Contains(pedido.PedidoId));

The SQL was generated:

SELECT 
    [Extent1].[PedidoId] AS [PedidoId]
    FROM [dbo].[Pedidoes] AS [Extent1]
    WHERE  NOT ([Extent1].[PedidoId] IN (1, 2))