none
Index across a computed value and foreign key RRS feed

  • Question

  • I am using ef core 2.1 rc1, I have a model as follows:
    I need to create a unique constraint that represents the following case:
    public class ChildModel
    {
        public int Id { get; set; }
        public string Resource { get; set; }
        public string Role { get; set; }
        public string Category { get; set; }
        public ParentModel Parent { get; set; }
    }
    I already have a unique constraint across Resource, Role, Category and ParentId however I also need a constraint across Resource and Role (when similar), Category and ParentId. For example, the following insert must fail:
    INSERT INTO [ChildModel]
        ([Resource] ,[Role] ,[Category], [ParentId])
    VALUES
        ('Foo', 'Bar', 'Baz', 1),
        ('Foo', 'Bar', 'Bof', 1);
    I'd rather not create a computed column as that changes the model, but even so when its persisted, it cannot be used in an index.

    How can I enforce this at the database level in my version of ef?
    Sunday, May 13, 2018 1:52 PM

All replies

  • Hi Ritmo2k,

    >>I'd rather not create a computed column as that changes the model, but even so when its persisted, it cannot be used in an index. How can I enforce this at the database level in my version of ef?

    According to your description, I am not sure what is your issue, do you want to use two unique constraint with two index, one is four columns and the other one is three columns? or other? could you please describe it in detailed.

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 14, 2018 2:27 AM
    Moderator
  • HI Zhanglong,

    If you look at the sample data describing an insert which should fail, you will see that the existing unique constraint is satisfied. It is for similar values of Resource, Role and ParentId that a different values of Category should not be allowed.

    Monday, May 14, 2018 9:14 AM
  • Hi Ritmo2k,

    According to description, it seems that you want to create a unique constraint with three columns(Resource, Role, ParentId), if so please try the following Fluent API.

    class MyContext : DbContext
    {
        public DbSet<ChildModel> ChildModels { get; set; }
    
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Car>()
                .HasAlternateKey(c => new {c.Resource , c.Role , c.ParentId });
                .
        }
    }

    And add a property named ParentId in your model

    public class ChildModel
    {
        public int Id { get; set; }
        public string Resource { get; set; }
        public string Role { get; set; }
        public string Category { get; set; }
        public int ParentId { get; set; }
        public ParentModel Parent { get; set; }
    
    }

    With related Fluent API.

     modelBuilder.Entity<ParentModel>()
                .HasOne(p => p.ParentModel)
                .WithMany(b => b.ChildModels)
                .HasForeignKey(p => p.ParentId);
    

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 14, 2018 9:40 AM
    Moderator
  • Hi Zhanglong,
    There are many other fields which can vary, so the unique constraint does not work in this implementation. I omitted several other fields which can vary with consistent values for Resource, Role and ParentId (for example if there were a Color field). For example, the following must satisfy integrity:
    INSERT INTO [ChildModel]
        ([Resource] ,[Role] ,[Category], [Color], [ParentId])
    VALUES
        ('Foo', 'Bar', 'Baz', 'Red', 1),
        ('Foo', 'Bar', 'Baz', 'Blue', 1),
        ('Foo', 'Bar', 'Baz', 'Green', 1),
        ('Foo', 'Bar', 'Baz', 'Yellow', 1);
    It is only when you vary [Category] across the above data set "within" similar values for [Resource] ,[Role] and [ParentId].

    It really seems more like a job for a before trigger?

    Thanks for the assistance.
    Monday, May 14, 2018 11:41 AM
  • Hi Ritmo2k,

    >>It really seems more like a job for a before trigger?

    According to your description, it seems that you want unique constraint dynamically, as far as I know, entity framework can't map database dynamically, I would suggest that you achieve your requirement via c# code.

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 15, 2018 12:58 AM
    Moderator