EF Core - Pass parameter with integer list

Question

I am trying to execute a query by passing as clause an 'in' with integers, this is the code so far:

public List<Pedido> GetByUsuario(List<GestorVendedor> gestorVendedor)
{
    int[] seller_id = new int[gestorVendedor.Count];

    for (int i = 0; i < gestorVendedor.Count; i++)
    {
        seller_id[i] = gestorVendedor[i].id;
    }

    return _dbContext.Pedido.FromSql("select * from dbo.vendas where seller_id in ({0})", String.Join(",", seller_id)).ToList();
}

With this code problem occurs in the conversion as shows the error message:

'Conversion failed when Converting the nvarchar value '1,2,3' to data type int.'

How to get around this problem?

Answer 1

Two questions I can consider in your code:

int[] seller_id = new int[gestorVendedor.Count];

Change to:

var seller_ids = gestorVendedor.Select(x => x.Id).ToArray();

To make the query I would not use the FromSql and yes would pass the consultation via linq that gets a lot easier:

return _dbContext.Pedido.Where(x => seller_ids.Contains(x.seller_id)).ToList();

The EF will already mount the query with the in parameters and passes to the database.

Answer 2

You don’t need to wear one query SQL if you are already using EF.

public List<Pedido> GetByUsuario(List<GestorVendedor> gestorVendedor)
{ 
    return _dbContext.Pedido.Where(p=> gestorVendedor.Select(x => x.GestorVendedorId).ToArray().Contains(p.SellerId)).ToList()
}

You transform the list into Array and use the Contains

BS: I don’t know if your property calls SellerId, maybe you have to adjust