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:
- Is the above behaviour of the Transactions EntitySet correct and/or is there a way to fix it?
- 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.
Best Answer
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 overTransactions
taken from yourDataContext
, but you don't have one in your property gettor.At this point your options are either:
DataContext
as argument; orEntitySet
s 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 isIQueryable
.