Leaky abstraction in LINQ to SQL EntitySet

Question

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.

Answer 1

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.