.NET 6 Entity Framework Core : many-to-many relationships

Question

I'm building a Web API using .NET 6 and Entity Framework Core to connect to a SQL Server database. The basic stuff looks like it's working, but I cannot make the many-to-many relationships in my models work.

Considering the following 3 tables in the database:

Products:

Categories:

CategoryProduct:

I have created 3 models corresponding to these tables as follows:

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

I have created the following DbContext class to communicate with the database:

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

When I run the project using swagger, I get the following results when calling and endpoint which retrieves the Products:

[
  {
    "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": []
  }
]

So, the endpoint is working and retrieving products from the database.

However, for some reason it does not make the relationship to categories (and yes, I am sure there are records in the ProductCategory table to define the relationship between products and categories). I am sure I am missing something, but I don't see what it is I'm missing. Can someone perhaps point me in the right direction on how to make this work?

Answer 1

I found a solution with the help of Qing Guo's answer. Installing the Microsoft.AspNetCore.Mvc.NewtonsoftJson package and making the suggested change to Program.cs and my ShopsController.cs I was able to make calls to the database and retrieve a list of products with their categories. However, the Name and Order properties of the categories were not being retrieved.

After some trial and error I came up with a solution that worked for me. I finetuned it a bit to suit my needs (I didn't really need the Order property for a Category in this response for example). What I did was the following: I made some new classes to map the database objects to, without the properties needed for database navigation. I then added those in a Select() on the Products table of the DbContext object. In the end my endpoint ended up like this:

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

The response this solution produces is as follows:

[
  {
    "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
      }
    ]
  }
]

It gives me the products for a specific shop, including the categories the product belongs to and only showing the prices for that specific shop. This is exactly how I wanted the output to be.