locked
Composite index in efcore 2.0 RRS feed

  • Question

  • User310927902 posted

    I have two models, model A contains one or more of Model B. Model B has a property, name, which must be unique only per model A.

    modelBuilder.Entity<ModelB>()
        .HasIndex(m => new {m.ModelAId, m.Name})
        .IsUnique();

    This does not produce the desired composite index, but rather two, a unique on Name and non-unique on fk. How can I accomplish this?

    Thanks!

    Saturday, February 3, 2018 11:27 PM

All replies

  • User-832373396 posted

    Hi ritmo2k,

    ritmo2k

    els, model A contains one or more of Model B. Model B has a property, name, which must be unique only per model A.

    Sir, I  am trying to reproduce your model relationship.

    and test the example what you tried, but it can't do on my local.

    Full code

    public class ClassB
        {
            [Key]
            public int ClassBID { get; set; }
            public string ClassBName { get; set; }
            public virtual ClassA ClassA { get; set; }
    
        }
        public class ClassA
        {
            [Key]
            public int ClassAID { get; set; }
            public string ClassAName { get; set; }
            public virtual ICollection<ClassB> ClassBs { get; set; }
        }
     public DbSet<ClassB> ClassBs { get; set; }
            public DbSet<ClassA> ClassAs { get; set; }
    
            //protected override void OnModelCreating(ModelBuilder modelBuilder)
            //{
              
            //}
            protected override void OnModelCreating(ModelBuilder builder)
            {
                builder.Entity<ClassB>()
                  .HasIndex(p => new { p.ClassA, p.ClassBID });
                base.OnModelCreating(builder);

    it will alert error as shown below

       at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
       at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
    Cannot call Property for the property 'ClassA' on entity type 'ClassB' because it is configured as a navigation property.
    Property can only be used to configure scalar properties.

    So we can't add composite index between 2 models as the error tells.

    With regards, Angelina Jolie

    Monday, February 5, 2018 7:50 AM
  • User310927902 posted

    The keys are both integer type, that is why it works however it looks like you edited your post as the notification email contained the correct answer. I was not thinking at all, it was a composite primary key that I needed as you described before the edit.

    Thanks:)

    Monday, February 5, 2018 7:40 PM
  • User310927902 posted

    Actually, I still have not arrived at a solution, imagine the following sample data for the table describing ModelB:

    Id ModelAId Name
    1 1 aa
    2 1 ab
    3 1 ab
    4 2 aa
    5 2 ab
    6 2 ac

    In the above, rows 2 and 3 need to produce an error, all rows with a common FK must have unique names. However, once the FK changes, they can repeat.

    That is actually what I am after.

    Thanks.

    Monday, February 5, 2018 8:39 PM
  • User-832373396 posted

    Hi ritmo2k,

    At first, after tested, then maybe we need to give up the Composite index between 2 models.

    • Next, 

    Composite key example

    Key A   Key B 
    1      1 
    2      2 
    3      3 

    Then abled data

    11 
    12 
    13 
    21 
    22 
    23 
    31 
    32 
    33

    and It met your requirement.

    • Next.

    The point is how to bind Composite key between two Models.

    I found an  example for you, please refer to

    public class NotificationMessageDevice
    {
        [Column(Order = 0), Key, ForeignKey("NotificationMessage")]
        public int NotificationMessage_ID { get; set; }
    
        [Column(Order = 1), Key, ForeignKey("Device")]
        public int Device_ID { get; set; }
        [Column(Order = 2), Key, ForeignKey("Device")]
        public string Device_UDID { get; set; }
        [Column(Order = 3), Key, ForeignKey("Device")]
        public string Device_ApplicationKey { get; set; }
    
        public virtual Device Device { get; set; }
        public virtual NotificationMessage NotificationMessage { get; set; }
    }

    from  https://stackoverflow.com/questions/14873169/creating-composite-key-entity-framework/14873545#14873545 

    Hope it is helpful to you and Good Luck :)

    Bests,

    Jolie

    Tuesday, February 6, 2018 9:57 AM