Subquery with Entity Framework

Question

I'm porting a subsystem from NHibernate to Entity Framework and want to see the best way to port the following query to EF.

var date = DateTime.Now; // It can be any day
AccountBalanceByDate abbd = null;
var lastBalanceDateByAccountQuery = QueryOver.Of<AccountBalanceByDate>()
    .Where(x => x.AccountId == abbd.AccountId && x.Date < date)
    .Select(Projections.Max<AccountBalanceByDate>(x => x.Date));

var lastBalances = session.QueryOver<AccountBalanceByDate>(() => abbd)
    .WithSubquery.WhereProperty(x => x.Date).Eq(lastBalanceDateByAccountQuery)
    .List();

The account balance class is:

public class AccountBalanceByDate
{
    public virtual int Id { get; set; }
    public virtual int AccountId { get; set; }
    public virtual DateTime Date { get; set; }
    public virtual decimal Balance { get; set; }
}

The table is:

CREATE TABLE [dbo].[AccountBalanceByDate]
(
    [Id]        int NOT NULL,
    [AccountId] int NOT NULL,
    [Date]      [datetime] NOT NULL,
    [Balance]   [decimal](19, 5) NOT NULL,

    PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )
)

A sample data is (using numeric ids for better understanding):

Id | Date        | Account | Balance
------------------------------------
 1 | 2014-02-01  | 101     | 1390.00000
 2 | 2014-02-01  | 102     | 1360.00000
 3 | 2014-02-01  | 103     | 1630.00000
 4 | 2014-02-02  | 102     | 1370.00000
 5 | 2014-02-02  | 103     | 1700.00000
 6 | 2014-02-03  | 101     | 1490.00000
 7 | 2014-02-03  | 103     | 1760.00000
 8 | 2014-02-04  | 101     | 1530.00000
 9 | 2014-02-04  | 102     | 1540.00000

The AccountBalanceByDate entity hold the account balance in a specific day. If a day doesn't have a transaction, that day will not have an AccountBalanceByDate and we should look for the previous days to see the balance for that account.

If I query with the date 2014-02-01 I should get:

 No results

If I query with the date 2014-02-02 I should get:

 1 | 2014-02-01  | 101     | 1390.00000
 2 | 2014-02-01  | 102     | 1360.00000
 3 | 2014-02-01  | 103     | 1630.00000

If I query with the date 2014-02-03 I should get:

 1 | 2014-02-01  | 101     | 1390.00000
 4 | 2014-02-02  | 102     | 1370.00000
 5 | 2014-02-02  | 103     | 1700.00000

If I query with the date 2014-02-04 I should get:

 4 | 2014-02-02  | 102     | 1370.00000
 6 | 2014-02-03  | 101     | 1490.00000
 7 | 2014-02-03  | 103     | 1760.00000

If I query with the date 2014-02-05 I should get:

 7 | 2014-02-03  | 103     | 1760.00000
 8 | 2014-02-04  | 101     | 1530.00000
 9 | 2014-02-04  | 102     | 1540.00000

I can do this in Entity Framework using raw SQL, but it is not the ideal.

using (var context = new DbContext()) 
{ 
    var lastBalances = context.AccountBalanceByDate.SqlQuery(
        @"SELECT
            *
        FROM 
            [AccountBalanceByDate] AB
        WHERE
            DATE = (
                SELECT
                    MAX(Date) 
                FROM 
                    [AccountBalanceByDate]
                WHERE
                    AccountId = AB.AccountId AND DATE < @p0
            )", date).ToList(); 
}

It is preferred to go to database just one time, like in NHibernate and raw SQL, but using just linq, is it possible?

UPDATE:

Fixed results in the question.

SQL showing the sample query on GIST: https://gist.github.com/LawfulHacker/275ec363070f2513b887

Entity Framework sample on GIST: https://gist.github.com/LawfulHacker/9f7bd31a21363ee0b646

Solution 1

The following query do exactly what I need with just one query to the database:

var accountBalance = context
    .AccountBalanceByDate
    .Where(a => 
        a.Date == context.AccountBalanceByDate
             .Where(b => b.AccountId == a.AccountId && b.Date < date).Max(b => b.Date));

Thanks @AgentShark for the help.

The code is on GIST: https://gist.github.com/sergiogarciadev/9f7bd31a21363ee0b646

Solution 2

Finally, a solution. :)

var date = DateTime.Now; // It can be any day
var lastBalances = (from a in context.AccountBalanceByDate
        where a.Date < date
        group a by new {a.AccountId} into g
        select g.OrderByDescending(a => a.Date).FirstOrDefault() into r
        select new
        {
            Id = r.Id,
            AccountId = r.AccountId,
            Date = r.Date,
            Balance = r.Balance
        }).ToList();

You wanted it in LINQ, but personally, I might of kept the SQL for maintainability.

Comments

[sergiogarciadev]

I update the gist sample with your solution, please note that it stills incorrect. Indeed, I appreciate a lot your help.

[Agent Shark]

@SergioGarcia I have to disagree with you sir, Ids returned from this LINQ match Ids from the SQL. I just hard coded the date. I tested this in VS2013 against your test data.

[sergiogarciadev]

Please check the gist. But I found a solution.

[Agent Shark]

The only difference is that mine is ordered by AccountId.

[Travis J]

During your testing, this didn't require multiple round trips?

[sergiogarciadev]

No, you can inspect the generated SQL and it is just one SQL query.