EF Db First approach - dealing with tables without PK's

.net-core c# entity-framework-core


I tried to create a context for existing db using db first approach and got the error below:

// Unable to generate entity type for table 'dbo.TT_ProjectMembers'. Please see the warning messages. Unable to identify the primary key for table 'dbo.TT_ProjectMembers'. Unable to generate entity type for table 'dbo.TT_ProjectMembers'.

TT_ProjectMembers table contains two columns: ProjectId(int) UserId(int). Unfortunately, I have no opportunity of updating the database (adding new columns and so on).

I decided to create the entity and all other stuff manually. I decided to set up composite key for this table, because it just makes sense - it's wrong to have 2 rows with the same ProjectId and UserId.

//entity class

    public class ProjectMember
        public int ProjectID { get; set; }
        public int UserID { get; set; }


    //dbcontext property
    public virtual DbSet<ProjectMember> ProjectMembers { get; set; }    

    // entity config
        modelBuilder.Entity<ProjectMember>(entity =>
        entity.HasKey(p => new {p.ProjectID, p.UserID});

Surprisingly, it worked. Now I can do any CRUD operations, but I have some questions:

  1. Is there any disadvantages or problems due to such solution of the problem? How do you usually solve this problem?
  2. Why EF needs you to have PK on the table?
  3. How EF works under the hood? (I know that this is a broad question, so any book/article advice will be appreciated)
1/2/2019 5:30:18 PM

Accepted Answer

1) Like this. Middleman tables that decompose a M:M relationship into two 1:M should have the two foreign key columns as their PK. It's a rookie error to make such tables have a third PK column

2) Because that's how it looks up related data when you ask for it (employee.Company.Name might cause company data to be loaded for that employee, so perhaps results in something like SELECT Company.* FROM Company JOIN Employee ON Company.ID = Employe.CompanyId WHERE Employee.Id = @id or, if joins aren't used, querying the CompanyId from that employee, then querying the company details from the Company ID), and how it knows for sure it's updating only on row when persisting changes. "No PK, no play!"

3) Too broad for SO, i'm afraid, but feel fre to take a look for resources that show you how to activate logging of the queries it generates, then you can see when you do context.Employee.Where(e => e.Name = "John") how it becomes SELECT x FROM Employee WHERE name = 'John' etc

1/2/2019 5:42:16 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow