Mixing
Unique primary key using Entity Framework
2
I'm new to EF. Let's say I have a table in DB like this:
ID FirstName LastName DateOfBirth
-------------------------------------------
1 John Smith 1.1.1990
2 Mary Wilson 5.1.1991
Now I insert new entity into the table using EF:
dbcontext.Persons.Add(new Person
{
FirstName = "John",
LastName = "Smith",
DateOfBith = "1.1.1990"
});
dbcontext.SaveChanges();
I need the code to throw an exception because this row already exists in the database but what EF does is that it increments the ID column by 1 and creates new record:
ID FirstName LastName DateOfBirth
--------------------------------------------
1 John Smith 1.1.1990
2 Mary Wilson 5.1.1991
3 John Smith 1.1.1990
Is EF even capable of doing this?
c# sql .net entity-framework primary-key
edited Nov 20 '18 at 15:29
S.Akbari
30.3k93673
asked Nov 20 '18 at 8:24
xcelmxcelm
6417
add a comment |
2
I'm new to EF. Let's say I have a table in DB like this:
ID FirstName LastName DateOfBirth
-------------------------------------------
1 John Smith 1.1.1990
2 Mary Wilson 5.1.1991
Now I insert new entity into the table using EF:
dbcontext.Persons.Add(new Person
{
FirstName = "John",
LastName = "Smith",
DateOfBith = "1.1.1990"
});
dbcontext.SaveChanges();
I need the code to throw an exception because this row already exists in the database but what EF does is that it increments the ID column by 1 and creates new record:
ID FirstName LastName DateOfBirth
--------------------------------------------
1 John Smith 1.1.1990
2 Mary Wilson 5.1.1991
3 John Smith 1.1.1990
Is EF even capable of doing this?
c# sql .net entity-framework primary-key
edited Nov 20 '18 at 15:29
S.Akbari
30.3k93673
asked Nov 20 '18 at 8:24
xcelmxcelm
6417
docs.microsoft.com/en-us/ef/core/modeling/keys
– JohnB
Nov 20 '18 at 8:25
Apply unique constraint stackoverflow.com/a/41257827/2630817
– Just code
Nov 20 '18 at 8:25
Can you please show us the table structure (columns and their data types) in the database (including how you've defined your primary key), and the model classPersonin C# ?
– marc_s
Nov 20 '18 at 9:14
@marc_s after i applied some of the solutions bellow c# code looks like this public int ID { get; set; } [Index("IX_UniqueConstraint", 1, IsUnique = true)] public string FirstName { get; set; } [Index("IX_UniqueConstraint", 2, IsUnique = true)] public string LastName { get; set; } [Index("IX_UniqueConstraint", 3, IsUnique = true)] public DateTime BirthDate { get; set; } Db - ID is int, LastName is nvarchar, FirstName is nvarchar and BirthDate is datetime
– xcelm
Nov 20 '18 at 9:22
add a comment |
2
2
2
I'm new to EF. Let's say I have a table in DB like this:
ID FirstName LastName DateOfBirth
-------------------------------------------
1 John Smith 1.1.1990
2 Mary Wilson 5.1.1991
Now I insert new entity into the table using EF:
dbcontext.Persons.Add(new Person
{
FirstName = "John",
LastName = "Smith",
DateOfBith = "1.1.1990"
});
dbcontext.SaveChanges();
I need the code to throw an exception because this row already exists in the database but what EF does is that it increments the ID column by 1 and creates new record:
ID FirstName LastName DateOfBirth
--------------------------------------------
1 John Smith 1.1.1990
2 Mary Wilson 5.1.1991
3 John Smith 1.1.1990
Is EF even capable of doing this?
c# sql .net entity-framework primary-key
edited Nov 20 '18 at 15:29
S.Akbari
30.3k93673
asked Nov 20 '18 at 8:24
xcelmxcelm
6417
I'm new to EF. Let's say I have a table in DB like this:
ID FirstName LastName DateOfBirth
-------------------------------------------
1 John Smith 1.1.1990
2 Mary Wilson 5.1.1991
Now I insert new entity into the table using EF:
dbcontext.Persons.Add(new Person
{
FirstName = "John",
LastName = "Smith",
DateOfBith = "1.1.1990"
});
dbcontext.SaveChanges();
I need the code to throw an exception because this row already exists in the database but what EF does is that it increments the ID column by 1 and creates new record:
ID FirstName LastName DateOfBirth
--------------------------------------------
1 John Smith 1.1.1990
2 Mary Wilson 5.1.1991
3 John Smith 1.1.1990
Is EF even capable of doing this?
c# sql .net entity-framework primary-key
c# sql .net entity-framework primary-key
edited Nov 20 '18 at 15:29
S.Akbari
30.3k93673
asked Nov 20 '18 at 8:24
xcelmxcelm
6417
edited Nov 20 '18 at 15:29
S.Akbari
30.3k93673
asked Nov 20 '18 at 8:24
xcelmxcelm
6417
edited Nov 20 '18 at 15:29
S.Akbari
30.3k93673
edited Nov 20 '18 at 15:29
S.Akbari
30.3k93673
edited Nov 20 '18 at 15:29
S.Akbari
30.3k93673
30.3k93673
asked Nov 20 '18 at 8:24
xcelmxcelm
6417
asked Nov 20 '18 at 8:24
xcelmxcelm
6417
asked Nov 20 '18 at 8:24
xcelmxcelm
6417
6417
docs.microsoft.com/en-us/ef/core/modeling/keys
– JohnB
Nov 20 '18 at 8:25
Apply unique constraint stackoverflow.com/a/41257827/2630817
– Just code
Nov 20 '18 at 8:25
Can you please show us the table structure (columns and their data types) in the database (including how you've defined your primary key), and the model classPersonin C# ?
– marc_s
Nov 20 '18 at 9:14
@marc_s after i applied some of the solutions bellow c# code looks like this public int ID { get; set; } [Index("IX_UniqueConstraint", 1, IsUnique = true)] public string FirstName { get; set; } [Index("IX_UniqueConstraint", 2, IsUnique = true)] public string LastName { get; set; } [Index("IX_UniqueConstraint", 3, IsUnique = true)] public DateTime BirthDate { get; set; } Db - ID is int, LastName is nvarchar, FirstName is nvarchar and BirthDate is datetime
– xcelm
Nov 20 '18 at 9:22
add a comment |
docs.microsoft.com/en-us/ef/core/modeling/keys
– JohnB
Nov 20 '18 at 8:25
Apply unique constraint stackoverflow.com/a/41257827/2630817
– Just code
Nov 20 '18 at 8:25
Can you please show us the table structure (columns and their data types) in the database (including how you've defined your primary key), and the model classPersonin C# ?
– marc_s
Nov 20 '18 at 9:14
@marc_s after i applied some of the solutions bellow c# code looks like this public int ID { get; set; } [Index("IX_UniqueConstraint", 1, IsUnique = true)] public string FirstName { get; set; } [Index("IX_UniqueConstraint", 2, IsUnique = true)] public string LastName { get; set; } [Index("IX_UniqueConstraint", 3, IsUnique = true)] public DateTime BirthDate { get; set; } Db - ID is int, LastName is nvarchar, FirstName is nvarchar and BirthDate is datetime
– xcelm
Nov 20 '18 at 9:22
docs.microsoft.com/en-us/ef/core/modeling/keys
– JohnB
Nov 20 '18 at 8:25
docs.microsoft.com/en-us/ef/core/modeling/keys
– JohnB
Nov 20 '18 at 8:25
Apply unique constraint stackoverflow.com/a/41257827/2630817
– Just code
Nov 20 '18 at 8:25
Apply unique constraint stackoverflow.com/a/41257827/2630817
– Just code
Nov 20 '18 at 8:25
Can you please show us the table structure (columns and their data types) in the database (including how you've defined your primary key), and the model class
Person in C# ?– marc_s
Nov 20 '18 at 9:14
Can you please show us the table structure (columns and their data types) in the database (including how you've defined your primary key), and the model class
Person in C# ?– marc_s
Nov 20 '18 at 9:14
@marc_s after i applied some of the solutions bellow c# code looks like this public int ID { get; set; } [Index("IX_UniqueConstraint", 1, IsUnique = true)] public string FirstName { get; set; } [Index("IX_UniqueConstraint", 2, IsUnique = true)] public string LastName { get; set; } [Index("IX_UniqueConstraint", 3, IsUnique = true)] public DateTime BirthDate { get; set; } Db - ID is int, LastName is nvarchar, FirstName is nvarchar and BirthDate is datetime
– xcelm
Nov 20 '18 at 9:22
@marc_s after i applied some of the solutions bellow c# code looks like this public int ID { get; set; } [Index("IX_UniqueConstraint", 1, IsUnique = true)] public string FirstName { get; set; } [Index("IX_UniqueConstraint", 2, IsUnique = true)] public string LastName { get; set; } [Index("IX_UniqueConstraint", 3, IsUnique = true)] public DateTime BirthDate { get; set; } Db - ID is int, LastName is nvarchar, FirstName is nvarchar and BirthDate is datetime
– xcelm
Nov 20 '18 at 9:22
add a comment |
2 Answers
2
active
oldest
votes
4
You have already defined your ID column as an identity column and it has been considered as your primary key and will be increased by one any time you insert a new record in your table. This is why you are allowed to insert a duplicate entity. You need to specify which column needs to be declared as PK, either in your model if you are using code-first approach and by using Data Annotation, something like this:
[Key]
public string FirstName { get; set; }
Or by using a unique constraint:
[Index("IX_UniqueConstraint", 1, IsUnique = true)]
public string FirstName { get; set; }
[Index("IX_UniqueConstraint", 2, IsUnique = true)]
public string LastName { get; set; }
[Index("IX_UniqueConstraint", 3, IsUnique = true)]
public DateTime DateOfBirth { get; set; }
You can also use fluent API for this purpose:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Persons>().HasKey(c => new { c.FirstName, c.LastName, c.DateOfBirth });
}
Or if you are using the DB-first approach, you can declare it in your database.
answered Nov 20 '18 at 8:27
S.AkbariS.Akbari
30.3k93673
thanks for swift reply - when using 2nd solution im getting thisexception: "Column 'FirstName' in table 'dbo.People' is of a type that is invalid for use as a key column in an index. " Im not sure what is wrong - my model class - public int ID { get; set; } [Index("IX_UniqueConstraint", 1, IsUnique = true)] public string FirstName { get; set; } [Index("IX_UniqueConstraint", 2, IsUnique = true)] public string LastName { get; set; } [Index("IX_UniqueConstraint", 3, IsUnique = true)] public DateTime BirthDate { get; set; }
– xcelm
Nov 20 '18 at 9:00
@xcelm There is a limitation in SQL Server (up till 2008 R2) that varchar(MAX) and nvarchar(MAX) (and several other types like text, ntext ) cannot be used in indices. You have 2 options: Check this post stackoverflow.com/a/2864174/2946329
– S.Akbari
Nov 20 '18 at 10:10
1
Yes It worked! It seems the problem is with the length of the varchar in DB. I restricted strings to [StringLength(100)] and it is solved. Thanks a lot :)
– xcelm
Nov 20 '18 at 13:28
though it doesn't work as I expected - if I try to put in First Name = "Joe" and LastName = "Smith" then Im getting the violation of PK error. What I need is to check whether the whole row (of course only specified columns) are already in DB and if at least one of them is different, then save the record into DB. Any Idea how to manage that?
– xcelm
Nov 20 '18 at 13:47
@xcelm And what about the last solution with fluent API? did you get the same or didn't try it yet?
– S.Akbari
Nov 20 '18 at 15:22
|
show 3 more comments
2
With EF you would need to do something like this:
[Index("IX_UniqueConstraint", 1, IsUnique = true)]
public string FirstName { get; set; }
[Index("IX_UniqueConstraint", 2, IsUnique = true)]
public string LastName { get; set; }
[Index("IX_UniqueConstraint", 3, IsUnique = true)]
public DateTime DateOfBirth { get; set; }
This will put a unique constraint across the 3 columns.
answered Nov 20 '18 at 8:27
HarambeHarambe
3911311
Hello first of all thanks for the speedy response. Your solution works though it doesn't work as I expected - if I try to put in First Name = "Joe" and LastName = "Smith" then Im getting the violation of PK error. What I need is to check whether the whole row (of course only specified columns) are already in DB and if at least one of them is different, then save the record into DB. Any Idea how to manage that?
– xcelm
Nov 20 '18 at 15:34
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
});
}
});
draft saved
draft discarded
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%2f53388863%2funique-primary-key-using-entity-framework%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
4
You have already defined your ID column as an identity column and it has been considered as your primary key and will be increased by one any time you insert a new record in your table. This is why you are allowed to insert a duplicate entity. You need to specify which column needs to be declared as PK, either in your model if you are using code-first approach and by using Data Annotation, something like this:
[Key]
public string FirstName { get; set; }
Or by using a unique constraint:
[Index("IX_UniqueConstraint", 1, IsUnique = true)]
public string FirstName { get; set; }
[Index("IX_UniqueConstraint", 2, IsUnique = true)]
public string LastName { get; set; }
[Index("IX_UniqueConstraint", 3, IsUnique = true)]
public DateTime DateOfBirth { get; set; }
You can also use fluent API for this purpose:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Persons>().HasKey(c => new { c.FirstName, c.LastName, c.DateOfBirth });
}
Or if you are using the DB-first approach, you can declare it in your database.
answered Nov 20 '18 at 8:27
S.AkbariS.Akbari
30.3k93673
thanks for swift reply - when using 2nd solution im getting thisexception: "Column 'FirstName' in table 'dbo.People' is of a type that is invalid for use as a key column in an index. " Im not sure what is wrong - my model class - public int ID { get; set; } [Index("IX_UniqueConstraint", 1, IsUnique = true)] public string FirstName { get; set; } [Index("IX_UniqueConstraint", 2, IsUnique = true)] public string LastName { get; set; } [Index("IX_UniqueConstraint", 3, IsUnique = true)] public DateTime BirthDate { get; set; }
– xcelm
Nov 20 '18 at 9:00
@xcelm There is a limitation in SQL Server (up till 2008 R2) that varchar(MAX) and nvarchar(MAX) (and several other types like text, ntext ) cannot be used in indices. You have 2 options: Check this post stackoverflow.com/a/2864174/2946329
– S.Akbari
Nov 20 '18 at 10:10
1
Yes It worked! It seems the problem is with the length of the varchar in DB. I restricted strings to [StringLength(100)] and it is solved. Thanks a lot :)
– xcelm
Nov 20 '18 at 13:28
though it doesn't work as I expected - if I try to put in First Name = "Joe" and LastName = "Smith" then Im getting the violation of PK error. What I need is to check whether the whole row (of course only specified columns) are already in DB and if at least one of them is different, then save the record into DB. Any Idea how to manage that?
– xcelm
Nov 20 '18 at 13:47
@xcelm And what about the last solution with fluent API? did you get the same or didn't try it yet?
– S.Akbari
Nov 20 '18 at 15:22
|
show 3 more comments
4
You have already defined your ID column as an identity column and it has been considered as your primary key and will be increased by one any time you insert a new record in your table. This is why you are allowed to insert a duplicate entity. You need to specify which column needs to be declared as PK, either in your model if you are using code-first approach and by using Data Annotation, something like this:
[Key]
public string FirstName { get; set; }
Or by using a unique constraint:
[Index("IX_UniqueConstraint", 1, IsUnique = true)]
public string FirstName { get; set; }
[Index("IX_UniqueConstraint", 2, IsUnique = true)]
public string LastName { get; set; }
[Index("IX_UniqueConstraint", 3, IsUnique = true)]
public DateTime DateOfBirth { get; set; }
You can also use fluent API for this purpose:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Persons>().HasKey(c => new { c.FirstName, c.LastName, c.DateOfBirth });
}
Or if you are using the DB-first approach, you can declare it in your database.
answered Nov 20 '18 at 8:27
S.AkbariS.Akbari
30.3k93673
thanks for swift reply - when using 2nd solution im getting thisexception: "Column 'FirstName' in table 'dbo.People' is of a type that is invalid for use as a key column in an index. " Im not sure what is wrong - my model class - public int ID { get; set; } [Index("IX_UniqueConstraint", 1, IsUnique = true)] public string FirstName { get; set; } [Index("IX_UniqueConstraint", 2, IsUnique = true)] public string LastName { get; set; } [Index("IX_UniqueConstraint", 3, IsUnique = true)] public DateTime BirthDate { get; set; }
– xcelm
Nov 20 '18 at 9:00
@xcelm There is a limitation in SQL Server (up till 2008 R2) that varchar(MAX) and nvarchar(MAX) (and several other types like text, ntext ) cannot be used in indices. You have 2 options: Check this post stackoverflow.com/a/2864174/2946329
– S.Akbari
Nov 20 '18 at 10:10
1
Yes It worked! It seems the problem is with the length of the varchar in DB. I restricted strings to [StringLength(100)] and it is solved. Thanks a lot :)
– xcelm
Nov 20 '18 at 13:28
though it doesn't work as I expected - if I try to put in First Name = "Joe" and LastName = "Smith" then Im getting the violation of PK error. What I need is to check whether the whole row (of course only specified columns) are already in DB and if at least one of them is different, then save the record into DB. Any Idea how to manage that?
– xcelm
Nov 20 '18 at 13:47
@xcelm And what about the last solution with fluent API? did you get the same or didn't try it yet?
– S.Akbari
Nov 20 '18 at 15:22
|
show 3 more comments
4
4
4
You have already defined your ID column as an identity column and it has been considered as your primary key and will be increased by one any time you insert a new record in your table. This is why you are allowed to insert a duplicate entity. You need to specify which column needs to be declared as PK, either in your model if you are using code-first approach and by using Data Annotation, something like this:
[Key]
public string FirstName { get; set; }
Or by using a unique constraint:
[Index("IX_UniqueConstraint", 1, IsUnique = true)]
public string FirstName { get; set; }
[Index("IX_UniqueConstraint", 2, IsUnique = true)]
public string LastName { get; set; }
[Index("IX_UniqueConstraint", 3, IsUnique = true)]
public DateTime DateOfBirth { get; set; }
You can also use fluent API for this purpose:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Persons>().HasKey(c => new { c.FirstName, c.LastName, c.DateOfBirth });
}
Or if you are using the DB-first approach, you can declare it in your database.
answered Nov 20 '18 at 8:27
S.AkbariS.Akbari
30.3k93673
You have already defined your ID column as an identity column and it has been considered as your primary key and will be increased by one any time you insert a new record in your table. This is why you are allowed to insert a duplicate entity. You need to specify which column needs to be declared as PK, either in your model if you are using code-first approach and by using Data Annotation, something like this:
[Key]
public string FirstName { get; set; }
Or by using a unique constraint:
[Index("IX_UniqueConstraint", 1, IsUnique = true)]
public string FirstName { get; set; }
[Index("IX_UniqueConstraint", 2, IsUnique = true)]
public string LastName { get; set; }
[Index("IX_UniqueConstraint", 3, IsUnique = true)]
public DateTime DateOfBirth { get; set; }
You can also use fluent API for this purpose:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Persons>().HasKey(c => new { c.FirstName, c.LastName, c.DateOfBirth });
}
Or if you are using the DB-first approach, you can declare it in your database.
answered Nov 20 '18 at 8:27
S.AkbariS.Akbari
30.3k93673
edited Nov 20 '18 at 8:54
answered Nov 20 '18 at 8:27
S.AkbariS.Akbari
30.3k93673
answered Nov 20 '18 at 8:27
S.AkbariS.Akbari
30.3k93673
answered Nov 20 '18 at 8:27
S.AkbariS.Akbari
30.3k93673
30.3k93673
thanks for swift reply - when using 2nd solution im getting thisexception: "Column 'FirstName' in table 'dbo.People' is of a type that is invalid for use as a key column in an index. " Im not sure what is wrong - my model class - public int ID { get; set; } [Index("IX_UniqueConstraint", 1, IsUnique = true)] public string FirstName { get; set; } [Index("IX_UniqueConstraint", 2, IsUnique = true)] public string LastName { get; set; } [Index("IX_UniqueConstraint", 3, IsUnique = true)] public DateTime BirthDate { get; set; }
– xcelm
Nov 20 '18 at 9:00
@xcelm There is a limitation in SQL Server (up till 2008 R2) that varchar(MAX) and nvarchar(MAX) (and several other types like text, ntext ) cannot be used in indices. You have 2 options: Check this post stackoverflow.com/a/2864174/2946329
– S.Akbari
Nov 20 '18 at 10:10
1
Yes It worked! It seems the problem is with the length of the varchar in DB. I restricted strings to [StringLength(100)] and it is solved. Thanks a lot :)
– xcelm
Nov 20 '18 at 13:28
though it doesn't work as I expected - if I try to put in First Name = "Joe" and LastName = "Smith" then Im getting the violation of PK error. What I need is to check whether the whole row (of course only specified columns) are already in DB and if at least one of them is different, then save the record into DB. Any Idea how to manage that?
– xcelm
Nov 20 '18 at 13:47
@xcelm And what about the last solution with fluent API? did you get the same or didn't try it yet?
– S.Akbari
Nov 20 '18 at 15:22
|
show 3 more comments
thanks for swift reply - when using 2nd solution im getting thisexception: "Column 'FirstName' in table 'dbo.People' is of a type that is invalid for use as a key column in an index. " Im not sure what is wrong - my model class - public int ID { get; set; } [Index("IX_UniqueConstraint", 1, IsUnique = true)] public string FirstName { get; set; } [Index("IX_UniqueConstraint", 2, IsUnique = true)] public string LastName { get; set; } [Index("IX_UniqueConstraint", 3, IsUnique = true)] public DateTime BirthDate { get; set; }
– xcelm
Nov 20 '18 at 9:00
@xcelm There is a limitation in SQL Server (up till 2008 R2) that varchar(MAX) and nvarchar(MAX) (and several other types like text, ntext ) cannot be used in indices. You have 2 options: Check this post stackoverflow.com/a/2864174/2946329
– S.Akbari
Nov 20 '18 at 10:10
1
Yes It worked! It seems the problem is with the length of the varchar in DB. I restricted strings to [StringLength(100)] and it is solved. Thanks a lot :)
– xcelm
Nov 20 '18 at 13:28
though it doesn't work as I expected - if I try to put in First Name = "Joe" and LastName = "Smith" then Im getting the violation of PK error. What I need is to check whether the whole row (of course only specified columns) are already in DB and if at least one of them is different, then save the record into DB. Any Idea how to manage that?
– xcelm
Nov 20 '18 at 13:47
@xcelm And what about the last solution with fluent API? did you get the same or didn't try it yet?
– S.Akbari
Nov 20 '18 at 15:22
thanks for swift reply - when using 2nd solution im getting thisexception: "Column 'FirstName' in table 'dbo.People' is of a type that is invalid for use as a key column in an index. " Im not sure what is wrong - my model class - public int ID { get; set; } [Index("IX_UniqueConstraint", 1, IsUnique = true)] public string FirstName { get; set; } [Index("IX_UniqueConstraint", 2, IsUnique = true)] public string LastName { get; set; } [Index("IX_UniqueConstraint", 3, IsUnique = true)] public DateTime BirthDate { get; set; }
– xcelm
Nov 20 '18 at 9:00
thanks for swift reply - when using 2nd solution im getting thisexception: "Column 'FirstName' in table 'dbo.People' is of a type that is invalid for use as a key column in an index. " Im not sure what is wrong - my model class - public int ID { get; set; } [Index("IX_UniqueConstraint", 1, IsUnique = true)] public string FirstName { get; set; } [Index("IX_UniqueConstraint", 2, IsUnique = true)] public string LastName { get; set; } [Index("IX_UniqueConstraint", 3, IsUnique = true)] public DateTime BirthDate { get; set; }
– xcelm
Nov 20 '18 at 9:00
@xcelm There is a limitation in SQL Server (up till 2008 R2) that varchar(MAX) and nvarchar(MAX) (and several other types like text, ntext ) cannot be used in indices. You have 2 options: Check this post stackoverflow.com/a/2864174/2946329
– S.Akbari
Nov 20 '18 at 10:10
@xcelm There is a limitation in SQL Server (up till 2008 R2) that varchar(MAX) and nvarchar(MAX) (and several other types like text, ntext ) cannot be used in indices. You have 2 options: Check this post stackoverflow.com/a/2864174/2946329
– S.Akbari
Nov 20 '18 at 10:10
1
1
Yes It worked! It seems the problem is with the length of the varchar in DB. I restricted strings to [StringLength(100)] and it is solved. Thanks a lot :)
– xcelm
Nov 20 '18 at 13:28
Yes It worked! It seems the problem is with the length of the varchar in DB. I restricted strings to [StringLength(100)] and it is solved. Thanks a lot :)
– xcelm
Nov 20 '18 at 13:28
though it doesn't work as I expected - if I try to put in First Name = "Joe" and LastName = "Smith" then Im getting the violation of PK error. What I need is to check whether the whole row (of course only specified columns) are already in DB and if at least one of them is different, then save the record into DB. Any Idea how to manage that?
– xcelm
Nov 20 '18 at 13:47
though it doesn't work as I expected - if I try to put in First Name = "Joe" and LastName = "Smith" then Im getting the violation of PK error. What I need is to check whether the whole row (of course only specified columns) are already in DB and if at least one of them is different, then save the record into DB. Any Idea how to manage that?
– xcelm
Nov 20 '18 at 13:47
@xcelm And what about the last solution with fluent API? did you get the same or didn't try it yet?
– S.Akbari
Nov 20 '18 at 15:22
@xcelm And what about the last solution with fluent API? did you get the same or didn't try it yet?
– S.Akbari
Nov 20 '18 at 15:22
|
show 3 more comments
2
With EF you would need to do something like this:
[Index("IX_UniqueConstraint", 1, IsUnique = true)]
public string FirstName { get; set; }
[Index("IX_UniqueConstraint", 2, IsUnique = true)]
public string LastName { get; set; }
[Index("IX_UniqueConstraint", 3, IsUnique = true)]
public DateTime DateOfBirth { get; set; }
This will put a unique constraint across the 3 columns.
answered Nov 20 '18 at 8:27
HarambeHarambe
3911311
Hello first of all thanks for the speedy response. Your solution works though it doesn't work as I expected - if I try to put in First Name = "Joe" and LastName = "Smith" then Im getting the violation of PK error. What I need is to check whether the whole row (of course only specified columns) are already in DB and if at least one of them is different, then save the record into DB. Any Idea how to manage that?
– xcelm
Nov 20 '18 at 15:34
add a comment |
2
With EF you would need to do something like this:
[Index("IX_UniqueConstraint", 1, IsUnique = true)]
public string FirstName { get; set; }
[Index("IX_UniqueConstraint", 2, IsUnique = true)]
public string LastName { get; set; }
[Index("IX_UniqueConstraint", 3, IsUnique = true)]
public DateTime DateOfBirth { get; set; }
This will put a unique constraint across the 3 columns.
answered Nov 20 '18 at 8:27
HarambeHarambe
3911311
Hello first of all thanks for the speedy response. Your solution works though it doesn't work as I expected - if I try to put in First Name = "Joe" and LastName = "Smith" then Im getting the violation of PK error. What I need is to check whether the whole row (of course only specified columns) are already in DB and if at least one of them is different, then save the record into DB. Any Idea how to manage that?
– xcelm
Nov 20 '18 at 15:34
add a comment |
2
2
2
With EF you would need to do something like this:
[Index("IX_UniqueConstraint", 1, IsUnique = true)]
public string FirstName { get; set; }
[Index("IX_UniqueConstraint", 2, IsUnique = true)]
public string LastName { get; set; }
[Index("IX_UniqueConstraint", 3, IsUnique = true)]
public DateTime DateOfBirth { get; set; }
This will put a unique constraint across the 3 columns.
answered Nov 20 '18 at 8:27
HarambeHarambe
3911311
With EF you would need to do something like this:
[Index("IX_UniqueConstraint", 1, IsUnique = true)]
public string FirstName { get; set; }
[Index("IX_UniqueConstraint", 2, IsUnique = true)]
public string LastName { get; set; }
[Index("IX_UniqueConstraint", 3, IsUnique = true)]
public DateTime DateOfBirth { get; set; }
This will put a unique constraint across the 3 columns.
answered Nov 20 '18 at 8:27
HarambeHarambe
3911311
answered Nov 20 '18 at 8:27
HarambeHarambe
3911311
answered Nov 20 '18 at 8:27
HarambeHarambe
3911311
answered Nov 20 '18 at 8:27
HarambeHarambe
3911311
3911311
Hello first of all thanks for the speedy response. Your solution works though it doesn't work as I expected - if I try to put in First Name = "Joe" and LastName = "Smith" then Im getting the violation of PK error. What I need is to check whether the whole row (of course only specified columns) are already in DB and if at least one of them is different, then save the record into DB. Any Idea how to manage that?
– xcelm
Nov 20 '18 at 15:34
add a comment |
Hello first of all thanks for the speedy response. Your solution works though it doesn't work as I expected - if I try to put in First Name = "Joe" and LastName = "Smith" then Im getting the violation of PK error. What I need is to check whether the whole row (of course only specified columns) are already in DB and if at least one of them is different, then save the record into DB. Any Idea how to manage that?
– xcelm
Nov 20 '18 at 15:34
Hello first of all thanks for the speedy response. Your solution works though it doesn't work as I expected - if I try to put in First Name = "Joe" and LastName = "Smith" then Im getting the violation of PK error. What I need is to check whether the whole row (of course only specified columns) are already in DB and if at least one of them is different, then save the record into DB. Any Idea how to manage that?
– xcelm
Nov 20 '18 at 15:34
Hello first of all thanks for the speedy response. Your solution works though it doesn't work as I expected - if I try to put in First Name = "Joe" and LastName = "Smith" then Im getting the violation of PK error. What I need is to check whether the whole row (of course only specified columns) are already in DB and if at least one of them is different, then save the record into DB. Any Idea how to manage that?
– xcelm
Nov 20 '18 at 15:34
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53388863%2funique-primary-key-using-entity-framework%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
docs.microsoft.com/en-us/ef/core/modeling/keys
– JohnB
Nov 20 '18 at 8:25
Apply unique constraint stackoverflow.com/a/41257827/2630817
– Just code
Nov 20 '18 at 8:25
Can you please show us the table structure (columns and their data types) in the database (including how you've defined your primary key), and the model class
Personin C# ?– marc_s
Nov 20 '18 at 9:14
@marc_s after i applied some of the solutions bellow c# code looks like this public int ID { get; set; } [Index("IX_UniqueConstraint", 1, IsUnique = true)] public string FirstName { get; set; } [Index("IX_UniqueConstraint", 2, IsUnique = true)] public string LastName { get; set; } [Index("IX_UniqueConstraint", 3, IsUnique = true)] public DateTime BirthDate { get; set; } Db - ID is int, LastName is nvarchar, FirstName is nvarchar and BirthDate is datetime
– xcelm
Nov 20 '18 at 9:22