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()).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