Simplify LINQ query with Contains

Question

I have two questions regarding the LINQ consultations below:

{    
    //lista de itens do orçamento
    var ids = itensOrcamento.Select(p => p.Id); 
    //Produtos - todos produtos cadastrados
    var produtos = Produtos.Where(p => ids.Contains(p.Id)); 
}

1. In relation to Produtos.Where(p => ids.Contains(p.Id)) I couldn’t exactly interpret the function p => ids.Contains(p.Id), i.e., how to describe this function (what it does)?
2. The second question is whether these queries can be optimized/improved (bringing together in a single query)

Answer 1

First let’s understand the SQL that each of them generates:

The contains is a WHERE id in (). When executing a query like this:

db.TABELA.Where(p => ids.Contains(p.ID)).ToList();

The generated SQL is like this:

    SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[CAMPO] AS [CAMPO]
    FROM [esquemateste].[TABELA] AS [Extent1]
    WHERE [Extent1].[ID] IN (cast(1 as bigint))

Other than SELECT that actually only filters the columns that will return:

db.TABELA.Select(p=> p.ID ).ToList();

Will generate the following SQL:

    SELECT 
    [Extent1].[ID] AS [ID]
    FROM [esquemateste].[TABELA] AS [Extent1]

SUMMING UP

Your first case it will take ALL records from the table, but it will only return the ID column. Your second case will return all table columns where the id exists in a collection of Ids.(Where(p => ids.Contains(p.ID)))

IMPROVING

If I understand correctly what you are doing is to get the details of the products registered on a budget. Surely you are going the right way to join the two, going only once in the database is always (99.9% of the time) better than going twice.

You can do it this way:

 var prods = (from io in db.itensOrcamento
              join p in db.protudo on p.ID equals io.ID
             where io.ID_DA_VENDA = XXX
            select p).ToList();

That way you make a join of the two tables by the same ID being picked up in its first query and used in the second.

UPDATING

Correcting a question in the reply according to the @Tobymosque comment. The implementation of the question really does not go twice, while making my reply I took into account the use of the .ToList() that I put in the first SQL made by me, after that I did not turn attention to the question that the implementation of the question does not have that .ToList()

If the join (I put in the answer) or in (that is in question) the performance is the same.

The performance gain would be only if you go twice in the database what would it be like to do this here: (which is different from what is being asked in the question)

var t = db.TABELA.Where(p => ids.Contains(p.ID)).ToList();
var produtos = Produtos.Where(p => p.id = t.id).ToList();

This generates two trips to the database because when running the .ToList() it will execute the query.

Answer 2

It’s hard to answer something without knowing the real context. Apparently the code is picking up all the ids budget items. I have my doubts if that’s what you wanted, but you’re doing this.

Then he takes each of the existing products somewhere and looks for the id of each product in the list of ids found in the budget item. I wonder if these ids have something to do with the id product, should have, but it’s weird. If it doesn’t, it won’t work. If it does, the organization seems strange.

Can’t be p.Contains() because p is just a data scalar, the code has to check into a data collection. p is the product of the time being analyzed, not all products, p is an element, p, is not the same as produtos.

The Contains() always analyzes the contents of a collection and tells you if one of the elements is what you’re looking for. It is a search that does not matter what the element is, but whether it exists or not, the return is boolean.

The body of Contains() would more or less amount to this:

foreach (var item in ids) if (item == p.Id) return true;
return false;

I don’t see how to optimize this. The two operations are distinct, in different objects. Unless in a larger context you can find something extra or something is wrong. But I doubt it will. I’m just wondering if you’re doing what you want, so that’s another problem.

The accepted answer is rather inefficient for misusing the ToList() so do not use this, interestingly the attempt to improve worsened the result.