如何将复杂的T-SQL转换为Linq [英] How do I convert complex T-SQL into Linq
问题描述
我正在使用EntityFramework Core 2.0在Asp.NET Core 2.0项目中进行工作.
I am working in an Asp.NET Core 2.0 project, using EntityFramework Core 2.0.
我正在尝试将现有的旧版SQL存储过程转换为EntityFramework Core中的Linq，并且在这部分T-SQL上遇到了困难；
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)
我尝试通过Linqer v4.6运行它，但它基本上只是将相同的内容从SQL窗口传递到Linq窗口中.
I tried running it through Linqer v4.6 but it just basically passed the same from the SQL window into the Linq window.
我在Linqer中将存储过程代码缩减为此
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)
Linqer生成了我在项目中具有的Linq代码；
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)
};
但是我无法弄清楚包装的T-SQL代码的Linq转换；
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;
Efn SQL Server Efn表具有以下字段；
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
在我的项目中，我有一个Efn实体类，如下所示；
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; }
}
这是我的dbcontext类
Here is my dbcontext class
公共类SerialNumberContext:DbContext {
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
推荐答案
用于将SQL转换为LINQ查询理解:
For translating SQL to LINQ query comprehension:
- 将子选择转换为单独声明的变量.
- 按LINQ子句顺序转换每个子句，将单子运算符和聚合运算符(
DISTINCT，TOP，MIN，MAX等)转换为应用于整个LINQ查询的函数. - 使用表别名作为范围变量.使用列别名作为匿名类型字段名称.
- 对多列使用匿名类型(
new {...}). -
LEFT JOIN是通过使用intojoinvariable 并从fromjoinvariable 接着是.DefaultIfEmpty()进行仿真来模拟的. - 用条件运算符(
?:)和null测试替换COALESCE. - 将
IN转换为.Contains()，将NOT IN转换为!...Contains(). - 将 x
BETWEENlowANDhigh 转换为 low<=x&&x<=high . -
SELECT *必须替换为select range_variable，或者对于联接来说，是包含所有范围变量的匿名对象. -
SELECT字段必须替换为select new {...}创建具有所有所需字段或表达式的匿名对象. - 正确的
FULL OUTER JOIN必须使用扩展方法来处理.
- Translate subselects as separately declared variables.
- Translate each clause in LINQ clause order, translating monadic and aggregate operators (
DISTINCT,TOP,MIN,MAXetc) into functions applied to the whole LINQ query. - Use table aliases as range variables. Use column aliases as anonymous type field names.
- Use anonymous types (
new {...}) for multiple columns. LEFT JOINis simulated by usingintojoinvariable and doing another fromfromthe joinvariable followed by.DefaultIfEmpty().- Replace
COALESCEwith the conditional operator (?:)and anulltest. - Translate
INto.Contains()andNOT INto!...Contains(). - Translate x
BETWEENlowANDhigh to low<=x&&x<=high. SELECT *must be replaced with select range_variable or for joins, an anonymous object containing all the range variables.SELECTfields must be replaced withselect new {...}creating an anonymous object with all the desired fields or expressions.- Proper
FULL OUTER JOINmust be handled with an extension method.
对于您的查询，您有3个基于3个SELECT的子查询，您可以从内到外进行翻译:
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();
这篇关于如何将复杂的T-SQL转换为Linq的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
