Select many rows from a data store using Linq to Entities

Question

We need to accept a collection of key values from user input. Then a query must be performed on a L2E data store which shall select all rows whose keys are included in the collection. The best I've got so far is:

var lines = dataStore.Entities.ToList(); /* To List to force a query */
var selectedLines = lines.Where(line=> inputValues.Contains(line.key)).Distinct();

However, this seems seems wasteful since we're pulling the entire data store in order to select (probably) just a small number of rows. Would it be less wasteful to execute a separate query matching each key value (the column is indexed) or is there a better way with Linq syntax that I've missed?

Why do you force the query to be executed? Is there a requirement behind this? — thekip, Jul 15 '11 at 07:15
@thekip: Yes, there is. Unless the query is forced, L2E tries to render the .Contains method in to an expression and fails. I'm still waiting for a response back as to the L2E version so I can respond to the answers. — Jesse Hallam, Jul 15 '11 at 20:08

score 1 · Accepted Answer · edited May 23 '17 at 10:24

1

EF4 has support for contains so you can just use it directly.

var selectedLines = dataStore.Entities
                             .Where(line=> inputValues.Contains(line.key))
                             .Distinct();

For possible workarounds in earlier versions see this SO question.

edited May 23 '17 at 10:24

Community

1
1

answered Jul 15 '11 at 07:05

Geoff Appleford

18,538
4
62
85

score 0 · Answer 2 · answered Jul 15 '11 at 07:05

You should have the contains within your first query.
This will then only return the values that the user specifiec.
I would also recomend you look at LinqPad very nice tool and it shows you what queries are being create by Linq to Entities.

var lines = (from p in dataStore.Entities
            where inputValues.Contains(p.key)
            select p).ToList();

Select many rows from a data store using Linq to Entities

2 Answers2