一、数据结构关系
---关系行数据库描述归类
---一对一
---【业务场景】学生的信息表-》学生的成绩表
create table Stu_Info(
StuID int identity(1,1),
StuName nvarchar(30) not null,
CreateTime datetime null,
primary key(StuID)
);
go
create table Stu_Score(
StuID int not null,
English decimal(18,2) null,
Chinese decimal(18,2) null,
primary key(StuID),
foreign key(StuID) references Stu_Info(StuID)
);
go
二、EF Core上下文对应
1.学生资料
/// <summary>
/// 学生信息
/// </summary>
internal class Stu_Info
{
[Key]
public int StuID { get; set; }
public string StuName { get; set; }
public DateTime? CreateTime { get; set; }
/// <summary>
/// 外键响应,一对一
/// </summary>
public virtual Stu_Score Stu_Score { get; set; }
}
2.学生成绩
/// <summary>
/// 学生的成绩记录
/// </summary>
internal class Stu_Score
{
[Key]
public int StuID { get; set; }
public decimal? English { get; set; }
public decimal? Chinese { get; set; }
/// <summary>
/// 外键锁定
/// </summary>
[ForeignKey("StuID")]
public virtual Stu_Info Stu_Info { get; set; }
}
3.数据库上下文
internal class StudentContext:DbContext
{
/// <summary>
/// 链接字符串
/// </summary>
public static string SqlStr { get; set; } = "data source=.;initial catalog=Student;Trusted_Connection=True;";
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
optionsBuilder.UseSqlServer(SqlStr);
}
base.OnConfiguring(optionsBuilder);
}
public virtual DbSet<Stu_Info> Stu_Info { get; set; }
public virtual DbSet<Stu_Score> Stu_Score { get; set; }
}
三、关系操作和筛选使用
1.添加学生信息和学生的成绩
static void Test1()
{
StudentContext _context = new StudentContext();
//添加学生信息
Stu_Info stu = new Stu_Info()
{
StuName = "李四",
CreateTime = DateTime.Now
};
//添加成绩信息
Stu_Score score = new Stu_Score()
{
StuID = stu.StuID,
Chinese = 100,
English = 50
};
stu.Stu_Score = score;
_context.Stu_Info.Add(stu);//保存到数据库
_context.SaveChanges();
Console.WriteLine(_context.Stu_Info.Count());
}
2.查询学生+成绩;过滤掉,没有成绩记录的学生
static void Test2()
{
StudentContext _context = new StudentContext();
var list = _context.Stu_Info.Include(q => q.Stu_Score)
.Where(q=>q.Stu_Score!=null) //条件筛选,过滤掉不存在关联表的数据
.ToList();
foreach (var item in list)
{
Console.WriteLine($"{item.StuName},英语成绩:{item.Stu_Score?.English},中文成绩:{item.Stu_Score?.Chinese}");
}
}
如果不过滤,则显示所有学生:
更多: