如何将复杂的T-SQL转换为Linq[英] How do I convert complex T-SQL into Linq

本文是小编为大家收集整理的关于如何将复杂的T-SQL转换为Linq的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我正在使用EntityFramework Core 2.0.

ASP.NET Core 2.0项目中工作

我正在尝试将现有的旧版SQL存储过程转换为EntityFramework Core中的LINQ,并且我对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)

我尝试通过linqer v4.6运行它,但是它基本上从SQL窗口传入了Linq窗口.

我将存储的过程代码删除到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)

和linqer生成了我在项目中拥有的LINQ代码;

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

,但我无法弄清楚包装T-SQL代码的LINQ翻译;

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

如果有帮助,我提供了有关该项目的进一步详细信息;

EFN SQL Server EFN表具有以下字段;

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

在我的项目中,我有一个EFN实体类,如下所示;

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

}

这是我的dbcontext类

公共类SerialNumberContext:dbContext {

    public DbSet<Efn> Efns { get; set; }

    public SerialNumberContext(DbContextOptions<SerialNumberContext> options) : base(options)
    {

    }


    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Efn>()
            .HasIndex(e => new { e.Mid, e.HighSerial, e.Date, e.LowSerial })
            .IsUnique()
            .HasName("IX_Efn_Mid_HighSerial_Date_LowSerial")
            .ForSqlServerIsClustered();

        modelBuilder.Entity<Efn>()
            .HasIndex(e => new { e.Mid, e.LowSerial })
            .HasName("IX_Efn_Mid_LowSerial");

        base.OnModelCreating(modelBuilder);
    }

}

这是完整的遗产存储过程

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

推荐答案

用于将SQL转换为LINQ查询理解:

  1. 将子选择单独转换为声明变量.
  2. 将每个子句在LINQ子句顺序中转换,将Monadic和聚合运算符转换为应用于整个LINQ查询的函数.
  3. .
  4. 使用表别名作为范围变量.将列别名用作匿名类型字段名称.
  5. 对多列使用匿名类型(new { ... }).
  6. LEFT JOIN通过使用into joinvariable 模拟,并从from进行 joinvariable ,然后是.DefaultIfEmpty().
  7. 用条件运算符(?:)和A null替换COALESCE
  8. translate IN .Contains()和NOT IN to ! ... Contains().
  9. translate x BETWEEN low AND high> high to low> low <= x && x <= high .
  10. SELECT *必须替换为select range_variable或对于加入,一个包含所有范围变量的匿名对象.
  11. SELECT必须用select new { ... }替换字段,创建一个具有所有所需字段或表达式的匿名对象.
  12. 正确的FULL OUTER JOIN必须使用扩展方法处理.

对于查询,您有3个基于3 SELECT s的子征服,您可以从内而外翻译它们:

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

本文地址:https://www.itbaoku.cn/post/1556761.html

问题描述

I am working in an Asp.NET Core 2.0 project, using EntityFramework Core 2.0.

I am trying to convert an existing legacy SQL stored procedures into Linq in EntityFramework Core and I am having difficulty with this particular segment of 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)

I tried running it through Linqer v4.6 but it just basically passed the same from the SQL window into the Linq window.

I cut the Stored Procedure code down to this in 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)

And Linqer produced Linq code that I have in my project as this;

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

But I can't figure out the Linq translation of the wrapping T-SQL code;

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

If it helps, I have provided some further details about the project;

The Efn SQL Server Efn table has the following fields;

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

and in my project, I have an Efn entity class as follows;

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

}

Here is my dbcontext class

public class SerialNumberContext : DbContext {

    public DbSet<Efn> Efns { get; set; }

    public SerialNumberContext(DbContextOptions<SerialNumberContext> options) : base(options)
    {

    }


    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Efn>()
            .HasIndex(e => new { e.Mid, e.HighSerial, e.Date, e.LowSerial })
            .IsUnique()
            .HasName("IX_Efn_Mid_HighSerial_Date_LowSerial")
            .ForSqlServerIsClustered();

        modelBuilder.Entity<Efn>()
            .HasIndex(e => new { e.Mid, e.LowSerial })
            .HasName("IX_Efn_Mid_LowSerial");

        base.OnModelCreating(modelBuilder);
    }

}

Here is the complete legacy Stored Procedure

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

推荐答案

For translating SQL to LINQ query comprehension:

  1. Translate subselects as separately declared variables.
  2. Translate each clause in LINQ clause order, translating monadic and aggregate operators (DISTINCT, TOP, MIN, MAX etc) into functions applied to the whole LINQ query.
  3. Use table aliases as range variables. Use column aliases as anonymous type field names.
  4. Use anonymous types (new { ... }) for multiple columns.
  5. LEFT JOIN is simulated by using into joinvariable and doing another from from the joinvariable followed by .DefaultIfEmpty().
  6. Replace COALESCE with the conditional operator (?:)and a null test.
  7. Translate IN to .Contains() and NOT IN to !...Contains().
  8. Translate x BETWEEN low AND high to low <= x && x <= high.
  9. SELECT * must be replaced with select range_variable or for joins, an anonymous object containing all the range variables.
  10. SELECT fields must be replaced with select new { ... } creating an anonymous object with all the desired fields or expressions.
  11. Proper FULL OUTER JOIN must be handled with an extension method.

For your query, you have 3 sub-queries based on the 3 SELECTs and you can translate them from inside out:

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