0

I have a table in SQL Server named EffectiveRole that has three columns

    [UserID] [int] NOT NULL,
    [DealID] [int] NULL,
    [RoleID] [int] NOT NULL

and it has an index IX_EffectiveRole on columns

([UserID] ASC,
[DealID] ASC,
[RoleID] ASC

My POCO class for this table is

[Table("EffectiveRole")]
public partial class EffectiveRole
{
        [Key]
        [Column(Order = 0)]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public int UserID { get; set; }

        [Key]
        [Column(Order = 1)]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public int? DealID { get; set; }

        [Key]
        [Column(Order = 2)]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public int RoleID { get; set; }
}

When I try to insert an entity by this code

var context = new Ema171Entities();
var effectiveRole = new EffectiveRole { DealID = null, UserID = 85, RoleID = 14 };            
context.EffectiveRoles.Add(effectiveRole);         
context.SaveChanges();

I get an error:

The changes to the database were committed successfully, but an error occurred while updating the object context. The ObjectContext might be in an inconsistent state. Inner exception message: The object state cannot be changed. This exception may result from one or more of the primary key properties being set to null. Non-Added objects cannot have null primary key values

How can I solve this? Any ideas?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ramanmittal
  • 91
  • 1
  • 11
  • `DealID` is null and is part of your composite PK – ocuenca Jun 26 '17 at 14:16
  • You **cannot** have nullable columns in your **primary key** in SQL Server (those columns marked with `[Key]` in your EF model) - you need to avoid this. – marc_s Jun 26 '17 at 14:18
  • Does the table in the database have a Primary Key on it? An Index is not a Primary Key. From what you've shown us of the table and class, you shouldn't have the `[Key]` attribute on those properties. – krillgar Jun 26 '17 at 14:22
  • 1
    A unique index and primary key are different things. See https://stackoverflow.com/questions/26837800/is-it-possible-to-set-a-unique-constraint-using-entity-framework-code-first for unique indexes. Note that SQL Server treats `NULL` as a value for uniqueness purposes. – Dan Guzman Jun 26 '17 at 14:31
  • @marc_s It is required that Table `EffectiveRole` Has unique combination of `UserID`, `DealID` and `RoleID` but `DealID` is null that's why I can not create composite primary key and using index. I have marked `DealID` as key because if there are two entity having same `UserID` and `RoleID` but different `DealID` then ef treat these entities are equal because their primary keys are equal – ramanmittal Jun 26 '17 at 15:12

0 Answers0