把旧系统迁移到.Net Core 2.0 日记（10) -- EF core 和之前版本多对多映射区别

EF Core 现在不支持多对多映射,只能做2个一对多映射.

而EF Core 的一对多映射,采用约定大于配置. 默认的外键字段名是(引用实体名+主键名, 或者引用实体的主键名)

 public class Product
    {   
        [Key]
        public int ProdId{ get; set; }

        public String ProdCode{ get; set; }
        public String ProdName{ get; set; }

        public IList<CategoryProduct> CategoryProducts { get; set; }
    }
    public class Category
    {   
        [Key]
        public int CategoryId{ get; set; }
        public String CategoryCode { get; set; }
        public String CategoryName{ get; set; }

        public IList<CategoryProduct> CategoryProducts { get; set; }
    }

例如Product实体里有一个Category类的字段. 对应的SQL语句,就会在Product表里查找CategoryCategoryId的字段,或者是CategoryId的字段

 参考这个 https://docs.microsoft.com/zh-cn/ef/core/modeling/relationships

比如Product和Category 我现在定义Product和Category是多对多关系.

那么实体定义如下:

   /// <summary>
    /// EF CORE 处理Many to Many关系,要转成两个 One to Many
    /// https://docs.microsoft.com/en-us/ef/core/modeling/relationships
    /// </summary>
    public class CategoryProduct
    {   
        public int Id { get; set; }
        public int CategoryId{ get; set; }
        public Category Category { get; set; }
        public int ProdId { get; set; }
        public Product Product { get; set; }
    }

在context的OnModelCreating方法指定mapping

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {  
            
            //多对多关系,要手工指定
            modelBuilder.Entity<CategoryProduct>().HasKey(x => new { x.CategoryId, x.ProdId }); //指定中间表主键


            modelBuilder.Entity<CategoryProduct>().HasOne(cp => cp.Category)
                        .WithMany(c => c.CategoryProducts)
                        .HasForeignKey(cp => cp.CategoryId);
            
            modelBuilder.Entity<CategoryProduct>().HasOne(cp => cp.Product)
                        .WithMany(c => c.CategoryProducts)
                        .HasForeignKey(cp => cp.ProdId);

        }

但是我很困惑,这样的写法, 我在Category里的CategoryProducts的Collection 有什么用呢?

比如我要找CategroyId=1的所有产品信息,我觉得最直观的写法的是context.categories.Where(t=>t.CategoryId==1).FirstOrDefault().Products.toList()这样的写法.

它现在却要这样写

 _context.CategoryProducts.Where(x => x.CategoryId.Equals(1)).Include(x=>x.Product).Select(x=>x.Product).ToList();

或者这样写

            var prodIdList = _context.CategoryProducts.Where(x => x.CategoryId.Equals(1)).Select(x=>x.ProdId);
            var qry = _context.Products.Where(x => prodIdList.Contains(x.ProdId)).ToList();

            var result = new { total = qry.Count(), rows = qry.ToList() };
            return Json(result);

这样需要查询2次,在NLOG的日志里可以看到

2018-04-15 22:33:56.9887|1|INFO|Microsoft.AspNetCore.Hosting.Internal.WebHost|Request starting HTTP/1.1 GET http://localhost:5001/CRM/Product/ListCategoryProducts/1   
2018-04-15 22:33:57.0437|24|INFO|Microsoft.AspNetCore.ResponseCaching.ResponseCachingMiddleware|No cached response available for this request. 
2018-04-15 22:33:57.2326|1|INFO|Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker|Executing action method FoxCRMCore.Controllers.CRM.ProductController.ListCategoryProducts (FoxCRMCore) with arguments (1) - ModelState is Valid 
2018-04-15 22:33:58.7572|10403|INFO|Microsoft.EntityFrameworkCore.Infrastructure|Entity Framework Core 2.0.2-rtm-10011 initialized 'CRMContext' using provider 'Pomelo.EntityFrameworkCore.MySql' with options: MaxPoolSize=128  
2018-04-15 22:33:59.4957|20101|INFO|Microsoft.EntityFrameworkCore.Database.Command|Executed DbCommand (73ms) [Parameters=[@__id_0='?'], CommandType='Text', CommandTimeout='30']
SELECT `x`.`CategoryId`, `x`.`CategoryCode`, `x`.`CategoryName`, `x`.`CreateDate`, `x`.`CreateUser`, `x`.`IsActive`, `x`.`ModifyDate`, `x`.`ModifyUser`, `x`.`SYSId`
FROM `CRMCategory` AS `x`
WHERE `x`.`CategoryId` = @__id_0 
2018-04-15 22:33:59.7948|20101|INFO|Microsoft.EntityFrameworkCore.Database.Command|Executed DbCommand (9ms) [Parameters=[@__id_0='?'], CommandType='Text', CommandTimeout='30']
SELECT `x`.`ProdId`, `x`.`CreateDate`, `x`.`CreateUser`, `x`.`IsActive`, `x`.`ModifyDate`, `x`.`ModifyUser`, `x`.`ProdCode`, `x`.`ProdFullName`, `x`.`ProdName`, `x`.`SYSId`
FROM `CRMProduct` AS `x`
WHERE `x`.`ProdId` IN (
    SELECT `x0`.`ProdId`
    FROM `CRMCategoryProduct` AS `x0`
    WHERE `x0`.`CategoryId` = @__id_0
) 
2018-04-15 22:33:59.8546|1|INFO|Microsoft.AspNetCore.Mvc.Formatters.Json.Internal.JsonResultExecutor|Executing JsonResult, writing value { total = 2, rows = System.Collections.Generic.List`1[FoxCRMCore.Models.PD.Product] }. 
2018-04-15 22:33:59.8996|2|INFO|Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker|Executed action FoxCRMCore.Controllers.CRM.ProductController.ListCategoryProducts (FoxCRMCore) in 2753.674ms 

如果Category和Product是1对多关系.

    public class Product
    {   
        [Key]
        public int ProdId{ get; set; }

        public String ProdCode{ get; set; }
        public String ProdName{ get; set; }

        public int CategoryId { get; set; }
        public Category Category { get; set; } //1对多
    }
    public class Category
    {   
        [Key]
        public int CategoryId{ get; set; }
        public String CategoryCode { get; set; }
        public String CategoryName{ get; set; }
  
        public IList<Product> Products { get; set; }

    }

在context的OnModelCreating方法指定mapping

            modelBuilder.Entity<Product>().HasOne(p => p.Category)
            .WithMany(c => c.Products)
            .HasForeignKey(p => p.CategoryId);

比如我要找CategroyId=1的所有产品信息,写法如下:

(因为用了Include,它会一直循环eagerLoad collection, 找Category对应的Products,然后再找Products的Category,如果转成Json就死循环了.所以用Select只显示部分字段)

Json.JsonSerializationException: Self referencing loop detected, 另一个方案是在Product的Category字段加上[IgnoreDataMember]  （using System.Runtime.Serialization;）

            var cat = _context.Categories.Where(x => x.CategoryId.Equals(id)).Include(x=>x.Products).FirstOrDefault();
            var qry = cat.Products.Select(x=> new {x.ProdId,
                x.ProdCode,
                x.ProdName,
                x.ProdFullName,
                x.CategoryId,
                x.Category.
                CategoryName});

            var result = new { total = qry.Count(), rows = qry.ToList() };
            return Json(result);

NLOG日志显示,也是查询2次

2018-04-15 23:28:54.7770|20101|INFO|Microsoft.EntityFrameworkCore.Database.Command|Executed DbCommand (68ms) [Parameters=[@__id_0='?'], CommandType='Text', CommandTimeout='30']
SELECT `x`.`CategoryId`, `x`.`CategoryCode`, `x`.`CategoryName`, `x`.`CreateDate`, `x`.`CreateUser`, `x`.`IsActive`, `x`.`ModifyDate`, `x`.`ModifyUser`, `x`.`SYSId`
FROM `CRMCategory` AS `x`
WHERE `x`.`CategoryId` = @__id_0
ORDER BY `x`.`CategoryId`
LIMIT 1 
2018-04-15 23:28:54.9476|20101|INFO|Microsoft.EntityFrameworkCore.Database.Command|Executed DbCommand (0ms) [Parameters=[@__id_0='?'], CommandType='Text', CommandTimeout='30']
SELECT `x.Products`.`ProdId`, `x.Products`.`CategoryId`, `x.Products`.`CreateDate`, `x.Products`.`CreateUser`, `x.Products`.`IsActive`, `x.Products`.`ModifyDate`, `x.Products`.`ModifyUser`, `x.Products`.`ProdCode`, `x.Products`.`ProdFullName`, `x.Products`.`ProdName`, `x.Products`.`SYSId`
FROM `CRMProduct` AS `x.Products`
INNER JOIN (
    SELECT `x0`.`CategoryId`
    FROM `CRMCategory` AS `x0`
    WHERE `x0`.`CategoryId` = @__id_0
    ORDER BY `x0`.`CategoryId`
    LIMIT 1
) AS `t` ON `x.Products`.`CategoryId` = `t`.`CategoryId`
ORDER BY `t`.`CategoryId` 

EF Core ThenInclude 自动完成提示,不会提示子对象的关联对象. 你写完再看,就会提示正确的.
https://github.com/dotnet/roslyn/issues/8237