MS SQL Server
  1. MS SQL Server
  3. Problème de requête complexe LINQ to SQL
Intereting Posts
Modifier une instruction CASE pour gérer les valeurs NULL? L'instruction INSERT est en conflit avec la contrainte FOREIGN KEY SAME TABLE Comment faire pour convertir le format Date Heure de 'AAAA-MM-JJ' à 'MM / JJ / AAAA HH: MM: SS AM' TSQL Obtenir des ID insérés Rejoindre les tables maître et de détail sous la forme d'une seule table Supprimer l'heure du server SQL DateTime 2005 Créer une feuille Excel et entrer dans SQL ne fonctionnera pas lorsqu'il est exécuté set? Le server Asp.net c # sql lit datatables du champ binary sql dans un bloc datatables dans un seul logging utiliser pypyodbc pour accéder à une table avec des espaces dans son nom Problème dans une jointure compliquée Est-il possible d'ignorer la longueur varchar lors de la définition de la key étrangère entre les tables? Requête SQL qui est un mélange d'une jointure LEFT et d'une jointure INNER Est-il possible de modifier le type de données SQL défini par l'user? Envoyer une requête en tant que paramètre à la fonction SQL

Problème de requête complexe LINQ to SQL

J'ai 3 tables: Principal (Principal_ID, Scale), Fréquence (Frequency_ID, Value) et Visit (Visit_ID, Principal_ID, Frequency_ID). J'ai besoin d'une requête qui renvoie tous les principaux (dans la table principale), et pour chaque logging, interroger la capacité requirejse pour ce principal, calculée comme suit: 

Capacity = (Principal.Scale == 0 ? 0 : (Frequency.Value == 1 ? 1 : Frequency.Value * 1.8) / Principal.Scale)

J'utilise LINQ to SQL, alors voici la requête: 

 from Principal p in ShopManagerDataContext.Instance.Principals let cap = ( from Visit v in p.Visits let fqv = v.Frequency.Value select (p.Scale != 0 ? ((fqv == 1.0f ? fqv : fqv * 1.8f) / p.Scale) : 0) ).Sum() select new { p, Capacity = cap };

Le TSQL généré: 

 SELECT [t0].[Principal_ID], [t0].[Name], [t0].[Scale], ( SELECT SUM( (CASE WHEN [t0].[Scale] <> @p0 THEN ( (CASE WHEN [t2].[Value] = @p1 THEN [t2].[Value] ELSE [t2].[Value] * @p2 END)) / (CONVERT(Real,[t0].[Scale])) ELSE @p3 END)) FROM [Visit] AS [t1] INNER JOIN [Frequency] AS [t2] ON [t2].[Frequency_ID] = [t1].[Frequency_ID] WHERE [t1].[Principal_ID] = [t0].[Principal_ID] ) AS [Capacity] FROM [Principal] AS [t0]

Et l'erreur que je reçois: 

 SqlException: Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.

Et des idées pour résoudre cela, si possible, dans une requête?

Merci beaucoup d'avance!

Voici 2 façons de le faire en changeant votre approche:

  1. Créez une fonction d'agrégat définie par l'user à l'aide de SQL CLR. Cela peut ne pas être la bonne solution pour vous, mais c'est un ajustement parfait pour le problème comme indiqué. D'une part, cela déplacerait toute la logique dans la couche de données, donc LINQ aurait une valeur limitée. Avec cette approche, vous obtenez l'efficacité, mais il y a un impact important sur votre architecture.
  2. Chargez les tables Visit et Fequency dans un DataSet typé et utilisez LINQ pour les datasets. Cela fonctionnera probablement en utilisant votre code existant, mais je ne l'ai pas essayé. Avec cette approche, votre architecture est plus ou less conservée, mais vous pourriez avoir un gros coup d'efficacité si Visit and Frequency est grand.

Basé sur le commentaire, j'ai une suggestion alternative. Comme votre erreur provient de SQL et que vous n'utilisez pas la nouvelle colonne comme filter, vous pouvez déplacer votre calcul vers le client. Pour que cela fonctionne, vous devez extraire tous les loggings pertinents (en utilisant DataLoadOptions.LoadWith <> dans votre context).

Pour faciliter votre utilisation avec la binding à un DataGrid, il serait probablement plus facile d'enterrer la complexité dans une propriété de Principal. 

 partial class Principal { public decimal Capacity { get { return this.Scale == 0 ? 0 : this.Visits.Select(v => (v.Frequency.Value == 1 ? 1 : v.Frequency.Value * 1.8) / this.Scale).Sum(); } } }

Ensuite, votre récupération devient vraiment simple: 

 using (ShopManagerDataContext context = new ShopManagerDataContext()) { DataLoadOptions options = new DataLoadOptions(); options.LoadWith<Principal>(p => p.Visits); options.LoadWith<Visit>(v => v.Frequency); context.LoadOptions = options; return (from p in context.Principals select p).ToList(); }