1. Connect to the database

To configure the application through DEPENDENCY injection, add EFCore to the DEPENDENCY injection container through AddDbContext in the ConfigureService method of the Startup class

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllers();

    services.AddDbContext<OpenDbContext>(
        options => options.UseMySql(Configuration["DbConfig:Mysql:ConnectionString"]);
}
Copy the code

Register a DbContext subclass named OpenDbContext into the Scope lifecycle of the DEPENDENCY injection container. The context is configured to use the MySQL database provider and reads the database connection string from the configuration.

The OpenDbContext class must expose a public constructor with the DbContextOptions parameter. This is how the context configuration of AddDbContext is passed to DbContext.

public class OpenDbContext : DbContext { public OpenDbContext(DbContextOptions options) : base(options) { } public DbSet<User> Users { get; set; } public DbSet<Score> Scores { get; set; } override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {// Another way to configure a database connection / / optionsBuilder. UseMySql (" connect to the database ", ServerVersion AutoDetect (" database connection string ")); } protected override void OnModelCreating(ModelBuilder ModelBuilder) {// ModelBuilder.Entity<User>().property (t) => t.account).isrequired ().hasmaxLength (20).hasComment (" account "); //modelBuilder.Entity<User>().hasdata (new User {Account=" seed "}); / / use the following method to replace the above batch increase etc operating modelBuilder. ApplyConfigurationsFromAssembly (Assembly) GetExecutingAssembly ()); base.OnModelCreating(modelBuilder); }}Copy the code

OpenDbContext is then injected into the application’s controller or other services by means of constructor injection.

There is another article about connecting to a database:.net database Generation Process

2. Operate the database

context.Database.EnsureDeleted(); / / remove the Database, if there is, without permission, the context will cause abnormal. Database. EnsureCreated (); // If the Database does not exist, create the Database and initialize the schema. If there are any tables, the schema will not be initialized context.database.migrate (); // Migrate the database according to the migration fileCopy the code

3. Query operations

3.1 Basic Query

db.Set<UserInfor>().ToList(); Var account = (from u in _context.Users where u.id == id select u.account).tolist (); / / query a single _context. Movie. FirstOrDefaultAsync (m = > m.I D = = id); _context.Movie.FindAsync(id); Set<User>().asnotracking ().Where(t=> t.id =="11").Select(t => new {t.a.count, t.PassWord }).FirstOrDefaultAsync(); Var blogs = context.blogs.Include(blog => blog.posts).tolist (); Var blogs = context.blogs.Include(blog => blog.posts).theninclude (post => post.author).tolist ();Copy the code

3.2 Trace and Untrace queries

Tracking behavior determines whether EFCore stores information about some entities in its change tracker. If an entity is tracked, any changes detected in that entity are saved to the database at SaveChanges(),

Entity types that do not have primary keys are not tracked.

# trace query _context.set <User>().tolistAsync (); # untrace query _context.set <User>().asnoTracking ().tolistAsync ();Copy the code

The default is a trace query

3.3 Query by Condition

3.3.1 Asynchronous Solution Is not supported

Func<User, bool> express = x => true; if (! string.IsNullOrWhiteSpace(dto.Data)) { express = x => x.Mobile == dto.Data; } string userid = ""; if (! string.IsNullOrWhiteSpace(userid)) { express = x => x.UserId == userid; } var bbb = _dbContext.Set<User>().Where(express).FirstOrDefault();Copy the code

3.3.2 Asynchronous Solution

Expression<Func<User, bool>> express = x => true; if (! string.IsNullOrWhiteSpace(dto.Data)) { express = x => x.Mobile == dto.Data; } var bbb = await _dbContext.Set<User>().Where(express).ToListAsync();Copy the code

3.4 Native SQL Query

You can use the FromSqlRaw extension method to start a LINQ query based on the original SQL query. FromSqlRaw can only be used on query roots located directly on DbSet<>.

3.4.1 Basic Native SQL Query

var blogs = context.Blogs .FromSqlRaw("select * from user") .ToList(); // EXECUTE the stored procedure var blogs = context.blogs.FromSqlRaw("EXECUTE dbo.getmostPopularBlogs ").tolist ();Copy the code

3.4.2 Parameterized Query

3.4.2.1 SQL injection

First, let’s write a simple EXAMPLE of SQL injection. For example, to inject the statement we query by ID, enter ID: idiDID ‘or ‘1’=’1

    var strSql = string.Format("select * from user where Id='{0}'", "ididid' or '1'='1");
    var query = await _context.Set<User>().FromSqlRaw(strSql).ToListAsync();
    Console.WriteLine(JsonConvert.SerializeObject(query));
Copy the code

Generate statements

select * from user where Id='ididid' or '1'='1' [{" Account ":" zhang ", "PassWord" : "123456", "CreateTime" : "the 2021-05-20 T22:53:44. 778101", "IsValid" : false, "Id" : "1395392302788120576 "}, {" Account ":" bill ", "PassWord" : "123456", "CreateTime" : "the 2021-05-20 T22:53:44. 849376", "IsValid" : false, "Id" : "13953923030901104 64 "}, {" Account ":" detective "and" PassWord ":" 123456 ", "CreateTime" : "the 2021-05-20 T22:53:44. 849425", "IsValid" : false, "Id" : "139539230309011 0467 "}]Copy the code

3.4.2.2 Parameterizing FromSqlRaw

Prevent SQL injection problems by parameterizing queries

Var strSql = "select * from user where Id=@id"; var parameter = new MySqlParameter[] { new MySqlParameter("@id","1395392302788120576"), }; var query = await _context.Set<User>().FromSqlRaw(strSql, parameter).ToListAsync();Copy the code

or

var strSql = "select * from user where Id={0}"; var query = await _context.Set<User>().FromSqlRaw(strSql, "1395392302788120576").ToListAsync(); Console.WriteLine(JsonConvert.SerializeObject(query)); Select * from user where Id=@p0 [{" Account ":" zhang ", "PassWord" : "123456", "CreateTime" : "the 2021-05-20 T22:53:44. 778101", "IsValid" : false, "Id" : "1395392302788120576 "}]Copy the code

Additional arguments are provided in placeholder form, which looks similar to string.format syntax, but the supplied values are wrapped in DbParameter. SQL injection can be prevented

3.4.2.3 FromSqlInterpolated parameterization

FromSqlInterpolated is similar to FromSqlRaw, but you can use string interpolating syntax with it. Like FromSqlRaw, FromSqlInterpolated can only be used at the query root, and both can prevent SQL injection.

    var query = await _context.Set<User>().FromSqlInterpolated($"select * from user where Id={"1395392302788120576"}").ToListAsync();
    Console.WriteLine(JsonConvert.SerializeObject(query));
Copy the code

Generate SQL

select * from user where Id=@p0 [{" Account ":" zhang ", "PassWord" : "123456", "CreateTime" : "the 2021-05-20 T22:53:44. 778101", "IsValid" : false, "Id" : "1395392302788120576 "}]Copy the code

Rule 3.4.3 limit

• The SQL query must return data for all attributes of the entity type.
• The columns in the result set must match the column name to which the attribute is mapped.
• SQL queries cannot contain associated data, but in many cases you can use it right after the queryIncludeMethod to return associated data (seeContaining associated data).

Reference: docs.microsoft.com/zh-cn/ef/co…

3.5 Complex Query

The data are as follows:

The users table (user)

User Score (SCORE)

Description: Contains three users, two of whom have both language and math data on the score sheet.

3.5.1 track of inside connection

Inner join: divided into implicit inner join and explicit inner join (different writing method, the same result)

3.5.1.1 Linq Query Expressions

Explicit inner join: join-in-on concatenation

Var list = (from u in _context.Users join sc in _context.Scores on u.id equals sc.UserId where sc.CourseName ==" select new { u.Account, u.PassWord, sc.CourseName, sc.Grade }).ToList(); Console.WriteLine(JsonConvert.SerializeObject(list));Copy the code

Remember to reference system. Linq otherwise prompt: no implementation of query mode for source type “DbSet” was found, no join was found

Generate SQL

SELECT `u`.`Account`, `u`.`PassWord`, `s`.`CourseName`, 'Grade' FROM 'user' AS 'u' INNER JOIN 'score' AS 's' ON 'u'. 'Id' = 's'. 'UserId' WHERE 's'.Copy the code

The results of

Implicit internal join: multiple FROM parallel splicing

Var list = (from u in _context.Users from sc in _context.Scores where u.id == sc.userid && sc.coursename == "select" new { u.Account, u.PassWord, sc.CourseName, sc.Grade }).ToList(); Console.WriteLine(JsonConvert.SerializeObject(list));Copy the code

Generate SQL

SELECT `u`.`Account`, `u`.`PassWord`, `s`.`CourseName`, ` s `. ` Grade ` FROM ` user ` AS ` u ` CROSS JOIN ` score ` AS ` s ` WHERE (` u `. ` ` Id = ` s `. ` UserId `) AND (` s `. ` CourseName ` = 'language')Copy the code

The results of

3.5.1.2 Standard Linq query operators

var list = _context.Users.Where(t => t.Account ! Join(_context.scores.Where(sc => sc.coursename == ""), u => u.id, sc => sc.userid, (u, sc) => new { u.Account, u.PassWord, sc.CourseName, sc.Grade }).ToList(); Console.WriteLine(JsonConvert.SerializeObject(list));Copy the code

Generate SQL

SELECT 'u'. 'Account', 'u'. 'PassWord', 's'. 'CourseName', 's'. 'Grade' FROM 'user' AS 'u' INNER JOIN 'score' AS 's' ON 'u'. 'Id' = 's'. `u`.`PassWord`, `t`.`CourseName`, `t`.`Grade` FROM `user` AS `u` INNER JOIN ( SELECT `s`.`CourseName`, `s`.`Grade`, ` s `. ` UserId ` FROM ` score ` AS ` s ` WHERE ` s `. ` CourseName ` = 'language') AS ` t ` ON ` u `. ` ` Id = ` t ` ` UserId `Copy the code

The results of

3.5.2 outer join

An outer join must be followed by an INTO, which can be followed by xx.defaultifEmpty () to return null for reference types and 0 for value types. For struct types, they are initialized to null(reference type) or 0(value type) depending on their member type,

If you only need to count the number of the right table or other attributes, you can omit xx. DefaultIfEmpty. However, if you need to click the fields of the right table, you cannot omit XX.

3.5.2.1 linq implementation

Query the class corresponding to all users. Because users and scores are one-to-many, multiple data will appear

var list = (from u in _context.Users join sc in _context.Scores on u.Id equals sc.UserId into ulist from sco in ulist.DefaultIfEmpty() where u.Account ! Select new {UserId = u.id, Account = u.account, sco.coursename}).tolist (); Console.WriteLine(JsonConvert.SerializeObject(list));Copy the code

Generate SQL

      SELECT `u`.`Id` AS `UserId`, `u`.`Account`, `s`.`CourseName`
      FROM `user` AS `u`
      LEFT JOIN `score` AS `s` ON `u`.`Id` = `s`.`UserId`
Copy the code

The results of

Nullable object must have a value. Nullable object must have a value

3.5.3 GroupJoin

The GroupJoin operator is often used to return queries in the form of “primary key object – set of foreign key objects”, such as “user information – grades of all subjects under this user”.

var list = _context.Users.Where(t => t.Account ! = null) .GroupJoin(_context.Scores, u => u.Id, sc => sc.UserId, (u, sc) => new { u.Account, u.PassWord, Scores = sc }).ToList(); Console.WriteLine(JsonConvert.SerializeObject(list));Copy the code

This code will prompt an error, for example: docs.microsoft.com/zh-cn/ef/co…

3.5.4 GrouBy

Group operation according to the user group, to find the number of subjects

    var list = (from sc in _context.Scores
                group sc by sc.UserId
                into g
                select new
                {
                    g.Key,
                    Count = g.Count()
                }).ToList();
    Console.WriteLine(JsonConvert.SerializeObject(list));

    var list2 = _context.Scores.GroupBy(sc => sc.UserId).Select(t => new
    {
        t.Key,
        Count = t.Count()
    }).ToList();
    Console.WriteLine(JsonConvert.SerializeObject(list2));
Copy the code

Generate SQL

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `s`.`UserId` AS `Key`, COUNT(*) AS `Count`
      FROM `score` AS `s`
      GROUP BY `s`.`UserId`
[{"Key":"1395392302788120576","Count":2},{"Key":"1395392303090110464","Count":2}]
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `s`.`UserId` AS `Key`, COUNT(*) AS `Count`
      FROM `score` AS `s`
      GROUP BY `s`.`UserId`
[{"Key":"1395392302788120576","Count":2},{"Key":"1395392303090110464","Count":2}]
Copy the code

4. Add

4.1 Basic Addition

    _context.Movie.Add(movie);
    // or
    await _context.Movie.AddRangeAsync(movies)
    await _context.SaveChangesAsync();
Copy the code

4.2 Insert with auto-increment key Already set

Turn off autoincrement first and then turn on autoincrement after inserting data

db.Database.OpenConnection(); db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [T_RoleInfor] ON"); Var r2 = new T_RoleInfor() {id = 123, roleName = "admin ", roleDescription =" admin "}; db.Add(r2); int count2 = db.SaveChanges(); db.Database.ExecuteSqlCommand("SET ID ENTITY_INSERT [T_RoleInfor] OFF");Copy the code

4.3 Adding using SQL

var strSql2 = "INSERT INTO `userinfo`(`Id`, `Account`, `PassWord`) VALUES (@id, @account, @password);" ; var parameter2 = new MySqlParameter[] { new MySqlParameter("@id","22"), new MySqlParameter("@account","2222"), new MySqlParameter("@password","22222") }; var flg = db.Database.ExecuteSqlRaw(strSql2, parameter2);Copy the code

5. Modify

var movie = await _context.Movie.FirstOrDefaultAsync(m => m.ID == id); Movie. The Name = "lee thought"; await _context.SaveChangesAsync();Copy the code

6. Delete

    var movie = await _context.Movie.FirstOrDefaultAsync(m => m.ID == id);
    _context.Movie.Remove(movie);
    await _context.SaveChangesAsync();
Copy the code

7. Refer to the documents

Official example: docs.microsoft.com/zh-cn/ef/co…

Wechat official Account [Peng Xiang]