Abstraction leak in LINQ to SQL EntitySet

Question

I am having problems with some dbml generated classes that are not designed to make good use of SQL. Imagine that I have an account table and a transaction table where each transaction is associated with a specific account. I load all of this into dbml and produce an Account class and a Transaction class. The Account class has an EntitySet reference for a transaction collection that represents all transactions in this account. Fair enough.

Now suppose I only need transactions for the current reporting period. Therefore, 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; } } 

It looks good and it works, but SQL is not very 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 

That is: pull out the entire set of transactions and process it using LINQ for objects. Ive tried to pull out a where clause, an orderby clause, replacing dates with constants while still remaining on the client side.

For comparison, I tried to call the transaction assembly directly from 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 great:

 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 this, I have two questions:

• Is the EntitySet transaction behavior above correct and / or is there a way to fix it?
• How to implement the above “fix” as a method for my Account class. The object classes generated by dbml do not have access to the DataContext.

Answer 1

You simply cannot do this, unfortunately. Collection properties generated for LINQ to SQL entity classes are not IQueryable ; therefore, any queries executed on them will use LINQ to Objects. This is by design. As you correctly noticed yourself, in order to get an effective request, you must request Transactions taken from your DataContext , but you do not have it in your getter.

At this point, your options are:

• Make a method that takes a DataContext as an argument; or
• Using a reflective hacker to extract context - the entity itself does not save it, but EntitySet on it, although indirectly - naturally, this is a version prone to breakage, etc.

As far as I know, the Entity Framework does not have this limitation, since its properties of the ObjectQuery<T> collection are IQueryable .

Answer 2

What is the type of transaction in the first example?

Remember that you are using extension methods. The Linq extension methods used are interface dependent. Transactions implement:

• IQueryable <T> will be linq-to-sql or linq-to-entity or ...
• IEnumerable <T> will provide you with linq-to-objects.

Edit:

This is a fingerprint of type EntitySet:

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

To answer your questions:

• Transactions do not implement IQueryable <T> so this is the correct behavior.
• Your account class must be able to reference a transaction table object.

Answer 3

Switch from using IEnumerable throughout to IQueryable, and your SQL will be optimized just to require only what you need.