How to create unique constraint with multiple columns and one of the columns is Object as foreign key on ef.core 3.1?

Question

This is my entity class :

public class TenantUsageLog
{
    [Key]
    public Guid Id { get; set; }

    public Tenant Tenant { get; set; }

    [DataType(DataType.Date)]
    public DateTime LogDate { get; set; }

    public int DatabaseRowCount { get; set; }

    public int DailyTraffic { get; set; }

}

I want to create a unique constraint with Tenant object and LogDate

I am adding this code on ApplicationDbContext>OnModelCreating :

       builder.Entity<TenantUsageLog>().HasIndex(p => new { p.Tenant.Id, p.LogDate }).IsUnique();

It returns this error during add-migration :

The properties expression 'p => new <>f__AnonymousType5`2(Id = p.Tenant.Id, LogDate = p.LogDate)' 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 'propertyAccessExpression')

I have also tried this :

       builder.Entity<TenantUsageLog>().HasIndex(p => new { p.Tenant, p.LogDate }).IsUnique();

This time,it returns with this error :

The property 'TenantUsageLog.Tenant' is of type 'Tenant' which is not supported by current database provider. Either change the property CLR type or ignore the property using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.

So,could you please help me to handle this problem ? I want to create a unique constraint which helps me to not add same data contains Tenant.Id and LogDate.

Thanks.

There should be an overload of HasIndex that accepts string array as property names. You can use that overload and you may specify shadow properties. It should work on later versions of EF core but not so sure about 3.1. You can give it a try. — Eldar
– Eldar, Commented Feb 9, 2023 at 8:39

Guru Stron · Accepted Answer · 2023-02-09 08:49:32Z

1

Expose the tenant id directly and use it:

public class TenantUsageLog
{
    [Key]
    public Guid Id { get; set; }

    public Guid TenantId { get; set; }

    public Tenant Tenant { get; set; }

    [DataType(DataType.Date)]
    public DateTime LogDate { get; set; }

    public int DatabaseRowCount { get; set; }

    public int DailyTraffic { get; set; }

}

builder.Entity<TenantUsageLog>().HasIndex(p => new { p.TenantId, p.LogDate }).IsUnique();

Also you can try using HasIndex overload accepting string params and pass the column names:

builder.Entity<TenantUsageLog>().HasIndex("TenantId", "LogDate").IsUnique();

edited Feb 9, 2023 at 8:49

answered Feb 9, 2023 at 8:31

Guru Stron

150k11 gold badges186 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tomas Shelby Over a year ago

Thanks, this works, but I hope my team lead except this,because He does not want to use Id, he wants to use object for foreign key relations.

Guru Stron Over a year ago

@TomasShelby added another possible approach. Also you can add index via direct SQL for example by manually changing the migration.

Tomas Shelby Over a year ago

This is : builder.Entity<TenantUsageLog>().HasIndex("TenantId", "LogDate").IsUnique(); better solution for me , because I do not need to expose tenantId. It is tried and worked fine. Thanks a lot.

Collectives™ on Stack Overflow

How to create unique constraint with multiple columns and one of the columns is Object as foreign key on ef.core 3.1?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related