Creating POCO classes for complex relationship scenario in EF core 7-CodePudding

I'm working with entityframework core 7 using code first approach. I have a typical scenario at least it's typical for me. I need to create 3 poco classes, which will eventually create 3 tables in database e.g. Company, User and Role. I am having issues with relationship. Below are table structures.

create table Company(
  Id uniqueidentifier not null Primary Key,
  Name varchar(255) not null,
  CreatedBy uniqueidentifier null Foreign Key references User(Id),
  ModifiedBy uniqueidentifier null Foreign Key references User(Id)
)

create table User(
  Id uniqueidentifier not null Primary Key,
  Name varchar(255) not null,
  RoleId uniqueidentifier null Foreign Key references Role(Id),
  CompanyId uniqueidentifier null Foreign Key references Company(Id),
  CreatedBy uniqueidentifier null Foreign Key references User(Id),
  ModifiedBy uniqueidentifier null Foreign Key references User(Id)
)

create table Role(
  Id uniqueidentifier not null Primary Key,
  Name varchar(255) not null,
  CompanyId uniqueidentifier null Foreign Key references Company(Id),
  CreatedBy uniqueidentifier null Foreign Key references User(Id),
  ModifiedBy uniqueidentifier null Foreign Key references User(Id)
)

There are other columns also but they don't have relationship so I omitted those.

CodePudding user response：

First of all, you won't be able to create tables because of circular reference (Company --> User --> Company). Since you allow nulls, you might - but it defeats the purpose of foreign keys.

Once you have the tables, the easiest way to create POCO classes and DbContext is to run scaffolding on the existing database. This gives you the classes with relationships either through Fluent APIs or (my preference) through data annotations. Once you are happy with the classes - delete the original table; create migrations and run database update

CodePudding user response：

Didn't do this for a while, but (without testing) it should probably work with something like this:

public class MyDbContext : DbContext
{
    public DbSet<Company> Company { get; set; }
    public DbSet<User> User { get; set; }
    public DbSet<Role> Role { get; set; }
}

public class Company
{
    public Guid Id { get; set; }
    [Required]
    [MaxLength(255)]
    public Name { get; set; }
    public User CreatedBy { get; set; }
    public Guid CreatedById { get; set; }
    public User ModifiedBy { get; set; }
    public Guid ModifiedById { get; set; }
}

public class User
{
    public Guid Id { get; set; }
    [Required]
    [MaxLength(255)]
    public Name { get; set; }
    public Role { get; set; }
    public Guid RoleId { get; set; }
    public Company Company { get; set; }
    public Guid CompanyId { get; set; }
}

public class Role
{
    public Guid Id { get; set; }
    [Required]
    [MaxLength(255)]
    public Name { get; set; }
    public Company Company { get; set; }
    public Guid CompanyId { get; set; }
    public User CreatedBy { get; set; }
    public Guid CreatedById { get; set; }
    public User ModifiedBy { get; set; }
    public Guid ModifiedById { get; set; }
}

I'm currently unsure about the allowed null FK within a user to another user. But if you're more familiar with DB first, just create the database as you like and let the POCOs be generated from EF core. Afterwards you can evolve your model by altering your POCOs and add migrations as you need.

The difference between Code first and DB first is really only this first step. If both exist you can't tell anymore which approach you've taken and you can change your POCOs as you like and create / apply migrations as you need.

Also I'm more familiar with DB first. I just create the database with the needed structure, relationships and features (especially default values, null FKs, delete propagation) and use DB first to receive a first version about how the POCOs have to look like to represent this stuff. Afterwards I check this structure in C# and alter it as needed (especially many-to-many is much more easier to define in C#). Then I drop my database and let EF generate a new one from my new code and check in DB if everything looks as expected.

CodePudding user response：

Put details of the poco classes , you must be using relations in a wrong way and partial classes to link those relations

Kindly paste code of your poco classes , not schema like this