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

Question

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

Navigation

#1 by (1 votes)

0

I am trying to query sql , using EntityFramework :

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

I did some research and found that the EntityFramework method that replaces the not in of sql is Contains() . Then I put 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 sql search is taking a long time to run, so I tried to put conditions on the listing size, giving the same error.

So the function works perfectly:

If you can help me with this problem, I'll be grateful!

sql c# entity-framework query

asked by anonymous 22.02.2017 / 22:14

1 answer

TypeError: b is undefined jquery.min.js: 3: 6371 Take animation out of the button and leave only the zip code field visible

score 1 · Accepted Answer

John, a more practical way of doing what you're trying to do is by using Any

An example would be:

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

The SQL generated was as follows

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]
    )

It is not a NOT IN but the result will be the same

However, your model must have the properties of navigation between 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 Contains :

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

The SQL generated was exactly the same.

The way to get a NOT IN would be to first save all of the request IDs that are in scheduling and then perform the query using Contains

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

The% generated% was:

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