One literature will use Entity Framework Core

This article is based on the latest version 5.0 of EF Core at press time.

Article Directory

1. Basics

Quickly start
how to generate
the configuration value converter of the model entity relationship based on the entity class
(Value Conversion)

2. Principles

Change tracking principle
Data query principle
Data storage principle

3. Optimization

Performance optimization of logs, indicators and interceptors

4. Frequently Asked Questions

4.1 How to view the generated sql

Use LogTo

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Blogging;Integrated Security=True")
        .LogTo(Console.WriteLine, LogLevel.Information);
}

4.2 How to execute SQL directly

context.Database.ExecuteSqlRaw("UPDATE [Employees] SET [Salary] = [Salary] + 1000");

//防止sql注入方式四,直接构造DbParameter
var user = new SqlParameter("user", "johndoe");
var blogs = context.Blogs
    .FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser @user", user)
    .ToList();

For details, please refer to [Principles-Principles of Data Query]

4.3 How to perform transactions

using var context = new BloggingContext();
using var transaction = context.Database.BeginTransaction();
try
{
    context.Blogs.Add(new Blog { Url = "http://blogs.msdn.com/dotnet" });
    context.SaveChanges();

    context.Blogs.Add(new Blog { Url = "http://blogs.msdn.com/visualstudio" });
    context.SaveChanges();

    var blogs = context.Blogs
        .OrderBy(b => b.Url)
        .ToList();

    // Commit transaction if all commands succeed, transaction will auto-rollback
    // when disposed if either commands fails
    transaction.Commit();
}
catch (Exception)
{
    // TODO: Handle failure
}

Transaction failure will be automatically rolled back, no manual operation is required. For more usage of transactions, please refer to 【Principles-Principles of Data Storage】

4.4 How to use database locks

4.4.1 Optimistic Locking

A new attribute is added to the entity, marked as Timestamp:

[Timestamp]
public byte[] version{get;set;}

Then SaveChangeswrap it with try-catch DbUpdateConcurrencyException, and throw an exception when there is a concurrency conflict , and then handle it.

For details, please refer to [Principles-Principles of Data Storage Section 3.2]

4.4.2 Pessimistic lock

After the query is locked, other queries and modifications are prohibited, which is prone to deadlock and low performance. efcore does not support it, it can be achieved with the help of ADO.NET transactions:

static void Main(string[] args)
        {
           using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                using (var tx = conn.BeginTransaction())
                {
                    try
                    {
                        using (var selectCmd = conn.CreateCommand())
                        {
                            selectCmd.Transaction = tx;
                            //xlock:排它锁,ROWLOCK行锁
                            selectCmd.CommandText = "select * from T_Girls with(xlock,ROWLOCK) where id=1";
                            using (var reader = selectCmd.ExecuteReader())
                            {
                                if (!reader.Read())
                                {
                                    Console.WriteLine("没有id为1的女孩");
                                    return;
                                }
                                string bf = null;
                                if (!reader.IsDBNull(reader.GetOrdinal("BF")))
                                {
                                    bf = reader.GetString(reader.GetOrdinal("BF"));
                                }
                                if (!string.IsNullOrEmpty(bf))//已经有男朋友
                                {
                                    if (bf == myname)
                                    {
                                        Console.WriteLine("早已经是我的人了");
                                    }
                                    else
                                    {
                                        Console.WriteLine("早已经被" + bf + "抢走了");
                                    }
                                    Console.ReadKey();
                                    return;
                                }
                                //如果bf==null,则继续向下抢
                            }
                            Console.WriteLine("查询完成,开始update");
                            using (var updateCmd = conn.CreateCommand())
                            {
                                updateCmd.Transaction = tx;
                                updateCmd.CommandText = "Update T_Girls set [email protected] where id=1";
                                updateCmd.Parameters.Add(new SqlParameter("@bf", myname));
                                updateCmd.ExecuteNonQuery();
                            }
                            Console.WriteLine("结束Update");
                            Console.WriteLine("按任意键结束事务");
                            Console.ReadKey();
                        }
                        //事务提交之前你一直占有这行数据
                        tx.Commit();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex);
                        tx.Rollback();
                    }
                }
            }
            Console.ReadKey();
        }

4.5 Types of navigation properties

If it is a collection, it can be ICollection<T>or List<T>orHashSet<T>

4.6 Default naming rules for primary and foreign keys

  1. Primary key naming rules: in line with Id, ID, ClassnameId, ClassnameIDthe properties of these rules is automatically considered to be a primary key
  2. Foreign key rules: <导航属性名称><导航属性对应实体的主键属性名>or<导航属性对应实体的主键属性名>

4.7 How does EF Core know SaveChangeswhich content to submit to the database?

Through the Change trackingrealization. After the data is read from the database, efcore will create a snapshot of the data and compare it with the snapshot when calling save. See [Principles-Principles of Change Tracking] for details.

4.8 Prohibit automatic assignment to the primary key

[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int BlogId { get; set; }

5. Some "pits"

"Pit" is not necessarily a real pit, so quotation marks are added.

5.1 Tracking the problems caused by the query

EF uses tracking query by default, and records the entity instance information queried in the tracker. When the next query returns an entity that has been recorded in the tracker (the same primary key), the recorded entity will be returned directly as the query result (although the SQL will be executed again), and the value in the database will not be overwritten. There are physical values. So the following code may surprise you:

var blog = context.Blogs.Single(b => b.Name == "fish");
blog.Url = "aa";
var b = context.Blogs.Single(b => b.Name == "fish");
Console.WriteLine(b.Url);

At this time, the url of the blog is still aa, although the generated sql can be seen that there are obviously two queries:

info: 2021/6/2 15:40:01.856 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT TOP(2) [b].[Id], [b].[Name], [b].[Url]
      FROM [Blogs] AS [b]
      WHERE [b].[Name] = N'fish'
info: 2021/6/2 15:40:01.862 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT TOP(2) [b].[Id], [b].[Name], [b].[Url]
      FROM [Blogs] AS [b]
      WHERE [b].[Name] = N'fish'

An operation for where:

var orders = context.Orders.Where(o => o.Id > 1000).ToList();
var filtered = context.Customers.Include(c => c.Orders.Where(o => o.Id > 5000)).ToList();

The query result at this time: the data whose order id is greater than 1000, not the data greater than 5000. Because in the case of tracking query, the navigation properties in the filter will be considered to have been loaded.

Because of this problem, sometimes you will find that you have Includeloaded the data of the sub-entity even though you didn't call it.

The reason for the above problem is that efcore only maintains a state of the same entity. If the same entity is both modified and deleted, then there will be an error when saving (the principle of change tracking above is explained in detail). This problem can be solved by using non-tracking queries AsNoTracking.

var blog = context.Blogs.Single(b => b.Name == "fish");
blog.Url = "aa";
var b = context.Blogs.AsNoTracking().Single(b => b.Name == "fish");
Console.WriteLine(b.Url);