Cannot configure many to many relation EF Core

0

I cannot configure many to many relation in EF Core and ask you for help. It is not a duplicate because I am not asking how to configure this in general, but asking for help in my specific case. There are my models:

public class CompanyDto
{
    public int Id { get; set; }
    public string Name { get; set; }
}
public class CompanyCategoryDto
{
    public int CompanyId { get; set; }
    public int CategoryId { get; set; }
    public CompanyDto Company { get; set; }
    public CategoryDto Category { get; set; }
}
public class CategoryDto
{
    private ICollection<int> _parentIds;
    public int Id { get; set; }
    public string Name { get; set; }
    public int? ParentId { get; set; }
    public CategoryDto Parent { get; set; }
    public ICollection<CategoryDto> Children { get; set; }
}

And here is my Context model building config:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<CompanyDto>().ToTable("companies");
    modelBuilder.Entity<CompanyCategoryDto>().ToTable("companies_categories")
        .HasOne<CompanyDto>();
    modelBuilder.Entity<CompanyCategoryDto>().HasOne<CategoryDto>();
    modelBuilder.Entity<CompanyCategoryDto>().HasKey(cc => new {cc.CompanyId, cc.CategoryId});
    modelBuilder.Entity<CompanyCategoryDto>().Property(cc => cc.CategoryId).HasColumnName("categoryId");
    modelBuilder.Entity<CompanyCategoryDto>().Property(cc => cc.CompanyId).HasColumnName("companyId");
    modelBuilder.Entity<CategoryDto>().ToTable("categories")
            .HasMany(c => c.Children).WithOne(c => c.Parent).HasForeignKey(c => c.ParentId);
    base.OnModelCreating(modelBuilder);
}
public virtual DbSet<CompanyDto> Companies { get; set; }
public virtual DbSet<CategoryDto> Categories { get; set; }
public virtual DbSet<CompanyCategoryDto> CompaniesCategories { get; set; }

There are the three tables:

companies_dcategories:
companyId  int not null,
categoryId int not null,
primary key (companyId, categoryId),
constraint companies_categories_categories_id_fk
    foreign key (categoryId) references categories (id),
constraint companies_categories_company_id_fk
    foreign key (companyId) references companies (id)
companies:
id  int not null,
primary key id
categories:
id  int not null,
parentId int not null,
primary key id,
constraint categories_categories_id_fk
    foreign key (parentId) references categories (id),

Performing this request:

return await _context.Set<CompanyDto>().Include(c => c.Categories).ToListAsync();

I get the following error:

An unhandled exception occurred while processing the request.

MySqlException: Unknown column 'c.Categories.CompanyDtoId' in 'field list'
MySqlConnector.Core.ServerSession.TryAsyncContinuation(Task<ArraySegment<byte>> task) in 
C:\projects\mysqlconnector\src\MySqlConnector\Core\ServerSession.cs, line 1252

MySqlException: Unknown column 'c.Categories.CompanyDtoId' in 'field list'
MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet(ResultSet resultSet) in 
C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs, line 81

And there is a log record:

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (12ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `c.Categories`.`Id`, `c.Categories`.`CompanyDtoId`, `c.Categories`.`ParentId`
      FROM `categories` AS `c.Categories`
      INNER JOIN (
          SELECT `c0`.`Id`
          FROM `companies` AS `c0`
      ) AS `t` ON `c.Categories`.`CompanyDtoId` = `t`.`Id`
      ORDER BY `t`.`Id`
MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 'c.Categories.CompanyDtoId' in 'field list'

Would be great if you could help me!

c#
entity-framework
.net-core
ef-core-2.2
asked on Stack Overflow Jun 13, 2019 by Vitalii Isaenko • edited Jun 13, 2019 by Vitalii Isaenko

1 Answer

0

You can use IEntityTypeConfiguration interface on your business model and configure many-to-many relationship in this way.

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

public class CompanyCategoryDto : IEntityTypeConfiguration<CompanyCategoryDto>
{
    public int CompanyId { get; set; }
    public virtual CompanyDto Company { get; set; }

    public int CategoryId { get; set; }
    public virtual CategoryDto Category { get; set; }

    //other properties

    public void Configure(EntityTypeBuilder<CompanyCategoryDto> builder)
    {
        builder.HasKey(x => new { x.CompanyId, x.CategoryId });
        builder.HasOne(x => x.Company).WithMany(x => x.Companies).HasForeignKey(x => x.CompanyId);
        builder.HasOne(x => x.Category).WithMany(x => x.Categories).HasForeignKey(x => x.CategoryId);
    }
}

And then add the below line inside OnModelCreating method:

builder.ApplyConfiguration(new CompanyCategoryDto());
answered on Stack Overflow Jun 13, 2019 by Reyan Chougle

User contributions licensed under CC BY-SA 3.0