Sous-requête avec Entity Framework

Je suis portage d'un sous-système de NHibernate à Entity Framework et que vous voulez voir la meilleure façon de port la requête suivante pour 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();

Le solde du compte de la classe 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 la suivante:

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 échantillon de données (en utilisant des id numériques pour mieux comprendre):

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

La AccountBalanceByDate entité tenir le solde du compte à un jour précis. Si un jour n'est pas une transaction, que la journée ne sera pas avoir un AccountBalanceByDate et nous devons chercher les jours précédents pour voir le solde de ce compte.

Si j'ai une requête à la date 2014-02-01 je devrais obtenir:

 No results

Si j'ai une requête à la date 2014-02-02 je devrais obtenir:

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

Si j'ai une requête à la date 2014-02-03 je devrais obtenir:

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

Si j'ai une requête à la date 2014-02-04 je devrais obtenir:

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

Si j'ai une requête à la date 2014-02-05 je devrais obtenir:

 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 le Cadre de l'Entité à l'aide de matières SQL, mais il 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 base de données une fois seulement, comme dans NHibernate et SQL brut, mais en utilisant simplement linq, est-il possible?

Mise à JOUR:

Fixe les résultats de la question.

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

Entité Cadre de l'échantillon sur l'ESSENTIEL: https://gist.github.com/LawfulHacker/9f7bd31a21363ee0b646

Votre propre requête SQL NE PAS produire les résultats que vous avez donné à votre question! L'interrogation de 2014-02-01 ne retourne rien! FIXER votre requête.
veuillez noter que j'ai une sous-requête qui référence le haut de tableau et il le faire pour chaque ligne. Ensuite, pour chaque ligne, il obtient son numéro de compte et obtenir le max de date. Il me renvoie tous les résultats du max la date de chaque compte, le max date peut être différente pour chaque compte. C'est le déclic, mon exemple est correct, j'ai vérifié à nouveau quand vous le fait qu'elle est erronée.
J'obtiens ce que vous essayez de faire, mais je suis entré dans votre exacte de données dans SQL Server 2008 et a couru votre requête. '2014-02-01' ne renvoie rien. '2014-02-02' renvoie Id 3,2,1. '2014-02-03 retour des Id 5,4,1. La "2014-02-04" renvoie Id 7,4,6.
Le SQL Échantillon est sur le GIST, l'EF échantillon est sur le chemin. Et je montrais les mauvais résultats (erreur de frappe), ils sont pour le lendemain. Pour la journée de 2014-02-01' sont pas de résultats.
L'EF Échantillon est sur le GIST, il en résulte exactement les résultats à cette question.

OriginalL'auteur LawfulHacker | 2014-05-15

  1. 21

    La requête suivante faire exactement ce dont j'ai besoin avec une seule requête à la base de données:

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

    Merci @AgentShark pour l'aider.

    Le code est sur GIST: https://gist.github.com/LawfulHacker/9f7bd31a21363ee0b646

    Au cours de vos tests, cela n'a pas besoin de plusieurs allers-retours?
    Non, vous pouvez inspecter le SQL généré et c'est juste une requête SQL.

    OriginalL'auteur

  2. 3

    Enfin, une 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();

    Que vous vouliez utiliser LINQ, mais personnellement, je pourrais bien le SQL pour la maintenabilité.

    - Je mettre à jour l'essentiel de l'échantillon avec votre solution, veuillez noter qu'il alambics incorrect. En effet, j'apprécie beaucoup votre aide.
    Je suis en désaccord avec vous monsieur, les Id de retour de ce LINQ correspondance des Identifiants de la base SQL. Je viens de codé en dur de la date. J'ai testé cette VS2013 à l'encontre de vos données de test.
    Veuillez vérifier les gist. Mais j'ai trouvé une solution.
    Le uniquement la différence est que le mien est commandé par AccountId.
    Désolé, votre dernière version de travail.

    OriginalL'auteur