Efcore: Fluent Api - Mapping an index over more than one column across entity types.

[lzocateli00]

I need to generate migration for my entity "Contact" has the "Complex Type -> Address".

So far so good!

However, in the "Contact" entity I need to create a unique index containing "Complex Type -> Address" properties, which is causing migration error.

How can I create this unique index?

The code with example is in:
https://github.com/lincolnzocateli/EFCoreExample/blob/master/Map/ContactMap.cs

```C#
using System.Linq;
using EfCoreExample.Models;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

namespace EfCoreExample.Map
{

public class ContactMap : IEntityTypeConfiguration<Contact>
{


    public void Configure(EntityTypeBuilder<Contact> builder)
    {

        builder.ToTable("Contacts");


        builder.HasKey(x => x.ContactId);

        builder.Property(x => x.ContactId)
            .IsRequired();


        builder.Property(x => x.Name)
            .IsRequired()
            .HasColumnType("varchar(40)");

        builder.Property(x => x.Observation)
            .HasColumnType("varchar(100)");

        builder.OwnsOne(x => x.Address).Property(x => x.AddressType)
            .IsRequired()
            .HasColumnType($"varchar(20)")
            .HasColumnName("Type");

        builder.OwnsOne(x => x.Address).Property(x => x.Street)
            .IsRequired()
            .HasColumnType($"varchar(60)")
            .HasColumnName("Street");

        builder.OwnsOne(x => x.Address).Property(x => x.Neighborhood)
            .HasColumnType($"varchar(60)")
            .HasColumnName("Complement");

        builder.OwnsOne(x => x.Address).Property(x => x.City)
            .IsRequired()
            .HasColumnType($"varchar(60)")
            .HasColumnName("City");

        builder.OwnsOne(x => x.Address).Property(x => x.State)
            .IsRequired()
            .HasColumnType($"varchar(2)")
            .HasColumnName("State");

        builder.OwnsOne(x => x.Address).Property(x => x.Zip)
            .IsRequired()
            .HasColumnType($"varchar(8)")
            .HasColumnName("ZipCode");

        //(1:N)
        builder.HasOne(x => x.Person)
            .WithMany(c => c.Contacts)
            .HasForeignKey(x => x.ContactId)
            .OnDelete(DeleteBehavior.Restrict);



       builder.HasIndex(e => new
           {
               e.Name,
               e.Address.AddressType,
               e.Address.Zip,
           }).HasName("IX_MyIndex")
           .IsUnique();

    }
}

}
```

Exception message:
The properties expression 'e => new <>f__AnonymousType13(Name = e.Name, AddressType = e.Address.AddressType, Zip = e.Address.Zip)' is not valid. The expressionshould represent a property access: 't => t.MyProperty'. When specifying multiple properties use an anonymous type: 't => new { t.MyProperty1, t.MyProperty2 }'. Parameter name: propertyAccessExpression Stack trace: System.ArgumentException: The properties expression 'e => new <>f__AnonymousType13(Name = e.Name, AddressType = e.Address.AddressType, Zip = e.Address.Zip)' isnot valid. The expression should represent a property access: 't => t.MyProperty'. When specifying multiple properties use an anonymous type: 't => new { t.MyProperty1, t.MyProperty2 }'.
Parameter name: propertyAccessExpression
at Microsoft.EntityFrameworkCore.Internal.ExpressionExtensions.GetPropertyAccessList(LambdaExpression propertyAccessExpression)
at Microsoft.EntityFrameworkCore.Metadata.Builders.EntityTypeBuilder1.HasIndex(Expression1 indexExpression)
at EfCoreExample.Map.ContactMap.Configure(EntityTypeBuilder1 builder) in /home/lincoln/Dropbox/EFCoreExample/Map/ContactMap.cs:line 70 at Microsoft.EntityFrameworkCore.ModelBuilder.ApplyConfiguration[TEntity](IEntityTypeConfiguration1 configuration)
at EfCoreExample.Context.EfCoreExampleContext.OnModelCreating(ModelBuilder modelBuilder) in /home/lincoln/Dropbox/EFCoreExample/Context/EfExampleContext.cs:line 37
at Microsoft.EntityFrameworkCore.Infrastructure.ModelCustomizer.Customize(ModelBuilder modelBuilder, DbContext context)
at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelCustomizer.Customize(ModelBuilder modelBuilder, DbContext context)
at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.CreateModel(DbContext context, IConventionSetBuilder conventionSetBuilder, IModelValidator validator)
at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.<>c__DisplayClass5_0.b__0(Object k)
at System.Collections.Concurrent.ConcurrentDictionary2.GetOrAdd(TKey key, Func2 valueFactory)
at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.GetModel(DbContext context, IConventionSetBuilder conventionSetBuilder, IModelValidator validator)
at Microsoft.EntityFrameworkCore.Internal.DbContextServices.CreateModel()
at Microsoft.EntityFrameworkCore.Internal.DbContextServices.get_Model()
at Microsoft.EntityFrameworkCore.Infrastructure.EntityFrameworkServicesBuilder.<>c.b__7_1(IServiceProvider p)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitFactory(FactoryCallSite factoryCallSite, ServiceProvider provider)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor2.VisitCallSite(IServiceCallSite callSite, TArgument argument) at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProvider provider) at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, ServiceProvider provider)
at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor2.VisitCallSite(IServiceCallSite callSite, TArgument argument) at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProvider provider) at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
at Microsoft.Extensions.DependencyInjection.ServiceProvider.<>c__DisplayClass22_0.b__0(ServiceProvider provider)
at Microsoft.Extensions.DependencyInjection.ServiceProvider.GetService(Type serviceType)
at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)
at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredServiceT
at Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()
at Microsoft.EntityFrameworkCore.DbContext.get_InternalServiceProvider()
at Microsoft.EntityFrameworkCore.DbContext.Microsoft.EntityFrameworkCore.Infrastructure.IInfrastructure.get_Instance()
at Microsoft.EntityFrameworkCore.Infrastructure.AccessorExtensions.GetServiceTService
at Microsoft.EntityFrameworkCore.Design.Internal.DbContextOperations.CreateContext(String contextType)
at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.AddMigration(String name, String outputDir, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.AddMigrationImpl(String name, String outputDir, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.AddMigration.<>c__DisplayClass0_1.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)

Steps to reproduce

Generate migration for the first time:
/> dotnet ef migrations add v1.0.0

Further technical details

EF Core version:
Microsoft.EntityFrameworkCore.Design version 2.0.1
Microsoft.EntityFrameworkCore.Tools.DotNet version 2.0.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer version 2.0.1
Operating system: Linux Debian 9
IDE: VSCODE

[ajcvickers]

Notes from triage: putting this on the backlog to consider supporting indexes and table splitting together. The implementation will likely require an internal store-side model.

@lincolnzocateli The workaround is to create the index in the migration, rather than in the model.

[lzocateli00]

Ok I'm following the backlog

[AndriySvyryd]

@lincolnzocateli Another workaround is to create a shadow property on the owned type and map it to the same column:
```C#
builder.OwnsOne(x => x.Address, ab =>
{
ab.Property("Name")
.HasColumnName("Name")
.IsRequired()
.HasColumnType("varchar(40)");

ab.HasIndex(
    "Name",
    "AddressType",
    "Zip",
).HasName("IX_MyIndex")
    .IsUnique();

}

Though since `Name` is required you will have to populate it manually when adding a new `Address`:
```C#
context.Entry(contact).Reference(e => e.Address).TargetEntry.Property<string>("Name")
    .CurrentValue = contact.Name;

[lzocateli00]

@AndriySvyryd Thanks for the tip, I tried to do it, but I did not find a viable solution for having to populate it manually when adding a new Address.

Many people in the dotnet community in Brazil have asked me about a solution to this, for now we are waiting for the backlog.

[AndriySvyryd]

@lincolnzocateli Why didn't my workaround for populating the shadow property work for you? Is that you don't have the context available when a new address is added? If so you could go through all entries and find which ones were added:
```C#
foreach (var entityEntry in context.ChangeTracker.Entries())
{
if (entityEntry.Entity.GetType() != typeof(Contact))
{
continue;
}

var addressEntry = entityEntry.Reference("Address").TargetEntry;
if (addressEntry.State == EntityState.Added
    || addressEntry.State == EntityState.Detached)
{
    addressEntry.Property("Name").CurrentValue = entityEntry.Property("Name").CurrentValue;
}

}
```

[lzocateli00]

@AndriySvyryd, I'm doing well with your solution,

thanks for the help.

[divega]

We wanted to keep this open in the backlog.

[dylinmaust]

@AndriySvyryd does this workaround require an additional column in the table? I've adapted your above code to my own solution by adding a shadow property on "Name", but it's creating two columns, one prefixed with "MyTableName_".

[smitpatel]

@dylinmaust - It does not require additional column. The point is you create a shadow property in your complex type and configure it using HasColumnName to use the same column as the property which is in your entity type. So they both would share the column in database without requiring additional column.

[dylinmaust]

@smitpatel Thanks for the clarification. Here's my configuration:

    public class PartEntityConfiguration : IEntityTypeConfiguration<Part>
    {
            public void Configure(EntityTypeBuilder<Part> modelBuilder)
            {
                    modelBuilder.HasKey(p => p.Id);

                    modelBuilder
                            .Property(p => p.Name)
                            .IsRequired()
                            .HasMaxLength(100);

                    modelBuilder
                            .HasIndex(p => p.Name)
                            .IsUnique();

                    modelBuilder
                            .OwnsOne(p => p.MountConfiguration, p => 
                            {
                                    // Workaround for unique index that crosses owned type boundaries
                                    // https://github.com/aspnet/EntityFrameworkCore/issues/11336#issuecomment-389670812
                                    p.Property<string>("Name")
                                            .HasColumnName("Name")
                                            .IsRequired()
                                            .HasMaxLength(100);

                                    p.HasIndex("Name", "MountingTypeId", "MountingOrientationId")
                                            .IsUnique();
                            });
            }
    }

which is generating something like this:

     migrationBuilder.CreateTable(
        name: "Part",
        columns: table => new
        {
                Id = table.Column<int>(nullable: false)
                        .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
                Part_Name = table.Column<string>(maxLength: 100, nullable: false),
                Name = table.Column<string>(maxLength: 100, nullable: false)
        })

[smitpatel]

Can you make following change and see if it works for you?
C# modelBuilder .Property(p => p.Name) .HasColumnName("Name") // <<-- this line is added by me .IsRequired() .HasMaxLength(100);

[dylinmaust]

That was an embarrassingly simple solution. Thank you! And thanks for the Markdown help 👍

[RamunasAdamonis]

Looking forward to see this feature added.

[sandersaares]

Lack of this discourages me from using owned types. Has this been considered and rejected from 3.0 scope? Is "backlog" equivalent to "maybe after 3.0" in this sense?

I am also interested in owned property referencing support in ForSqlServerInclude

[xu-lei-richard]

+1 need this

[xaviervv]

Any news?

[AndriySvyryd]

There are many other improvements that have bigger impact than this, so we won't get to this one any time soon. Use the 👍 reaction on the first post to indicate your support, this is one of the ways we measure impact.

[ivanrodriguezfernandez]

Any update on when this is going to be resolved?

[xu-lei-richard]

Any news?

[roji]

This issue is in the backlog milestone, which means we don't plan to work on it for 5.0; once that's released, we'll reexamine which issues can make it into 6.0.

Note that this issue has an easy workaround - simply create the index in your migration - which means we generally don't consider this to be extremely high priority.