Linq-to-SQL EntitySet Is Not IQueryable -- Any Workarounds?

Question

When you query an EntitySet property on a model object in Linq-to-SQL, it returns all rows from the entityset and does any further querying client-side.

This is confirmed in a few places online and I've observed the behavior myself. The EntitySet does not implement IQueryable.

What I've had to do is convert code like:

var myChild = ... ;
// Where clause performed client-side.
var query = myChild.Parents().Where(...) ;

to:

var myChild = ... ;
// Where clause performed in DB and only minimal set of rows returned.
var query = MyDataContext.Parents().Where(p => p.Child() == myChild) ;

Does anyone know a better solution?

A secondary question: is this fixed in the Entity Framework?

I'm not sure why I'm not getting answers, especially from Microsoft folks. This might not seem important, but really it gets in the way of a preferred scoped data access coding style. If you want to limit the scope of data to objects owned by the current user, for example, you might use: theUser.Products().Where(...) rather than DataContext.Products().Where(p => p.User() == theUser).Where(...). Makes the coding repetitive and more likely to introduce a mistake. Contrast with Rails ActiveRecord: http://guides.rubyonrails.org/association_basics.html#why-associations. — Pete, Mar 11 '11 at 18:57

score 6 · Answer 1 · answered Mar 23 '11 at 00:17

6

An EntitySet is just a collection of entities. It implements IEnumerable, not IQueryable. The Active Record pattern specifies that entities be directly responsible for their own persistence. OR mapper entities don't have any direct knowledge of the persistence layer. OR Mappers place this responsibility, along with Unit Of Work, and Identity Map responsibilities into the Data Context. So if you need to query the data source, you gotta use the context (or a Table object). To change this would bend the patterns in use.

answered Mar 23 '11 at 00:17

mikesigs

10,491
3
33
40

2

I see the point. They did have this enabled in some early beta versions of linq-to-sql, though. Maybe the problem is that I prefer another pattern in common use! I am finding that I need to rip out huge chunks of l2s anyway and replace with good old fashioned handwritten SQL and plain objects. – Pete Apr 11 '11 at 15:03

score 0 · Answer 2 · edited May 23 '17 at 12:09

I had a similar problem: How can I make this SelectMany use a join. After messing with LINQPad for a good amount of time I found a decent workaround. The key is to push the EntitySet you are looking at inside a SelectMany, Select, Where, etc. Once it's inside that it becomes an Expression and then the provider can turn it into a proper query.

Using your example try this:

var query = from c in Children
            where c == myChild
            from p in c.Parents
            where p.Age > 35
            select p;

I'm not able to 100% verify this query as I don't know the rest of your model. But the first two lines of the query cause the rest of it to become an Expression that the provider turns into a join. This does work with my own example that is on the question linked to above.

Thanks! But my main goal was to keep the syntax so it naturally scoped to children owned only by the parent (without a separate clause for that later in the statement). In general, I am moving more and more code away from LinqToSQL to just plain SQL because it is too time consuming to play these tricks to make efficient SQL come out the other end. — Pete, Jul 18 '11 at 19:28

Linq-to-SQL EntitySet Is Not IQueryable -- Any Workarounds?

2 Answers2

Linked