CSharp: Relational mapping in donet using Entity Framework
sql script
--dbo.Students
--Id int identity
--Name
--Age byte
--CreatedTime date
-- ModifiedTime date
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].Students') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE Students
GO
create table Students
(
Id INT IDENTITY(1,1) PRIMARY KEY,
[Name] nvarchar(50) not null,
Age tinyint null,
CreatedTime date null,
ModifiedTime date null
)
go
insert into Students([Name],Age,CreatedTime,ModifiedTime) values('sibodu',12,getdate(),getdate())
go
select * from Students
go
--StudentContact ContactNumber
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].StudentContact') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE StudentContact
GO
create table StudentContact
(
Id INT IDENTITY(1,1) PRIMARY KEY,
StudentId int
FOREIGN KEY REFERENCES Students(Id),
ContactNumber nvarchar(50) null
)
go
-- dbo.Courses
--Id int identity
--Name
--MaximumStrength int
--CreatedTime
--ModifiedTime
--
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].Courses') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE Courses
GO
create table Courses
(
Id INT IDENTITY(1,1) PRIMARY KEY,
[Name] nvarchar(50) not null,
MaximumStrength int null,
CreatedTime date null,
ModifiedTime date null
)
go
insert into Courses([Name],MaximumStrength,CreatedTime,ModifiedTime) values('语文',25,getdate(),getdate())
go
select * from Courses
go
-- DropForeignKey("dbo.Orders", "CustomerId", "dbo.Customers");
-- DropForeignKey("dbo.Products", "CategoryId", "dbo.Categories");
--SutdentCourcese
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].SutdentCourcese') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE SutdentCourcese
GO
create table SutdentCourcese
(
Id INT IDENTITY(1,1) PRIMARY KEY,
StudentId int
FOREIGN KEY REFERENCES Students(Id),
CourseId int
FOREIGN KEY REFERENCES Courses(Id)
)
go
select * from SutdentCourcese
go
using EntityFramework6.Enity;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity.Core.Metadata;
using System;
using System.ComponentModel.DataAnnotations.Schema;
//using Microsoft.EntityFrameworkCore.Metadata;
namespace EntityFramework6.Entity
{
/// <summary>
/// 学生
/// </summary>
[Table("Student")]
public class Student : BaseEntity
{
[Display(Name = "姓名")]
public string Name { get; set; }
[Display(Name = "年龄")]
public byte Age { get; set; }
/// <summary>
///
/// </summary>
public DateTime CreatedTime { get; set; }
/// <summary>
///
/// </summary>
public DateTime ModifiedTime { get; set; }
/// <summary>
///
/// </summary>
[Display(Name = "联系方式")]
public virtual StudentContact Contact { get; set; }
/// <summary>
/// 课程表
/// </summary>
[Display(Name = "课程表")]
public virtual ICollection<SutdentCourcese> SutdentCourcese { get; set; }
}
}
using EntityFramework6.Enity;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity.Core.Metadata;
using System.ComponentModel.DataAnnotations.Schema;
namespace EntityFramework6.Entity
{
/// <summary>
/// 課程
/// </summary>
[Table("Courses")]
public class Course : BaseEntity
{
/// <summary>
/// 課程名称
/// </summary>
[Display(Name = "課程名称")]
public string Name { get; set; }
/// <summary>
/// 課時時長
/// </summary>
[Display(Name = "課時時長")]
public int MaximumStrength { get; set; }
/// <summary>
/// 课程
/// </summary>
[Display(Name = "课程表")]
public virtual ICollection<SutdentCourcese> SutdentCourcese { get; set; }
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using EntityFramework6.Enity;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity.Core.Metadata;
using System.ComponentModel.DataAnnotations.Schema;
namespace EntityFramework6.Entity
{
/// <summary>
/// 学生课程表
/// </summary>
[Table("SutdentCourcese")]
public class SutdentCourcese
{
/// <summary>
///
/// </summary>
[Display(Name = "ID")]
public int Id { get; set; }
/// <summary>
/// 学生ID
/// </summary>
[Display(Name = "学生ID")]
[ForeignKey("Student")]
public int StudentId { get; set; }
/// <summary>
/// 学生ID
/// </summary>
[Display(Name = "课程ID")]
[ForeignKey("Course")]
public int CourseId { get; set; }
/// <summary>
/// 学生
/// </summary>
[Display(Name = "学生")]
public virtual Student Student { get; set; }
/// <summary>
/// 课程
/// </summary>
[Display(Name = "课程")]
public virtual Course Course { get; set; }
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using EntityFramework6.Enity;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity.Core.Metadata;
using System.ComponentModel.DataAnnotations.Schema;
//using Microsoft.EntityFrameworkCore.Metadata;
namespace EntityFramework6.Entity
{
/// <summary>
/// 学生联系表
/// </summary>
[Table("StudentContact")]
public class StudentContact
{
/// <summary>
///
/// </summary>
[Display(Name = "ID")]
public int Id { get; set; }
/// <summary>
/// 联系电话
/// </summary>
[Display(Name = "联系电话")]
public string ContactNumber { get; set; }
/// <summary>
/// 学生ID
/// </summary>
[Display(Name = "学生ID")]
[ForeignKey("Student")]
public int StudentId { get; set; }
/// <summary>
/// 学生
/// </summary>
[Display(Name = "学生")]
public virtual Student Student {get;set;}
}
}
Mapping
using EntityFramework6.Entity;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.ModelConfiguration;
//https://entityframework.net/one-to-one-relationship
//https://www.entityframeworktutorial.net/code-first/foreignkey-dataannotations-attribute-in-code-first.aspx
//https://github.com/dotnet/ef6
//https://docs.microsoft.com/en-us/ef/ef6/
namespace EntityFramework6.Map
{
/// <summary>
///
/// </summary>
public class StudentMap : EntityTypeConfiguration<Student>
{
/// <summary>
///
/// </summary>
public StudentMap()
{
//table
ToTable("Students");
//key
HasKey(t => t.Id);
//fields
Property(x => x.Id)
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
Property(x => x.Name).HasColumnType("VARCHAR").HasMaxLength(50);
Property(x => x.Age);
Property(x => x.CreatedTime);
Property(x => x.ModifiedTime);
//relationship Many-to-Many Relationishhip 多对多 课程表
//HasMany(t => t.SutdentCourcese).WithMany(c => c.Student)
// .Map(t => t.ToTable("SutdentCourcese")
// .MapLeftKey("StudentId")
// .MapRightKey("CourseId"));
//多对一
HasMany(t => t.SutdentCourcese)
.WithRequired(w => w.Student)
.HasForeignKey(o => o.StudentId);
//realationship One-to-One 一对一 联系表
HasOptional(x => x.Contact)
.WithOptionalDependent(l => l.Student);
// HasRequired(x => x.Contact).WithOptional(l => l.Student);
}
}
}
using EntityFramework6.Entity;
using System.Data.Entity.ModelConfiguration;
using System.ComponentModel.DataAnnotations.Schema;
namespace EntityFramework6.Map
{
/// <summary>
/// geovindu
/// </summary>
public class CourseMap : EntityTypeConfiguration<Course>
{
public CourseMap()
{
//table
ToTable("Courses");
//key
HasKey(k => k.Id);
//property
Property(t => t.Id).HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity);
Property(t => t.Name).HasColumnType("VARCHAR").HasMaxLength(50);
Property(t => t.MaximumStrength);
Property(t => t.CreatedTime);
Property(t => t.ModifiedTime);
//HasMany(t => t.SutdentCourcese).WithMany(c => c.Course)
// .Map(t => t.ToTable("SutdentCourcese")
// .MapLeftKey("CourseId")
// .MapRightKey("StudentId"));
//
//HasMany(c => c.SutdentCourcese).WithMany(l => l.Course)
// .Map(t => t.ToTable("SutdentCourcese")
// .MapLeftKey("CourseId"));
//多对多
//HasMany(c => c.Students).WithMany(l => l.Courses);
//HasMany(c => c.SutdentCourcese).WithMany(l => l.Course);
//多对一
HasMany(c => c.SutdentCourcese).WithRequired(l => l.Course)
.HasForeignKey(k=>k.CourseId);
// HasMany(c => c.Students).WithRequired(l => l.);
}
}
}
using EntityFramework6.Entity;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.ModelConfiguration;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace EntityFramework6.Map
{
/// <summary>
///
/// </summary>
public class SutdentCourceseMap : EntityTypeConfiguration<SutdentCourcese>
{
/// <summary>
///
/// </summary>
public SutdentCourceseMap()
{
//table
ToTable("SutdentCourcese");
//key
HasKey(t => t.Id);
//fields
Property(x => x.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
Property(x => x.StudentId)
.HasColumnName("StudentId")
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
Property(x => x.CourseId)
.HasColumnName("CourseId")
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
//relationship one-to-one 一对多
HasRequired(c => c.Course).WithMany(l => l.SutdentCourcese); //Contact SutdentCourcese
HasRequired(s => s.Student).WithMany(l => l.SutdentCourcese);
//HasMany 多对多
//HasMany(c => c.Course).WithMany(l => l.SutdentCourcese);
//HasMany(s => s.Student).WithMany(l => l.SutdentCourcese);
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.ModelConfiguration;
using EntityFramework6.Entity;
//https://www.entityframeworktutorial.net/code-first/foreignkey-dataannotations-attribute-in-code-first.aspx
namespace EntityFramework6.Map
{
/// <summary>
///
/// </summary>
public class StudentContactMap:EntityTypeConfiguration<StudentContact>
{
public StudentContactMap()
{
//table
ToTable("StudentContact");
//key
HasKey(t => t.Id);
//fields
Property(x => x.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
Property(x=>x.StudentId)
.HasColumnName("StudentId")
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
Property(x => x.ContactNumber);
//relationship one-to-one 一对一
//HasRequired(s => s.Student).WithOptional(l => l.Contact); //Student in class Contact
HasOptional(s=>s.Student).WithOptionalPrincipal(l=>l.Contact);
}
}
}
调用：
//查询集合
var customers = ctx.Customers;
//var names = customers
// .Select(x => x.Name)
// //.Take(10)
// .OrderBy(d=>1)
// .Skip(5)
// .ToList();
var names = customers
.Select(x => x.Name)
.Take(10)
.OrderBy(d => 1)
//.Skip(5)
.ToList();
foreach (var name in names)
{
Console.WriteLine(name + "\n\t");
}
//原始查询
var cus = ctx.Database.SqlQuery<Customer>("SELECT * FROM dbo.Customers").ToList();
foreach (var cs in cus)
{
Console.WriteLine("name:"+cs.Name + "\n\t");
}
var id = 1;
var par = new SqlParameter[]
{
new SqlParameter(){ ParameterName="@id",SqlDbType=SqlDbType.Int,Value=id },
};
var cust = ctx.Database.SqlQuery<Customer>("SELECT * FROM dbo.Customers where id=@id", par).ToList();
foreach (var ducs in cust)
{
Console.WriteLine("姓名:" + ducs.Name + "\n\t");
}
输出：
SELECT
[Project1].[Name] AS [Name]
FROM ( SELECT TOP (10)
1 AS [C1],
[c].[Name] AS [Name]
FROM [dbo].[Customers] AS [c]
) AS [Project1]
ORDER BY [Project1].[C1] ASC
-- Executing at 2023/1/29 11:48:02 +08:00
-- Completed in 178 ms with result: SqlDataReader
涂年生
涂聚文
test
geovindu
江城
保中
王二
李三
张四
赵五
SELECT * FROM dbo.Customers
-- Executing at 2023/1/29 11:48:03 +08:00
-- Completed in 10 ms with result: SqlDataReader
name:涂年生
name:涂聚文
name:test
name:geovindu
name:江城
name:保中
name:王二
name:李三
name:张四
name:赵五
name:陈六
name:刘七
name:徐八
name:何九
SELECT * FROM dbo.Customers where id=@id
-- @id: '1' (Type = Int32, IsNullable = false)
-- Executing at 2023/1/29 11:48:03 +08:00
-- Completed in 7 ms with result: SqlDataReader
姓名:涂年生
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)生存.---Geovin Du(涂聚文）
浙公网安备 33010602011771号