Simplify LINQ Query with Contains

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 looks like this:

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

Unlike the SELECT that actually only filters the columns that will return:

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

It will generate the following SQL:

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

SUMMARY

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

IMPROVING

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

You can do this as follows:

 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();

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

UPDATE

Correcting an issue in response to @TobyMosque's comment. The implementation of the question really does not go twice, in making my answer I took into consideration the use of .ToList() that I put in the first SQL done by me, after that I did not return to the attention the question that the implementation of the question does not have this .ToList()

If you use the join (which I put in the answer) or the in (which is in the question) the performance is the same.

The performance gain would only be if you go twice in the database what would be doing it here: (which is different from what is being done 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 database hits because when running .ToList() it will execute the query.

It is difficult to answer something without knowing the real context. It looks like the code is catching all% of% of budget items. I have my doubts if that's what you wanted, but you're doing this.

Then he picks up each of the existing products somewhere and looks for ids of each product in the list of id found in the budget item. I wonder if these ids have any relation to ids of product, should have, but it's weird. If it does not, it will not work. If so, the organization seems strange.

Can not be id because p.Contains() is just a scalar data, the code has to check on a data collection. p is the product of the time to be analyzed, it is not all products, p is an element, p , is not the same as p .

produtos always looks at the content of a collection and tells you if one of the elements is what you are 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 amount to something like this:

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

I do not see how to optimize this. The two operations are distinct, in distinct objects. Unless in a larger context you have to find out something extra or something wrong. But I doubt it will. I'm just wondering if you're doing what you want, that's another problem.