How to execute a Query including a Where?

Question

Simplified, I have a entity, let's call it "Foo", in my database a few times.

I now want to select certain Foos based on their FooCode (a list of FooCodes is selected by the user).

Let's say I have five Foos, three of them having the FooCode "20". I want to return a list of Foos with the FooCode "20"; the expected result is a list of Foos with the size of three.

The following LinQ-expression works totally fine, although it selects all Foos before executing the Where (please correct me if I'm wrong). So with a big amounts of Foos it's probably not the fastest solution.

// user defined list of FooCodes - now containing: "20", "20", "20"
List<string> selectedFooCodes = ...
List<Foo> = _transactionalDataAccess
                .Query<Foo>(query => query)
                .Where(entity => selectedFooCodes.Contains(entity.FooCode)).ToList();

I suppose a LinQ-expression as the following will select faster, because the Where is directly after the Query. Anyways, it returns a list of Foos with the size of five. As far as I understand, it should select the exactly same as the above query.

// user defined list of FooCodes - now containing: "20", "20", "20"
List<string> selectedFooCodes = ...
List<Foo> = _transactionalDataAccess
                .Query<Foo>(query => query
                .Where(entity => selectedFooCodes.Contains(entity.FooCode)));

Why is that? Can someone tell me what I'm missing. The above LinQ return the exactly same as:

_transactionalDataAccess
                .Query<Foo>(query => query);

Why doesn't the Where make any difference? Or is there an even faster notation I don't know about?

I'm sorry if this was asked before. I searched through the LinQ questions on StackOverflow, but haven't found a similar question. I have to admit that I decided to ask my own question, because it needs less time than reading and understanding all the existing ones.

Can you post more details about your code? What's type of _transactionalDataAccess? — Ricardo Silva
@RicardoSilva It's of type ITransactionalDataAccess which narrows down to IQueryDataAccess. I'm sorry, it's a thing from our company-intern framework. It basically just represents the interface with the (Oracle) database. Hope that helps. Does it even matter? — Alex
It is not possible to answer this question without knowing what a _transactionalDataAccess object is. — Hogan

Ricardo Silva Ricardo Silva · Accepted Answer · 2015-04-30T14:38:50

I don't know exactly how your component to access database works. But it seems to work in a similar way to Linq to Sql, or EF. Your first code seems to do something like this:

var searchContent = (from a in dataModel.TableA
                    select a)
                    .ToList()
                    .Where(a => a.ID == 22);

Now, the second example, appears to do something like this:

var searchContent = (from a in dataModel.TableA
                    select a)
                    .Where(a => a.ID == 22)
                    .ToList();

Notice that the from will only generate one queryable (it's will not do the search), and the Where method only increment this queryable. But, at this point, the query was not executed yet. The query will be only effective executed in ToList method (so it will be search only for the entries that has ID equals 22, and return this to the list.

The first example select ALL itens, mount objects into the List (now it's in your computer memory), and from the list it takes only the objects that has ID 22

How to execute a Query including a Where?

1 Answers