Linq2Sql Pipes and Filters on inner list object

Question

I'm using Linq2Sql with pipes/filters. My database consists of Accounts and Transactions tables where each Transaction row is linked to an Account.

When displaying an Account I want to show the Account with all its Transactions. Simple enough. Now I am trying to limit the transactions that are shown by /accounts/4/{year}/{month}/{day} for example.

Account a = AccountsRepository.GetAccounts()
               .WithID(id)
               .FilterTransactions(year, month, day)
               .SingleOrDefault();

So how can I write a filter for returning the Account but also filtering the transactions returned?

When I run profiler without the FilterTransactions I get 2 sql calls...

exec sp_executesql N'SELECT [t0].[ID], [t0].[BankName], 
[t0].[BankCode], [t0].[CardNumber], [t0].[Locale]
FROM [dbo].[Accounts] AS [t0]
WHERE [t0].[ID] = @p0',N'@p0 int',@p0=1

exec sp_executesql N'SELECT [t0].[ID], [t0].[AccountID], 
[t0].[Date], [t0].[Description], [t0].[Amount]
FROM [dbo].[Transactions] AS [t0]
WHERE [t0].[AccountID] = @p0',N'@p0 int',@p0=1

My thinking was, in the filter do something like (simple example for the year)

public static IQueryable<Account> FilterTransactions(
this IQueryable<Account> qry, int? year, int? month, int? day)
{
    ...loop through each Account
    a.Transactions = a.Transactions
    .Where(t => t.Date.Year == year);
}

However, a.Transactions is an EntitySet and the Where returns an IEnumerable.

An alternative solution would be to return an Account to my View and then in the View call a HtmlHelper that calls another Action to display the Transactions. However, this would result in 1 extra sql call and also caused me problems displaying the information I needed.

Answer 1

In the same SQL connection and the same datacontext object, a SQL call to get the account info and a set operation to get the transactions is all but unavoidable. To my understanding you would basically have to write a sproc that returns two table results and then interpret the nasty result back into the two pieces of information you want.

Maybe someone with more wisdom than me could do this with Linq, I do know that you can do it direct in SQL.

Answer 2

Instead I decided to implement a different way by creating a ViewModel that contains both an Account object and a List of transactions. The transactions were filtered in the same action. Due to lazy loading the Entity transactions on the actual Account object are never called unless you iterate over them.