CSharp: Relational mapping in donet using Entity Framework

 sql script

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92

--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

　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193

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

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221

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);         }       } }

　　

调用：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

//查询集合 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"); }

　　

输出：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85

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       姓名:涂年生