EF Core中,通过实体类向SQL Server数据库表中插入数据后,实体对象是如何得到数据库表中的默认值的

时间:2023-03-08 15:35:15

我们使用EF Core的实体类向SQL Server数据库表中插入数据后,如果数据库表中有自增列或默认值列,那么EF Core的实体对象也会返回插入到数据库表中的默认值。

下面我们通过例子来展示,EF Core是怎么获取数据库的默认值的。首先我们建立一个.NET Core控制台项目,然后引入EF Core所需的NuGet包。

获取自增列值


我们在SQL Server数据库中建立一个表Person,该表有一个自增列ID为主键:

CREATE TABLE [dbo].[Person](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Age] [int] NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

然后我们使用EF Core的DB First将该表映射为实体类Person,如下所示:

public partial class Person
{
public int Id { get; set; }
public string Name { get; set; }
public int? Age { get; set; }
}

实体类Person生成的Fluent API代码如下所示:

modelBuilder.Entity<Person>(entity =>
{
entity.Property(e => e.Id).HasColumnName("ID"); entity.Property(e => e.Name).HasMaxLength();
});

然后我们在Program类的Main方法中,通过实体类Person向数据库Person表中插入一行数据:

class Program
{
static void Main(string[] args)
{
using (var dbContext = new DemoDBContext())
{
Person person = new Person()
{
Name = "Tom",
Age =
}; dbContext.Person.Add(person);
dbContext.SaveChanges(); Console.WriteLine(person.Id);//输出:1
} Console.WriteLine("Press any key to end...");
Console.ReadKey();
}
}

我们在dbContext.SaveChanges()这行代码执行后,通过EF Core的后台日志可以查看到生成的SQL语句如下:

=============================== EF Core log started ===============================
Executed DbCommand (95ms) [Parameters=[@p0='?' (DbType = Int32), @p1='?' (Size = 50)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
INSERT INTO [Person] ([Age], [Name])
VALUES (@p0, @p1);
SELECT [ID]
FROM [Person]
WHERE @@ROWCOUNT = 1 AND [ID] = scope_identity();
=============================== EF Core log finished ===============================

可以看到EF Core最后使用了scope_identity()函数,来返回了刚刚插入的自增列ID的值。

获取默认值列


我们在SQL Server数据库中再建立一个表Book,该表有一个默认值列BookCode为主键,其默认值为函数newid():

CREATE TABLE [dbo].[Book](
[BookCode] [nvarchar](50) NOT NULL,
[BookName] [nvarchar](50) NULL,
[BookDescription] [nvarchar](50) NULL,
CONSTRAINT [PK_Book] PRIMARY KEY CLUSTERED
(
[BookCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO ALTER TABLE [dbo].[Book] ADD CONSTRAINT [DF_Book_BookCode] DEFAULT (newid()) FOR [BookCode]
GO

然后我们使用EF Core的DB First将该表映射为实体类Book,如下所示:

public partial class Book
{
public string BookCode { get; set; }
public string BookName { get; set; }
public string BookDescription { get; set; }
}

实体类Book生成的Fluent API代码如下所示:

modelBuilder.Entity<Book>(entity =>
{
entity.HasKey(e => e.BookCode); entity.Property(e => e.BookCode)
.HasMaxLength()
.HasDefaultValueSql("(newid())"); entity.Property(e => e.BookDescription).HasMaxLength(); entity.Property(e => e.BookName).HasMaxLength();
});

然后我们在Program类的Main方法中,通过实体类Book向数据库Book表中插入一行数据:

class Program
{
static void Main(string[] args)
{
using (var dbContext = new DemoDBContext())
{
Book book = new Book()
{
BookDescription = "English book",
BookName = "English"
}; dbContext.Add(book);
dbContext.SaveChanges(); Console.WriteLine(book.BookCode);//输出:B5AD3E33-3730-4545-8D45-9BD6DFCD5063
} Console.WriteLine("Press any key to end...");
Console.ReadKey();
}
}

我们在dbContext.SaveChanges()这行代码执行后,通过EF Core的后台日志可以查看到生成的SQL语句如下:

=============================== EF Core log started ===============================
Executed DbCommand (116ms) [Parameters=[@p0='?' (Size = 50), @p1='?' (Size = 50)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
DECLARE @inserted0 TABLE ([BookCode] nvarchar(50), [_Position] [int]);
MERGE [Book] USING (
VALUES (@p0, @p1, 0)) AS i ([BookDescription], [BookName], _Position) ON 1=0
WHEN NOT MATCHED THEN
INSERT ([BookDescription], [BookName])
VALUES (i.[BookDescription], i.[BookName])
OUTPUT INSERTED.[BookCode], i._Position
INTO @inserted0; SELECT [t].[BookCode] FROM [Book] t
INNER JOIN @inserted0 i ON ([t].[BookCode] = [i].[BookCode])
ORDER BY [i].[_Position];
=============================== EF Core log finished ===============================

我们发现EF Core实际上是用SQL Server的Merge语句往数据库Book表中插入了数据,并且使用了Merge语句的OUTPUT关键字,来返回Book表中列BookCode插入的默认值。

我们可以自己写个Merge语句来实验下获取Book表中列BookCode插入的默认值:

DECLARE @R TABLE (BookCode NVARCHAR(50))

MERGE INTO [dbo].[Book]
USING (SELECT 1 AS [Number]) AS S ON 1=0
WHEN NOT MATCHED
THEN INSERT([BookName],[BookDescription]) VALUES(N'Chinese',N'Chinese Book')
OUTPUT
INSERTED.BookCode
INTO @R; SELECT * FROM @R

输出结果:

EF Core中,通过实体类向SQL Server数据库表中插入数据后,实体对象是如何得到数据库表中的默认值的

Book表的数据:

EF Core中,通过实体类向SQL Server数据库表中插入数据后,实体对象是如何得到数据库表中的默认值的