C# 控制台使用 EF Core，实现简单SQL Server数据库增、删、改、查、分页、关联

参考

• DeepSeek
• 豆包
• https://learn.microsoft.com/zh-cn/ef/core/miscellaneous/connection-strings?tabs=dotnet-core-cli
• https://www.cnblogs.com/clis/p/16278939.html
• https://learn.microsoft.com/zh-cn/ef/core/
• https://learn.microsoft.com/zh-cn/ef/core/saving/basic
• https://www.bilibili.com/video/BV1wP4y177tr

环境

软件/系统	版本	说明
Windows	windows 10 专业版 22H2 64 位操作系统, 基于 x64 的处理器
Microsoft Visual Studio	Community 2022 (64 位) - Current 版本 17.13.6
.NET SDK	6.0
EntityFrameworkCore	Microsoft.EntityFrameworkCore.SqlServer 7.0.20	项目依赖，7.x 版本兼容 .net 6.0
SQL Server Management Studio	20.2.37.0
Windows Server	Windows Server 2019 Standard Evaluation (10.0)	服务器
SQL Server	Microsoft SQL Server 2019 Enterprise Edition	服务器

正文

准备工作

1. 创建 C# 控制台项目，基于 .net 6.0 ,不使用顶级语句.
2. 控制台或者命令安装 Microsoft.EntityFrameworkCore.SqlServer 7.0.20.
3. 创建项目结构如下
   

代码

3. Program.cs

   using SchoolXiaQiuChu.Data;
   using SchoolXiaQiuChu.Models;
   
   namespace SchoolXiaQiuChu
   {
   	class Program
   	{
   		static async Task Main()
   		{
   			using (var service = new StudentService())
   			{
   				try
   				{
   					// 创建一个教室
   					ClassRoom? classRoom;
   					using (var context = new SchoolContext())
   					{
   						classRoom = context.ClassRooms.FirstOrDefault(item => item.Name == "一年级一班");
   						if (classRoom == null)
   						{
   							classRoom = new ClassRoom { Name = "一年级一班" };
   							context.ClassRooms.Add(classRoom);
   							await context.SaveChangesAsync();
   						}
   					}
   
   					// 创建学生
   					var student = await service.CreateStudent("张三", 12, classRoom.Id);
   					Console.WriteLine($"创建学生: {student.Name}，ID: {student.Id}");
   
   					// 获取学生
   					var retrievedStudent = await service.GetStudentById(student.Id);
   					Console.WriteLine($"获取学生: {retrievedStudent?.Name}，班级: {retrievedStudent?.ClassRoom.Name}");
   
   					// 更新学生
   					await service.UpdateStudent(student.Id, "李四", 13, classRoom.Id);
   					Console.WriteLine("学生信息已更新");
   
   					// 分页查询
   					var students = await service.GetStudents(1, 5);
   					Console.WriteLine($"第1页共有 {students.Count} 名学生");
   
   					// 按教室查询
   					var classStudents = await service.GetStudentsByClassRoom(classRoom.Id);
   					Console.WriteLine($"班级 {classRoom.Name} 共有 {classStudents.Count} 名学生");
   
   					// 删除学生
   					await service.DeleteStudent(student.Id);
   					Console.WriteLine("学生已删除");
   				}
   				catch (Exception ex)
   				{
   					Console.WriteLine($"操作出错: {ex.Message}");
   				}
   			}
   		}
   	}
   }
   

4. StudentService.cs

   using SchoolXiaQiuChu.Data;
   using SchoolXiaQiuChu.Models;
   using Microsoft.EntityFrameworkCore;
   
   namespace SchoolXiaQiuChu
   {
   	public class StudentService : IDisposable
   	{
   		private readonly SchoolContext _context;
   
   		public StudentService()
   		{
   			_context = new SchoolContext();
   		}
   
   		// 创建学生
   		public async Task<Student> CreateStudent(string name, byte age, int classRoomId)
   		{
   			var student = new Student
   			{
   				Name = name,
   				Age = age,
   				ClassRoomId = classRoomId
   			};
   
   			_context.Students.Add(student);
   			await _context.SaveChangesAsync();
   
   			return student;
   		}
   
   		// 获取单个学生
   		public async Task<Student?> GetStudentById(int id)
   		{
   			return await _context.Students
   				.Include(s => s.ClassRoom) // 关联查询
   				.FirstOrDefaultAsync(s => s.Id == id);
   		}
   
   		// 获取所有学生（分页）
   		public async Task<List<Student>> GetStudents(int page = 1, int pageSize = 10)
   		{
   			return await _context.Students
   				.Include(s => s.ClassRoom)
   				.Skip((page - 1) * pageSize)
   				.Take(pageSize)
   				.ToListAsync();
   		}
   
   		// 更新学生
   		public async Task UpdateStudent(int id, string name, byte age, int classRoomId)
   		{
   			var student = await _context.Students.FindAsync(id);
   
   			if (student == null)
   			{
   				throw new ArgumentException("学生不存在");
   			}
   
   			student.Name = name;
   			student.Age = age;
   			student.ClassRoomId = classRoomId;
   
   			await _context.SaveChangesAsync();
   		}
   
   		// 删除学生
   		public async Task DeleteStudent(int id)
   		{
   			var student = await _context.Students.FindAsync(id);
   
   			if (student == null)
   			{
   				throw new ArgumentException("学生不存在");
   			}
   
   			_context.Students.Remove(student);
   			await _context.SaveChangesAsync();
   		}
   
   		// 按教室查询学生
   		public async Task<List<Student>> GetStudentsByClassRoom(int classRoomId)
   		{
   			return await _context.Students
   				.Where(s => s.ClassRoomId == classRoomId)
   				.Include(s => s.ClassRoom)
   				.ToListAsync();
   		}
   
   		// 获取教室及其所有学生
   		public async Task<ClassRoom?> GetClassRoomWithStudents(int id)
   		{
   			return await _context.ClassRooms
   				.Include(c => c.Students)
   				.FirstOrDefaultAsync(c => c.Id == id);
   		}
   
   		public void Dispose()
   		{
   			_context?.Dispose();
   		}
   	}
   }
   

5. Data/SchoolContext.cs

   using Microsoft.EntityFrameworkCore;
   using SchoolXiaQiuChu.Models;
   
   namespace SchoolXiaQiuChu.Data
   {
   	public class SchoolContext : DbContext
   	{
   		public DbSet<Student> Students { get; set; }
   		public DbSet<ClassRoom> ClassRooms { get; set; }
   
   		protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
   		{
   			optionsBuilder.UseSqlServer(
   					@"Data Source=数据库服务器IP;Database=数据库名;User ID=数据库账号;Password=数据库密码;Connect Timeout=30;Encrypt=True;Trust Server Certificate=True;Application Intent=ReadWrite;Multi Subnet Failover=False");
   		}
   	}
   }
   

6. Models/ClassRoom.cs

   namespace SchoolXiaQiuChu.Models
   {
   	public class ClassRoom
   	{
   		public int Id { get; set; }
   		public string Name { get; set; }
   
   		// 反向导航属性
   		public virtual ICollection<Student> Students { get; set; }
   	}
   }
   

7. Models/Student.cs

   namespace SchoolXiaQiuChu.Models
   {
   	public class Student
   	{
   		public int Id { get; set; }
   		public string Name { get; set; }
   		public byte Age { get; set; }
   		public int ClassRoomId { get; set; }
   
   		// 导航属性
   		public virtual ClassRoom ClassRoom { get; set; }
   	}
   }
   

运行截图