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

我们使用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(50);
});

 

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

class Program
{
    static void Main(string[] args)
    {
        using (var dbContext = new DemoDBContext())
        {
            Person person = new Person()
            {
                Name = "Tom",
                Age = 20
            };

            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(50)
        .HasDefaultValueSql("(newid())");

    entity.Property(e => e.BookDescription).HasMaxLength(50);

    entity.Property(e => e.BookName).HasMaxLength(50);
});

 

然后我们在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

输出结果：

Book表的数据：