Leaky abstraction in LINQ to SQL EntitySet

Question

Hi,

I’m having trouble with some dbml generated classes that don’t to resolve down to efficient SQL. Imagine I have an Accounts table and a Transactions table where each transaction is associated with a particular account. I load all this into dbml and out pops an Account class and a Transaction class. The Account class has an EntitySet reference to a collection of Transactions that represents all the transactions on that account. Fair enough.

Now suppose I only want the transactions for the current accounting period. So I add a method like this:

public IEnumerable<Transaction> CurrentTransactions
{
 get
 {
  DateTime dtStart = CurrentPeriod;
  DateTime dtEnd = NextPeriod;
  return
   from t in Transactions
   orderby t.date
   where t.date >= CurrentPeriod && t.date <= NextPeriod
   select t;
 }
}

Looks nice and it works, but the SQL is not good:

SELECT [t0].[id], [t0].[account_id], [t0].[date], [t0].[description], [t0].[amount], [t0].[sign]
FROM [dbo].[transactions] AS [t0]
WHERE [t0].[account_id] = @p0

Ie: it’s pulling the entire set of transactions down and processing it with LINQ for Objects. I’ve tried taking out the where clause, the orderby clause, replacing the dates with constants it’s all still done client side.

For comparison, I tried calling the Transactions collection directly off the data context:

DateTime dtStart = account.CurrentPeriod;
DateTime dtEnd = account.NextPeriod;
IEnumerable<Transaction> trans=
    from t in MyDataContext.Transactions
    orderby t.date
    where t.date >= dtStart && t.date <= dtEnd && t.account_id==iAccountID
    select t;

and it works beautifully:

SELECT [t0].[id], [t0].[account_id], [t0].[date], [t0].[description], [t0].[amount], [t0].[sign]
FROM [dbo].[transactions] AS [t0]
WHERE ([t0].[date] >= @p0) AND ([t0].[date] <= @p1) AND ([t0].[account_id] = @p2)
ORDER BY [t0].[date]

So after all that, I have two questions:

1. Is the above behaviour of the Transactions EntitySet correct and/or is there a way to fix it?
2. How do I implement the above "fix" as a method on my Account class. Entity classes generated by dbml don't have access to a DataContext.

Any help appreciated.

Answer 1

Switch from using IEnumerable throughout to IQueryable instead and your SQL will be optimized to only pull on demand what you need.

Answer 2

What is the type of Transactions in the first example?

Remember you are using Extension Methods. The Linq Extension Methods that are used are dependant on the interface Transactions implements:

• IQueryable<T> would be linq-to-sql or linq-to-entities or ...
• IEnumerable<T> will give you linq-to-objects.

Edit:

This is the fingerprint of the EntitySet type:

public sealed class EntitySet<TEntity> : IList, 
    ICollection, IList<TEntity>, ICollection<TEntity>, IEnumerable<TEntity>, 
    IEnumerable, IListSource
where TEntity : class

To answer your questions:

1. Transactions does not implement IQueryable<T> so it is the correct behaviour
2. Your account class will need to be able to reference the Transactions Table object

Answer 3

You just cannot do that, sadly. Collection properties generated for LINQ to SQL entity classes are not IQueryable; therefore, any queries performed on them will be using LINQ to Objects. This is by design. As you rightly note yourself, to get efficient query you have to query over Transactions taken from your DataContext, but you don't have one in your property gettor.

At this point your options are either:

• Make it a method which takes a DataContext as argument; or
• Use reflection hackery to retrive the context - entity itself doesn't store it, but EntitySets on it do, albeit indirectly - naturally this is version specific, prone to breakage, etc.

So far as I know, Entity Framework does not have this limitation, since its collection properties are ObjectQuery<T> - which is IQueryable.