How do I convert complex T-SQL into Linq

Question

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

Answer 1

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