Mixing
EF Db First approach - dealing with tables without PK's
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.ToTable("TT_ProjectMembers");
entity.HasKey(p => new {p.ProjectID, p.UserID});
});
Surprisingly, it worked. Now I can do any CRUD operations, but I have some questions:
- Is there any disadvantages or problems due to such solution of the problem? How do you usually solve this problem?
- Why EF needs you to have PK on the table?
- How EF works under the hood? (I know that this is a broad question, so any book/article advice will be appreciated)
c# .net-core entity-framework-core
asked Jan 2 at 17:30
Kirill.LutzowKirill.Lutzow
4618
add a comment |
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.ToTable("TT_ProjectMembers");
entity.HasKey(p => new {p.ProjectID, p.UserID});
});
Surprisingly, it worked. Now I can do any CRUD operations, but I have some questions:
- Is there any disadvantages or problems due to such solution of the problem? How do you usually solve this problem?
- Why EF needs you to have PK on the table?
- How EF works under the hood? (I know that this is a broad question, so any book/article advice will be appreciated)
c# .net-core entity-framework-core
asked Jan 2 at 17:30
Kirill.LutzowKirill.Lutzow
4618
1
(2) Because of change tracking and update/delete operations. If you don't need such (readonly data), consider Query Types which have no such requirement.
– Ivan Stoev
Jan 2 at 19:44
add a comment |
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.ToTable("TT_ProjectMembers");
entity.HasKey(p => new {p.ProjectID, p.UserID});
});
Surprisingly, it worked. Now I can do any CRUD operations, but I have some questions:
- Is there any disadvantages or problems due to such solution of the problem? How do you usually solve this problem?
- Why EF needs you to have PK on the table?
- How EF works under the hood? (I know that this is a broad question, so any book/article advice will be appreciated)
c# .net-core entity-framework-core
asked Jan 2 at 17:30
Kirill.LutzowKirill.Lutzow
4618
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.ToTable("TT_ProjectMembers");
entity.HasKey(p => new {p.ProjectID, p.UserID});
});
Surprisingly, it worked. Now I can do any CRUD operations, but I have some questions:
- Is there any disadvantages or problems due to such solution of the problem? How do you usually solve this problem?
- Why EF needs you to have PK on the table?
- How EF works under the hood? (I know that this is a broad question, so any book/article advice will be appreciated)
c# .net-core entity-framework-core
c# .net-core entity-framework-core
asked Jan 2 at 17:30
Kirill.LutzowKirill.Lutzow
4618
asked Jan 2 at 17:30
Kirill.LutzowKirill.Lutzow
4618
asked Jan 2 at 17:30
Kirill.LutzowKirill.Lutzow
4618
asked Jan 2 at 17:30
Kirill.LutzowKirill.Lutzow
4618
asked Jan 2 at 17:30
Kirill.LutzowKirill.Lutzow
4618
4618
1
(2) Because of change tracking and update/delete operations. If you don't need such (readonly data), consider Query Types which have no such requirement.
– Ivan Stoev
Jan 2 at 19:44
add a comment |
1
(2) Because of change tracking and update/delete operations. If you don't need such (readonly data), consider Query Types which have no such requirement.
– Ivan Stoev
Jan 2 at 19:44
1
1
(2) Because of change tracking and update/delete operations. If you don't need such (readonly data), consider Query Types which have no such requirement.
– Ivan Stoev
Jan 2 at 19:44
(2) Because of change tracking and update/delete operations. If you don't need such (readonly data), consider Query Types which have no such requirement.
– Ivan Stoev
Jan 2 at 19:44
add a comment |
1 Answer
1
active
oldest
votes
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
answered Jan 2 at 17:36
Caius JardCaius Jard
12.5k21340
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54010669%2fef-db-first-approach-dealing-with-tables-without-pks%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Jan 2 at 17:36
Caius JardCaius Jard
12.5k21340
add a comment |
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
answered Jan 2 at 17:36
Caius JardCaius Jard
12.5k21340
add a comment |
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
answered Jan 2 at 17:36
Caius JardCaius Jard
12.5k21340
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
answered Jan 2 at 17:36
Caius JardCaius Jard
12.5k21340
edited Jan 2 at 17:42
answered Jan 2 at 17:36
Caius JardCaius Jard
12.5k21340
answered Jan 2 at 17:36
Caius JardCaius Jard
12.5k21340
answered Jan 2 at 17:36
Caius JardCaius Jard
12.5k21340
12.5k21340
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54010669%2fef-db-first-approach-dealing-with-tables-without-pks%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
(2) Because of change tracking and update/delete operations. If you don't need such (readonly data), consider Query Types which have no such requirement.
– Ivan Stoev
Jan 2 at 19:44