title: Subquery with Entity Framework
body: <p>I'm porting a subsystem from <em>NHibernate</em> to <em>Entity Framework</em> and want to see the best way to port the following query to <em>EF</em>.</p>
  
  <pre><code>var date = DateTime.Now; // It can be any day
  AccountBalanceByDate abbd = null;
  var lastBalanceDateByAccountQuery = QueryOver.Of&lt;AccountBalanceByDate&gt;()
      .Where(x =&gt; x.AccountId == abbd.AccountId &amp;&amp; x.Date &lt; date)
      .Select(Projections.Max&lt;AccountBalanceByDate&gt;(x =&gt; x.Date));
  
  var lastBalances = session.QueryOver&lt;AccountBalanceByDate&gt;(() =&gt; abbd)
      .WithSubquery.WhereProperty(x =&gt; x.Date).Eq(lastBalanceDateByAccountQuery)
      .List();
  </code></pre>
  
  <p>The account balance class is:</p>
  
  <pre><code>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; }
  }
  </code></pre>
  
  <p>The table is:</p>
  
  <pre><code>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
      )
  )
  </code></pre>
  
  <p>A sample data is (using numeric ids for better understanding):</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The <em>AccountBalanceByDate</em> entity hold the account balance in a specific day. If a day doesn't have a transaction, that day will not have an <em>AccountBalanceByDate</em> and we should look for the previous days to see the balance for that account.</p>
  
  <p>If I query with the date <em>2014-02-01</em> I should get:</p>
  
  <pre><code> No results
  </code></pre>
  
  <p>If I query with the date <em>2014-02-02</em> I should get:</p>
  
  <pre><code> 1 | 2014-02-01  | 101     | 1390.00000
   2 | 2014-02-01  | 102     | 1360.00000
   3 | 2014-02-01  | 103     | 1630.00000
  </code></pre>
  
  <p>If I query with the date <em>2014-02-03</em> I should get:</p>
  
  <pre><code> 1 | 2014-02-01  | 101     | 1390.00000
   4 | 2014-02-02  | 102     | 1370.00000
   5 | 2014-02-02  | 103     | 1700.00000
  </code></pre>
  
  <p>If I query with the date <em>2014-02-04</em> I should get:</p>
  
  <pre><code> 4 | 2014-02-02  | 102     | 1370.00000
   6 | 2014-02-03  | 101     | 1490.00000
   7 | 2014-02-03  | 103     | 1760.00000
  </code></pre>
  
  <p>If I query with the date <em>2014-02-05</em> I should get:</p>
  
  <pre><code> 7 | 2014-02-03  | 103     | 1760.00000
   8 | 2014-02-04  | 101     | 1530.00000
   9 | 2014-02-04  | 102     | 1540.00000
  </code></pre>
  
  <p>I can do this in Entity Framework using raw SQL, but it is not the ideal.</p>
  
  <pre><code>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 &lt; @p0
              )", date).ToList(); 
  }
  </code></pre>
  
  <p>It is preferred to go to database just one time, like in <em>NHibernate</em> and raw SQL, but using just <em>linq</em>, is it possible?</p>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>Fixed results in the question.</p>
  
  <p>SQL showing the sample query on GIST: <a href="https://gist.github.com/LawfulHacker/275ec363070f2513b887" rel="noreferrer">https://gist.github.com/LawfulHacker/275ec363070f2513b887</a></p>
  
  <p>Entity Framework sample on GIST: <a href="https://gist.github.com/LawfulHacker/9f7bd31a21363ee0b646" rel="noreferrer">https://gist.github.com/LawfulHacker/9f7bd31a21363ee0b646</a></p>