如何在linq中获取下一个自动增量ID [英] How to get the next auto-increment id in linq

问题描述

我需要下一个自动递增值...我正在使用Mysql数据库...我需要Linq查询示例



我尝试了什么：

  SELECT `AUTO_INCREMENT`  FROM  INFORMATION_SCHEMA.TABLES  WHERE  TABLE_SCHEMA = '  distributorempanel_new'  AND  TABLE_NAME = '  temp_rate_objective_new'）

...这个查询mysql工作正常....但我需要Linq查询....

解决方案

甚至不尝试。这是一个危险的游戏：MySQL是一个多用户环境，所以你不能以任何方式保证当你想要使用它时，你获取的下一个值可用，因为不同的用户也可能已经使用它。使用错误的唯一值的风险是可怕的：一旦数据库被摧毁就整理出来，就像这是一个非常讨厌和困难的手工作业。

只有在使用和分配ID后才能获取ID连续 - 从不试图提前获得它，它只会给你带来问题。

除了 OriginalGriff [ ^ ]，我建议您阅读以下内容：

c＃ - 带实体框架的自动编号 - 堆栈溢出 [ ^ ]

c＃ - 我可以在LINQ插入后返回'id'字段吗？ - 堆栈溢出 [ ^ ]

linq to sql - 如何在不使用MySql中的自动增量的情况下增加主键 - 堆栈溢出 [ ^ ]

< blockquote> / *开始存储过程* /

USE [MyDatabase]

GO



SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo]。[My_StoredProcedure]

@VariableNum [int] = NULL，

@VariableDate [datetime] = NULL，

...

...

@Variable_Return_Id [int]输出

WITH EXECU TE as CALLER

AS

开始

INSERT INTO

My_Table（Var_Num，

Var_Date，

...，

...



）价值（@VariableNum，

@VariableDate

....

....

）

SET @Variable_Return_Id = @@ IDENTITY

结束

/ *结束存储程序* /



注意：你必须在SQL Server数据表中创建此存储过程，并在My_StoredProcedure中添加Visual Studio项目Linq dbml with down down。

当您调用My_StoredProcedure时添加新记录并返回IDENTITY Number。



C＃示例：

int AddRec（）

{

int num = 1;

DateTime DateVal = DateTime.Now.Date;

int ResultId;

ResultId = My_StoredProcedure（num，DateVal）;

return（ResultId）;

}



VB示例：

函数AddRec（）as integer

Dim num As Integer = 1

Dim DateVal As DateTime = DateTime.Now.Date

Dim ResultId As integer

ResultId = My_StoredProcedure（num，DateVal）

AddRec = ResultId'或返回ResultId

结束函数

I need Next Auto Increment Value ... i am using Mysql Database... I need Linq query examples

What I have tried:

SELECT `AUTO_INCREMENT` FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'distributorempanel_new' AND   TABLE_NAME   = 'temp_rate_objective_new')

... this query mysql working fine.... but i need Linq query....

解决方案

Don't even try. That's a dangerous game: MySQL is a multiuser environment, so you cannot in any way guarantee that the "next value" you fetch will be available when you want to use it as a different user may have taken it as well. And the risks of using the wrong unique value are horrible: sorting out a database once it's been coirrupted like that is a very nasty and difficult manual job.
Only ever fetch an ID once it's been used and assigned to a row - never try to get it in advance, it will only ever give you problems.

In addition to solution 1 by OriginalGriff[^], i'd suggest to read these:
c# - Autonumber with Entity Framework - Stack Overflow[^]
c# - Can I return the 'id' field after a LINQ insert? - Stack Overflow[^]
linq to sql - How to increment primary key without using Auto Increment in MySql - Stack Overflow[^]

/* Begin Stored Procedure */
USE [MyDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[My_StoredProcedure]
@VariableNum [int] = NULL,
@VariableDate [datetime] = NULL,
...
...
@Variable_Return_Id [int] OUTPUT
WITH EXECUTE AS CALLER
AS
Begin
INSERT INTO
My_Table(Var_Num ,
Var_Date ,
... ,
...

) VALUES (@VariableNum ,
@VariableDate
....
....
)
SET @Variable_Return_Id = @@IDENTITY
End
/* End Stored Procedure */

Note : You must create this stored procedure in SQL Server data table and add Visual studio project Linq dbml in My_StoredProcedure with drop down.
When you call is My_StoredProcedure add a new record and Return IDENTITY Number.

C# Example:
int AddRec()
{
int num=1;
DateTime DateVal = DateTime.Now.Date;
int ResultId;
ResultId=My_StoredProcedure(num, DateVal);
return(ResultId);
}

VB Example:
Function AddRec() as integer
Dim num As Integer = 1
Dim DateVal As DateTime = DateTime.Now.Date
Dim ResultId As integer
ResultId=My_StoredProcedure(num, DateVal)
AddRec=ResultId ' or Return ResultId
End Function

这篇关于如何在linq中获取下一个自动增量ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！