Как мне преобразовать сложный T-SQL в Linq
Я работаю в проекте Asp.NET Core 2.0, используя EntityFramework Core 2.0.
Я пытаюсь преобразовать существующие хранимые процедуры SQL в Linq в EntityFramework Core, и у меня возникают трудности с этим конкретным сегментом 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)
};
Но я не могу понять перевод Linq упаковочного кода T-SQL;
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
1 ответ
Решение
Для перевода SQL в понимание запросов LINQ:
- Переведите подвыборы как отдельно объявленные переменные.
- Переводите каждое предложение в порядок предложений LINQ, переводя монадические и агрегатные операторы (
DISTINCT,TOP,MIN,MAXи т. д.) в функции, применяемые ко всему запросу LINQ. - Используйте псевдонимы таблиц в качестве переменных диапазона. Используйте псевдонимы столбцов в качестве имен полей анонимного типа.
- Используйте анонимные типы (
new {...}) для нескольких столбцов. LEFT JOINмоделируется с помощьюintoприсоединяйся и делай из другогоfromприсоединяемая переменная с последующим.DefaultIfEmpty(),- замещать
COALESCEс условным оператором (?:) иnullтестовое задание. - Переведите
INв.Contains()а такжеNOT INв!...Contains(), - Перевестих
BETWEENнизкийANDпо убыванию<=Икс&&Икс<=высокий SELECT *должен быть заменен на select range_variable или для соединений анонимный объект, содержащий все переменные диапазона.SELECTполя должны быть заменены наselect new {...}создание анонимного объекта со всеми нужными полями или выражениями.- правильный
FULL OUTER JOINдолжен обрабатываться методом расширения.
Для вашего запроса у вас есть 3 подзапроса на основе 3 SELECTи вы можете перевести их изнутри:
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();