LawfulHacker LawfulHacker - 2 months ago 17
SQL Question

Subquery with Entity Framework

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

Answer

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/sergio-garcia/9f7bd31a21363ee0b646