Create Composite Index using Entity Property and Reference Shadow Property

[kejvi-doko]

I am trying to create a composite unique constraint using one column from entity property (AssessmentId) and the other from an owned complex type property (User.Code) .

Model Declaration

    public class UserAssessment : BaseEntity
    {
        public UserReference User { get; set; }
        public Guid AssessmentId { get; set; }
        public virtual Assesment Assesment { get; set; }
        public bool IsCompleted { get; set; }
        public bool IsActive { get; set; }
        public DateTime StartDate { get; set; }
        public DateTime Deadline { get; set; }
        public ICollection<Answer> Answers { get; set; }
    }


    public class UserReference : ValueObject
    {
        public Guid Code { get; set; }
        public string UserName { get; set; }
        public string FullName { get; set; }
        public string Email { get; set; }

        protected override IEnumerable<object> GetAtomicValues()
        {
            return new object[] { Code, UserName, FullName, Email };
        }
    }

    public class Assesment : BaseEntity
    {
        public string Name { get; set; }
        public TenantReference Tenant { get; set; }
        public virtual AssesmentCategory AssesmentCategory { get; set; }
        public Guid AssesmentCategoryId { get; set; }
        public ICollection<AssesmentQuestion> AssesmentQuestions { get; set; }
        public ICollection<UserAssessment> UserAssessments { get; set; }
    }

EF Core Entity Configuration

        protected override void PostConfigure(EntityTypeBuilder<UserAssessment> builder)
        {
            builder.HasKey(x => x.Id);
            builder.Property(x => x.Id).HasColumnType("varchar(255)");
            builder.Property(x => x.AssessmentId).HasColumnType("varchar(255)");

            builder.OwnsOne(x => x.User, cfg =>
            {
                cfg.Property<Guid>(t => t.Code).HasColumnType("varchar(255)");
                
            });

            // Test 01
            //builder.HasIndex(x => new {x.User, x.AssessmentId})
            //    .IsUnique();

            // Test 02
            // builder.HasAlternateKey("User.Code", "AssessmentId");

           // Test 03
            //builder.HasIndex(x=>new {x.AssessmentId,x.User.Code})
            //    .IsUnique();

           // Test 04
            //builder.HasIndex("AssessmentId", "Code")
            //    .IsUnique();

            builder.Property(x => x.IsActive).HasColumnType("bit");
            builder.Property(x => x.IsCompleted).HasColumnType("bit");
            builder.Property(x => x.StartDate);
            builder.Property(x => x.Deadline);

            builder.HasOne(x => x.Assesment)
                   .WithMany(x => x.UserAssessments)
                   .HasForeignKey(x => x.AssessmentId);

            
        }

** Migration Command **

Add-Migration UniqueConstraint

The generated index should look something like this:

CREATE UNIQUE INDEX UserAssessments_AssessmentId_IDX USING BTREE ON DemoDb.UserAssessments (AssessmentId,User_Code);

I have tried different ways to declare the unique index but still migration fails with the following errors:

• 'User' cannot be used as a property on entity type 'UserAssessment' because it is configured as a navigation.
• The property 'Code' cannot be added to the type 'UserAssessment' because there was no property type specified and there is no corresponding CLR property or field. To add a shadow state property the property type must be specified
• The properties expression 'x => new <>f__AnonymousType3`2(AssessmentId = x.AssessmentId, Code = x.User.Code)' is not valid. The expression should represent a simple property access: 't => t.MyProperty'. When specifying multiple properties use an anonymous type: 't => new { t.MyProperty1, t.MyProperty2 }'.
  Parameter name: propertyAccessExpression
• 'User' cannot be used as a property on entity type 'UserAssessment' because it is configured as a navigation.

Am I doing something wrong on the declaration of index or this is a missing feature on EF Core and if so is there any way around this issue.

Further technical details

EF Core version: 2.2.6
Database provider: (MySql.Data.EntityFrameworkCore 8.0.18)
Target framework: (.Net.Core 2.2)
Database: MySQL 8+ (InnoDB)
Operating system: Windows 10 PRO
IDE: Visual Studio Enterprise 2019 (16.3.7)

Thanks

[ajcvickers]

@AndriySvyryd Is there a duplicate for this?

[AndriySvyryd]

Duplicate of #11336