Sous-requête avec Entity Framework

Je porte un sous-système de NHibernate à Entity Framework et je veux voir la meilleure façon de porter la requête suivante vers 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(); 

La class de solde du count est:

 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; } } 

La table est:

 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 ) ) 

Un exemple de données est (en utilisant des identifiants numériques pour une meilleure compréhension):

• Classe .NET SqlConnection, regroupement de connections et logique de reconnection
• Valeur sans millisecondes lors de la lecture des valeurs DateTime d'une database SQL en C #
• comment détecter SQL Server Timeout de l'application .NET sans utiliser catch Exception
• comment get la valeur autoincrement suivante en sql
• Unité d'essai de test / mise à jour / suppression

 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 

L'entité AccountBalanceByDate détient le solde du count dans un jour spécifique. Si un jour n'a pas de transaction, ce jour n'aura pas de AccountBalanceByDate et nous devrions searchr les jours précédents pour voir le solde de ce count.

Si je request à la date 2014-02-01 je devrais get:

  No results 

Si je request à la date 2014-02-02 je devrais get:

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

Si je request à la date 2014-02-03 je devrais get:

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

Si je request avec la date 2014-02-04 je devrais get:

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

Si je request à la date 2014-02-05 je devrais get:

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

Je peux le faire dans Entity Framework en utilisant du SQL brut, mais ce n'est pas l'idéal.

 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(); } 

Il est préférable d'aller à la database une seule fois, comme dans NHibernate et SQL brut, mais en utilisant juste linq , est-ce possible?

METTRE À JOUR:

Résultats corrigés dans la question.

SQL montrant l'exemple de requête sur GIST: https://gist.github.com/LawfulHacker/275ec363070f2513b887

Échantillon Entity Framework sur GIST: https://gist.github.com/LawfulHacker/9f7bd31a21363ee0b646

• Utiliser SQL View ou SQL Query?
• Pouvons-nous déclarer des variables dans le file app.config
• Quelle est une bonne alternative au triggersment d'une procédure stockée 368 fois pour mettre à jour la database?
• Comment vérifier si une requête SQL réussit avec C #
• Casting Eval ("bitValue") comme Bool

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

 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();