安装EF Core

这里就不写了，太多人写过了

定义一个数据库对象

public class User
{
    [Key]//标记为主键
    public int id { get; set; }
    [Required]//确保值不为空
    [StringLength(50)]//限制长度不超过50
	/*
    [RegularExpression(正则表达式)]确保与正则表达式匹配
    [Column(TypeName = "name",Name="XXX")] 没搞清楚你们自己试试
    */
    public string? name { get;set; }
    public int? age { get;set; }
    public string? text { get;set; }
}

定义一个EF Core查询和操作对象

介绍一下EF Core大概的操作流程

• 首先通过C#代码生成SQL语句
• 将生成好的语句放到SQL数据库中查询
• 拿回数据

public class Context : DbContext
{
    public Context()
    {
		
	}
    public Context(DbContextOptions<Context> options):base(options)
    {
		
	}
    public DbSet<User> EF { get; set; } // 注意要和表名相同
	protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
	{
		string connString = @"server=127.0.0.1;user=sa;pwd=123;database=Test;TrustServerCertificate=true";//数据库连接字符串
        optionsBuilder.UseSqlServer(connString)
            .LogTo(Console.WriteLine, LogLevel.Information);// 这里是为了输出生成的SQL语句调试完请注释掉
	} 
}

添加数据

C#

using (Context con = new ())
{
    User addUser = new()
    {
        name = "Zengtudor",
        age=15,
        text="hello world"
    };
    con.EF.Add(addUser);
    con.SaveChanges();
}

自动转换的SQL Server 语句

SET IMPLICIT_TRANSACTIONS OFF;
      SET NOCOUNT ON;
      INSERT INTO [EF] ([age], [name], [text])
      OUTPUT INSERTED.[id]
      VALUES (@p0, @p1, @p2);

查询数据

C# 代码

using (Context con = new())
{
    List<User>? user = con.EF.Where(u => u.name != null&&u.name.Contains("e")).ToList();
    user.ForEach(e =>
    {
        Console.WriteLine($"{e.name} is {e.age},it says {e.text}");
    });
}

自动转换的SQL Server 语句

SELECT [e].[id], [e].[age], [e].[name], [e].[text]
      FROM [EF] AS [e]
      WHERE ([e].[name] IS NOT NULL) AND ([e].[name] LIKE N'%e%')

删除数据

主键查找

using (Context con = new())
{
    User? DUser = con.Find<User>(4); //查找主键为4的成员
    if(DUser is not null)
    {
        con.Remove(DUser);
        con.SaveChanges();//删除并保存
        Console.WriteLine("Remove OK");
    }
}

内容查找

C#

using (Context con = new())
{
    List<User> DUsers = con.EF.Where(e => e.name != null&&e.name.Contains("e")).ToList();
    if (DUsers.Any())
    {
        con.EF.RemoveRange(DUsers);

        Console.WriteLine(con.SaveChanges());
    }
}

输出日志中的SQL

info: 2023/8/11 14:25:46.311 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (16ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [e].[id], [e].[age], [e].[name], [e].[text]
      FROM [EF] AS [e]
      WHERE [e].[name] LIKE N'%e%'
info: 2023/8/11 14:25:46.437 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (16ms) [Parameters=[@p0='?' (DbType = Int32), @p1='?' (DbType = Int32), @p2='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      DELETE FROM [EF]
      OUTPUT 1
      WHERE [id] = @p0;
      DELETE FROM [EF]
      OUTPUT 1
      WHERE [id] = @p1;
      DELETE FROM [EF]
      OUTPUT 1
      WHERE [id] = @p2;