.NET 6实体框架核心：多一对多的关系

Question

我正在使用.NET 6和实体框架Core构建Web API，以连接到SQL Server数据库。基本的东西看起来像是在起作用，但是我无法在模型中建立多一的关系。

考虑数据库中的以下3个表：

products ：

类别：

sdmkf.png“ rel =” nofollow noreferrer“> ：

我创建了3个与这些表相对应的模型，如下所示：

public class Product
{
    [Key]
    public int Id { get; set; }             = 0;
    public string Name { get; set; }        = string.Empty;
    public string Description { get; set; } = string.Empty;
    public string Label { get; set; }       = string.Empty;
    public int Sativa { get; set; }         = 0;
    public int Indica { get; set; }         = 0;

    public ICollection<CategoryProduct> Categories { get; set; } = new HashSet<CategoryProduct>();
}

public class Category
{
    [Key]
    public int Id { get; set; }      = 0;
    public string Name { get; set; } = string.Empty;
    public int Order { get; set; }   = 0;

    public ICollection<CategoryProduct> Products { get; set; } = new HashSet<CategoryProduct>();
}

public class CategoryProduct
{
    public int CategoryId { get; set; }     = 0;
    public Category? Category { get; set; } = null;
    public int ProductId { get; set; }      = 0;
    public Product? Product { get; set; }   = null;
}

我创建了以下dbContext类，以与数据库进行通信：

public class CoffeeshopContext : DbContext
{
    public DbSet<Shop>? Shops { get; set; }                        = null;
    public DbSet<Category>? Categories { get; set; }               = null;
    public DbSet<Product>? Products { get; set; }                  = null;
    public DbSet<Price>? Prices { get; set; }                      = null;
    public DbSet<CategoryProduct>? ProductCategories { get; set; } = null;

    private readonly IConfiguration _configuration;

    public CoffeeshopContext(DbContextOptions<CoffeeshopContext> options, IConfiguration configuration) : base(options)
    {
        _configuration = configuration;
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<CategoryProduct>().HasKey(x => new { x.CategoryId, x.ProductId });
        modelBuilder.Entity<CategoryProduct>().HasOne(x => x.Product)
                                              .WithMany(x => x.Categories)
                                              .HasForeignKey(x => x.ProductId);
        modelBuilder.Entity<CategoryProduct>().HasOne(x => x.Category)
                                              .WithMany(x => x.Products)
                                              .HasForeignKey(x => x.CategoryId);
        base.OnModelCreating(modelBuilder);
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
            optionsBuilder.UseSqlServer(_configuration.GetConnectionString(nameof(CoffeeshopContext)));
    }
}

当我使用Swagger运行项目时在调用和检索产品的端点时，我会得到以下结果：

[
  {
    "id": 3,
    "name": "Ice Cream Bean",
    "description": "Well balanced hybrid with a mood boosting euphoric high and body relaxation. Diesel and citrus aroma with creamy fruity undertones.",
    "label": "CALI STRAIN",
    "sativa": 50,
    "indica": 50,
    "categories": []
  },
  {
    "id": 4,
    "name": "Blue Cheese",
    "description": "Deep relaxing, calming and pain relief. Berry, blue cheese aroma and a creamy taste.",
    "label": "CLASSIC STRAIN",
    "sativa": 20,
    "indica": 80,
    "categories": []
  }
]

因此，端点正在工作并从数据库中检索产品。

但是，由于某种原因，它没有与类别之间的关系（是的，我相信productCategory表中有一些记录来定义产品和类别之间的关系）。我确定我缺少一些东西，但是我看不到它缺少什么。有人可以将我指向如何完成这项工作的正确方向吗？

Answer 1

我在清楚的答案的帮助下找到了解决方案。安装microsoft.aspnetcore.mvc.newtonsoftjson软件包，然后将建议更改为program.cs 和我的shopscontroller.cs我能够制造调用数据库并检索具有其类别的产品列表。但是，未检索名称和订单属性属性尚未检索。

经过一番反复试验，我想出了一种对我有用的解决方案。我对它有所适合我的需求（我并不需要在此响应中为类别的order属性）。我所做的是以下内容：我完成了一些新类，将数据库对象映射到没有数据库导航所需的属性。然后，我在products dbContext对象的表中添加了select（）。最后，我的终点最终以这样的方式出现：

[HttpGet, Route("~/coffeeshop/products")]
public async Task<ActionResult<IEnumerable<Product>>> GetProducts(int shopid)
{
    if (_context.Products == null)
        return NotFound();

    return await _context.Products
        .Include(x => x.Categories)
        .Include(x => x.Prices)
        .Where(x => x.Prices.Any(y => y.ShopId == shopid))
        .Select(x => new Product()
        {
            Id          = x.Id,
            Name        = x.Name,
            Description = x.Description,
            Label       = x.Label,
            Sativa      = x.Sativa,
            Indica      = x.Indica,
            Categories  = x.Categories.Select(y => new SmallCategory()
            {
                Name  = y.Category.Name,
                Id    = y.CategoryId
            }).ToList(),
            Prices = x.Prices.Where(y => y.ShopId == shopid).Select(z => new SmallPrice()
            {
                Gram1 = z.Gram1,
                Gram2 = z.Gram2,
                Gram5 = z.Gram5
            }).ToList()
        })
        .ToListAsync();
}

该解决方案产生的响应如下：

[
  {
    "id": 4,
    "name": "Blue Cheese",
    "description": "Deep relaxing, calming and pain relief. Berry, blue cheese aroma and a creamy taste.",
    "label": "CLASSIC STRAIN",
    "sativa": 20,
    "indica": 80,
    "categories": [
      {
        "id": 1,
        "name": "weed"
      },
      {
        "id": 4,
        "name": "indica"
      }
    ],
    "prices": [
      {
        "gram1": 15,
        "gram2": 30,
        "gram5": 67.5
      }
    ]
  }
]

它为我提供了特定商店的产品，包括该产品所属的类别，仅显示该特定商店的价格。这正是我希望输出的方式。

Answer 2

1.安装软件包microsoft.aspnetcore.mvc.mvc.newtonsoftjson

2.从builder.services.addcontrollers（）;

builder.Services.AddControllers().AddNewtonsoftJson(options =>
    options.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore
); 

3. In GetProduct Action添加添加添加 。

        // GET: api/Products
        [HttpGet]
        public async Task<ActionResult<IEnumerable<Product>>> GetProduct()
        {
          if (_context.Products == null)
          {
              return NotFound();
          }
            return await _context.Products.Include(x=>x.Categories).ToListAsync();
        }

​

​//i.sstatic.net/68t0x.png“ alt =” inter Image Description在此处”>