数据库 Freesql
基础复习：
https://www.cnblogs.com/my_life/articles/5950415.html
一对多关系
对象的定义
public class Customer { [Column(IsPrimary = true, IsIdentity = true)] public int Id { get; set; } public string Name { get; set; } [Navigate(nameof(Order.CustomerId))] public ICollection<Order> Orders { get; set; } } public class Order { [Column(IsPrimary = true, IsIdentity = true)] public int Id { get; set; } public string OrderNo { get; set; } public int CustomerId { get; set; } [Navigate(nameof(CustomerId))] public Customer Customer { get; set; } }
一对多的操作
//初始化数据 public void TestOne2Many() { Customer smith = new Customer() { Name="Smith" }; smith.Orders = new List<Order>(); smith.Orders.Add(new Order() { OrderNo = "Device", Customer = smith }) ; smith.Orders.Add(new Order() { OrderNo = "Consumables", Customer=smith }); //局部设置 var repo = Db.mysql.GetRepository<Customer>(); //repo.DbContextOptions.EnableCascadeSave = true; repo.Insert(smith); //全局设置,默认为false Db.mysql.SetDbContextOptions(opt =>opt.EnableCascadeSave = true ); } //一对多的查询，修改和删除 private void button4_Click(object sender, EventArgs e) { var repo = Db.mysql.GetRepository<Customer>(); //repo.DbContextOptions.EnableCascadeSave = true; var r = repo.Select.Where(s => s.Name == "Smith").IncludeMany(c => c.Orders).ToList(); r[0].Orders.ToList()[0].OrderNo = "1334"; repo.Update(r); var del=repo.Where(c => c.Name == "Smith").First(); repo.Delete(del); }
多对多操作
代码示例1：
class Song { public Guid Id { get; set; } public string Name { get; set; } public List<Tag> Tags { get; set; } } class Tag { public Guid Id { get; set; } public string TagName { get; set; } public List<Song> Songs { get; set; } } class SongTag { public Guid SongId { get; set; } public Song Song { get; set; } public Guid TagId { get; set; } public Tag Tag { get; set; } } public void TestManyToMany() { var tags = new[] { new Tag { TagName = "流行" }, new Tag { TagName = "80后" }, new Tag { TagName = "00后" }, new Tag { TagName = "摇滚" } }; var ss = new[] { new Song { Name = "爱你一万年.mp3", Tags = new List<Tag>(new[] { tags[0], tags[1] }) }, new Song { Name = "李白.mp3", Tags = new List<Tag>(new[] { tags[0], tags[2] }) } }; var repo = Db.mysql.GetRepository<Song>(); repo.DbContextOptions.EnableCascadeSave = true; repo.Insert(ss); //INSERT INTO "Song"("Id", "Name") VALUES('5d90fdb3-6a6b-2c58-00c8-37974177440d', '爱你一万年.mp3'), ('5d90fdb3-6a6b-2c58-00c8-37987f29b197', '李白.mp3') //INSERT INTO "Tag"("Id", "TagName") VALUES('5d90fdb7-6a6b-2c58-00c8-37991ead4f05', '流行'), ('5d90fdbd-6a6b-2c58-00c8-379a0432a09c', '80后') //INSERT INTO "SongTag"("SongId", "TagId") VALUES('5d90fdb3-6a6b-2c58-00c8-37974177440d', '5d90fdb7-6a6b-2c58-00c8-37991ead4f05'), ('5d90fdb3-6a6b-2c58-00c8-37974177440d', '5d90fdbd-6a6b-2c58-00c8-379a0432a09c') //INSERT INTO "Tag"("Id", "TagName") VALUES('5d90fdcc-6a6b-2c58-00c8-379b5af59d25', '00后') //INSERT INTO "SongTag"("SongId", "TagId") VALUES('5d90fdb3-6a6b-2c58-00c8-37987f29b197', '5d90fdb7-6a6b-2c58-00c8-37991ead4f05'), ('5d90fdb3-6a6b-2c58-00c8-37987f29b197', '5d90fdcc-6a6b-2c58-00c8-379b5af59d25') ss[0].Name = "爱你一万年.mp5"; ss[0].Tags.Clear(); ss[0].Tags.Add(tags[0]); ss[1].Name = "李白.mp5"; ss[1].Tags.Clear(); ss[1].Tags.Add(tags[3]); repo.Update(ss); //UPDATE "Song" SET "Name" = CASE "Id" //WHEN '5d90fdb3-6a6b-2c58-00c8-37974177440d' THEN '爱你一万年.mp5' //WHEN '5d90fdb3-6a6b-2c58-00c8-37987f29b197' THEN '李白.mp5' END //WHERE ("Id" IN ('5d90fdb3-6a6b-2c58-00c8-37974177440d','5d90fdb3-6a6b-2c58-00c8-37987f29b197')) //SELECT a."SongId", a."TagId" //FROM "SongTag" a //WHERE (a."SongId" = '5d90fdb3-6a6b-2c58-00c8-37974177440d') //DELETE FROM "SongTag" WHERE ("SongId" = '5d90fdb3-6a6b-2c58-00c8-37974177440d' AND "TagId" = '5d90fdbd-6a6b-2c58-00c8-379a0432a09c') //INSERT INTO "Tag"("Id", "TagName") VALUES('5d90febd-6a6b-2c58-00c8-379c21acfc72', '摇滚') //SELECT a."SongId", a."TagId" //FROM "SongTag" a //WHERE (a."SongId" = '5d90fdb3-6a6b-2c58-00c8-37987f29b197') //DELETE FROM "SongTag" WHERE ("SongId" = '5d90fdb3-6a6b-2c58-00c8-37987f29b197' AND "TagId" = '5d90fdb7-6a6b-2c58-00c8-37991ead4f05' OR "SongId" = '5d90fdb3-6a6b-2c58-00c8-37987f29b197' AND "TagId" = '5d90fdcc-6a6b-2c58-00c8-379b5af59d25') //INSERT INTO "SongTag"("SongId", "TagId") VALUES('5d90fdb3-6a6b-2c58-00c8-37987f29b197', '5d90febd-6a6b-2c58-00c8-379c21acfc72') ss[0].Name = "爱你一万年.mp4"; ss[0].Tags.Clear(); ss[1].Name = "李白.mp4"; ss[1].Tags.Clear(); repo.Update(ss); //DELETE FROM "SongTag" WHERE ("SongId" = '5d90fdb3-6a6b-2c58-00c8-37974177440d') //DELETE FROM "SongTag" WHERE ("SongId" = '5d90fdb3-6a6b-2c58-00c8-37987f29b197') //UPDATE "Song" SET "Name" = CASE "Id" //WHEN '5d90fdb3-6a6b-2c58-00c8-37974177440d' THEN '爱你一万年.mp4' //WHEN '5d90fdb3-6a6b-2c58-00c8-37987f29b197' THEN '李白.mp4' END //WHERE ("Id" IN ('5d90fdb3-6a6b-2c58-00c8-37974177440d','5d90fdb3-6a6b-2c58-00c8-37987f29b197')) }
代码示例2
public class Author { [Column(IsPrimary = true, IsIdentity = true)] public int Id { get; set; } public string Name { get; set; } [Navigate(ManyToMany =typeof(AuthorPaper))] public List<Paper> Papers { get; set; } } public class Paper { [Column(IsPrimary = true, IsIdentity = true)] public int Id { get; set; } public string Name { get; set; } [Navigate(ManyToMany = typeof(AuthorPaper))] public List<Author> Authors { get; set; } } public class AuthorPaper { public int AuthorId { get; set; } public Author author { get; set; } public int PaperId { get; set; } public Paper paper { get; set; } } public class SelfManyToMany { public static void Test() { var repo = Db.mysql.GetRepository<Author>(); Author Jim = new Author(); Jim.Name = "Jim"; var papers = new[] { new Paper{Name="P1"}, new Paper{Name="P2"}, new Paper{Name="P3"}, new Paper{Name="P4"}, new Paper{Name="P5"}, new Paper{Name="P6"}, }; Jim.Papers = new List<Paper>(); Jim.Papers.Add(papers[2]); Jim.Papers.Add(papers[3]) ; repo.Insert(Jim); } public static void TestClear() { var repo = Db.mysql.GetRepository<Author>(); var jim=repo.Select.IncludeMany(s => s.Papers).First(); jim.Papers.Clear(); repo.Update(jim); } }
基于Repository的基本操作
获取IFreeSql对象，里面有Insert<> ,Update<> ,Delete<>,Select<>等。
或者var repo=Db.mysql.GetRepository<>();
里面有Select, Delete,Insert，Update等。
