Langage C#
  1. Langage C#
  3. Comment convertir des T-SQL complexes en Linq
Intereting Posts
C #: Comment convertir un fichier TIFF multipage via MemoryStream en une seule image? Comment désactiver la clé de locking des majuscules Afficher XML (formaté) dans un Razor Textarea Échappement SQL avec sqlite en C # Piping dans un fichier sur la ligne de commande à l’aide de System.Diagnostics.Process C # Func et opérateur conditionnel Interfaces et en-têtes Utiliser IComparer pour le sorting Connexion à LDAP à partir de C # à l’aide de DirectoryServices Les services de données ADO.NET ont leur place dans la conception globale Comment puis-je obtenir la clé précédente de SortedDictionary? Comment puis-je m’échapper “en chaîne verbatim? c # Directory.GetDirectories à l’exclusion des dossiers Le générateur de nombres aléatoires choisit toujours la même valeur lorsqu’il est exécuté dans une boucle Pourquoi les types de nombres C # sont-ils immuables?

Comment convertir des T-SQL complexes en Linq

Je travaille dans un projet Asp.NET Core 2.0 en utilisant EntityFramework Core 2.0.

J’essaie de convertir des procédures stockées SQL existantes en Linq dans EntityFramework Core et je rencontre des difficultés avec ce segment particulier de T-SQL. 

SET @Target = (SELECT MIN(A.[Serial]) FROM (SELECT [HighSerial] + 1 AS 'Serial' FROM [Efn] WHERE [Mid] = @Mid AND ([HighSerial] + 1) BETWEEN @MinSerial AND @MaxSerial AND ([HighSerial] + 1) NOT IN (SELECT [LowSerial] FROM [Efn] WHERE [Mid] = @Mid)) A)

J’ai essayé de l’exécuter avec Linqer v4.6, mais il a simplement passé la même chose de la fenêtre SQL à la fenêtre Linq.

J’ai coupé le code de procédure stockée jusqu’à celui-ci dans Linqer; 

 SELECT [HighSerial] + 1 AS 'Serial' FROM [Efn] WHERE [Mid] = @Mid AND ([HighSerial] + 1) BETWEEN @MinSerial AND @MaxSerial AND ([HighSerial] + 1) NOT IN (SELECT [LowSerial] FROM [Efn] WHERE [Mid] = @Mid)

Et Linqer a produit le code Linq que j’ai dans mon projet comme ceci; 

  var query = from Efn in _serialNumberContext.Efns where Efn.Mid == mid && (Efn.HighSerial + 1) >= minSerial && (Efn.HighSerial + 1) <= maxSerial && ! (from Efn0 in _serialNumberContext.Efns where Efn0.Mid == mid select new { Efn0.LowSerial }).Contains(new { LowSerial = (Int64)(Efn.HighSerial + 1) }) select new { Serial = (Efn.HighSerial + 1) };

Mais je n’arrive pas à comprendre la traduction Linq du code T-SQL enveloppant; 

 SET @Target = (SELECT MIN(A.[Serial]) FROM ( ... ... ...) A)

Si cela peut aider, j’ai fourni quelques détails supplémentaires sur le projet;

La table Efn Efn SQL Server comporte les champs suivants; 

  [Mid] INT NOT NULL, [Date] DATE NOT NULL, [LowSerial] BIGINT NOT NULL, [HighSerial] BIGINT NOT NULL

et dans mon projet, j’ai une classe d’entités Efn comme suit; 

 public class Efn { [Required] [Column(TypeName = "int")] public int Mid { get; set; } [Required] [Column(TypeName="date")] public DateTime Date { get; set; } [Required] [Column(TypeName = "bigint")] public long LowSerial { get; set; } [Required] [Column(TypeName = "bigint")] public long HighSerial { get; set; } }

Voici ma classe dbcontext

Classe publique SerialNumberContext: DbContext { 

  public DbSet Efns { get; set; } public SerialNumberContext(DbContextOptions options) : base(options) { } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity() .HasIndex(e => new { e.Mid, e.HighSerial, e.Date, e.LowSerial }) .IsUnique() .HasName("IX_Efn_Mid_HighSerial_Date_LowSerial") .ForSqlServerIsClustered(); modelBuilder.Entity() .HasIndex(e => new { e.Mid, e.LowSerial }) .HasName("IX_Efn_Mid_LowSerial"); base.OnModelCreating(modelBuilder); } }

Voici la procédure stockée héritée complète 

 USE [SerialNumberDB] GO IF EXISTS (SELECT 1 FROM sys.objects WHERE name = N'fetchEfnSerial' AND [type]=N'P') BEGIN DROP PROCEDURE [dbo].[fetchEfnSerial] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[fetchEfnSerial] ( @Mid INT, @MinSerial BIGINT = NULL, @MaxSerial BIGINT = NULL ) AS DECLARE @Date DATE = CONVERT(DATE, GETDATE()) DECLARE @Target BIGINT; DECLARE @MAX_BIG_INT BIGINT = 9223372036854775807; IF (@MinSerial IS NULL) BEGIN SET @MinSerial = 1 END IF (@MaxSerial IS NULL) BEGIN SET @MaxSerial = @MAX_BIG_INT END SET @Target = NULL; BEGIN TRY BEGIN TRANSACTION IF ((SELECT 1 FROM [Efn] WHERE @MinSerial BETWEEN [LowSerial] AND [HighSerial] AND [Mid] = @Mid) IS NULL) BEGIN SET @Target = @MinSerial END ELSE BEGIN SET @Target = (SELECT MIN(A.[Serial]) FROM (SELECT [HighSerial] + 1 AS 'Serial' FROM [Efn] WHERE [Mid] = @Mid AND ([HighSerial] + 1) BETWEEN @MinSerial AND @MaxSerial AND ([HighSerial] + 1) NOT IN (SELECT [LowSerial] FROM [Efn] WHERE [Mid] = @Mid)) A) END IF @Target IS NULL BEGIN DECLARE @ErrorText VARCHAR(255) = 'ERROR: No Serial Numbers are available in the specified range; between MinSerial: ' + CONVERT(VARCHAR(19), @MinSerial) + ' and MaxSerial: ' + CONVERT(VARCHAR(19), @MaxSerial) RAISERROR (@ErrorText, 16, 1) END IF @Target IS NOT NULL BEGIN IF EXISTS (SELECT 1 FROM [Efn] WHERE [Mid] = @Mid AND [Date] = @Date AND [HighSerial] = @Target - 1) BEGIN -- If for this MID, the max value in the serial number block before the target -- serial number is from today, just update the max serial number of that block. UPDATE [Efn] SET [HighSerial] = @Target WHERE [Mid] = @Mid AND [HighSerial] = @Target - 1 END ELSE BEGIN -- Otherwise, we need to make a new serial number block for this MID for today. INSERT INTO [Efn] SELECT @Mid, @Date, @Target, @Target END -- Return the target serial number to the caller so it can be used. SELECT @Target AS 'Serial' END COMMIT TRANSACTION END TRY BEGIN CATCH IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION DECLARE @ERRORMSG NVARCHAR(255) SET @ERRORMSG = ERROR_MESSAGE() RAISERROR(@ERRORMSG, 16, 1) END CATCH GO

Pour traduire SQL en compréhension de requête LINQ:

  1. Traduit les sous-requêtes en tant que variables déclarées séparément.
  2. Traduisez chaque clause dans l’ordre des clauses LINQ, en traduisant les opérateurs monadiques et agrégés ( DISTINCT , TOP , MIN , MAX etc.) en fonctions appliquées à l’ensemble de la requête LINQ.
  3. Utilisez des alias de table comme variables d’intervalle. Utilisez des alias de colonne comme noms de champs de type anonyme.
  4. Utilisez des types anonymes ( new { … } ) pour plusieurs colonnes.
  5. LEFT JOIN est simulé en utilisant joinvariable et en en faisant une autre à partir from .DefaultIfEmpty() suivie de .DefaultIfEmpty() .
  6. Remplacez COALESCE par l’opérateur conditionnel ( ?: COALESCE par un test null .
  7. Traduire IN en .Contains() et NOT IN ! … Contains() .
  8. Traduire x entre bas AND haut en bas <= x && x <= haut .
  9. SELECT * doit être remplacé par select plage_variable ou pour les jointures, un object anonyme contenant toutes les variables de plage.
  10. SELECT champs SELECT doivent être remplacés par select new { ... } créer un object anonyme avec tous les champs ou expressions souhaités.
  11. FULL OUTER JOIN doit être traité avec une méthode d’extension.

Pour votre requête, vous avez 3 sous-requêtes basées sur les 3 SELECT et vous pouvez les traduire de l'intérieur vers l'extérieur: 

 var lowSerials = from Efn in _serialNumberContext.Efns where Efn.Mid == mid select Efn.LowSerial; var serials = from Efn in _serialNumberContext.Efns where Efn.Mid == mid && minSerial <= Efn.HighSerial + 1 && Efn.HighSerial + 1 <= maxSerial && !lowSerials.Contains(Efn.HighSerial + 1) select Efn.HighSerial + 1; var Target = serials.Min();