Entity Framework sample for stack overflow.

StackOverflow_Sample.cs

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFSample
{
    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; }
    }

    public class AccountContext : DbContext
    {
        public DbSet<AccountBalanceByDate> AccountBalanceByDate { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<AccountBalanceByDate>().ToTable("AccountBalanceByDate");
            base.OnModelCreating(modelBuilder);
        }

        public AccountContext()
        {
        }
    } 

    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new AccountContext())
            {
                if (!context.AccountBalanceByDate.Any())
                {
                    context.AccountBalanceByDate.AddRange(
                        new[]
                        {
                            new AccountBalanceByDate() { AccountId = 101, Date = DateTime.Parse("2014-02-01"), Balance = 1390 },
                            new AccountBalanceByDate() { AccountId = 102, Date = DateTime.Parse("2014-02-01"), Balance = 1360 },
                            new AccountBalanceByDate() { AccountId = 103, Date = DateTime.Parse("2014-02-01"), Balance = 1630 },
                            new AccountBalanceByDate() { AccountId = 102, Date = DateTime.Parse("2014-02-02"), Balance = 1370 },
                            new AccountBalanceByDate() { AccountId = 103, Date = DateTime.Parse("2014-02-02"), Balance = 1700 },
                            new AccountBalanceByDate() { AccountId = 101, Date = DateTime.Parse("2014-02-03"), Balance = 1490 },
                            new AccountBalanceByDate() { AccountId = 103, Date = DateTime.Parse("2014-02-03"), Balance = 1760 },
                            new AccountBalanceByDate() { AccountId = 101, Date = DateTime.Parse("2014-02-04"), Balance = 1530 },
                            new AccountBalanceByDate() { AccountId = 102, Date = DateTime.Parse("2014-02-04"), Balance = 1540 }
                        });

                    context.SaveChanges();
                }
            }

            using (var context = new AccountContext())
            {
                Console.WriteLine("Expected result");

                DateTime date = new DateTime(2014, 2, 1);
                
                while (date <= new DateTime(2014, 2, 5))
                {
                    Console.WriteLine("Results for {0}", date);

                    var accountBalance = context.AccountBalanceByDate.SqlQuery(
                        @"SELECT * FROM 
                            [AccountBalanceByDate] AB
                        WHERE
                            DATE = (
                                SELECT
                                    MAX(Date) 
                                FROM 
                                    [AccountBalanceByDate]
                                WHERE
                                    AccountId = AB.AccountId AND DATE < @p0
                            )", date);

                    Console.WriteLine("Date       | Account | Balance");
                    Console.WriteLine("--------------------------");

                    foreach (var a in accountBalance)
                    {
                        Console.WriteLine("{0:yyyy-MM-dd} | {1:7} | {2:10.2}", a.Date, a.AccountId, a.Balance);
                    }

                    Console.WriteLine();
                    Console.WriteLine("--------------------------");
                    Console.WriteLine();

                    date = date.AddDays(1);
                }

                Console.ReadKey();
            }

            using (var context = new AccountContext())
            {
                Console.WriteLine("Final solution");

                DateTime date = new DateTime(2014, 2, 1);

                while (date <= new DateTime(2014, 2, 5))
                {
                    Console.WriteLine("Results for {0}", date);

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


                    Console.WriteLine("Date       | Account | Balance");
                    Console.WriteLine("--------------------------");

                    foreach (var a in accountBalance)
                    {
                        Console.WriteLine("{0:yyyy-MM-dd} | {1:7} | {2:10.2}", a.Date, a.AccountId, a.Balance);
                    }

                    Console.WriteLine();
                    Console.WriteLine("--------------------------");
                    Console.WriteLine();

                    date = date.AddDays(1);
                }

                Console.ReadKey();
            }
        }
    }
}