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!
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());
User contributions licensed under CC BY-SA 3.0