C# Fluent API Map two properties to same column

user_9745732 0
Viewed 858

My problem is actually quite straight forward:

This is the MySQL table "ClubCategory". As you can see it links a club to a category.

+------------+------+------+-----+---------+-------+
| Field      | Type | Null | Key | Default | Extra |
+------------+------+------+-----+---------+-------+
| CategoryId | int  | NO   | PRI | NULL    |       |
| ClubId     | int  | NO   | PRI | NULL    |       |
+------------+------+------+-----+---------+-------+

The problem is as follows: My backing C# class has to implement an Interface that specifies an additional property named OtherId where OtherId is just an alias for CategoryId.

The class looks as follows

public class ClubCategory : IClubFilterLinker
{
    private int _categoryId;

    public int ClubId { get; set; }

    public int CategoryId
    {
        get => _categoryId;
        set => _categoryId = value;
    }

    public int OtherId
    {
        get => _categoryId;
        set => _categoryId = value;
    }
}

I basically need to be able to use either ClubCategory.CategoryId or ClubCategory.OtherId to access the same database column CategoryId.

The Fluent API mapping I tried looks like this:

modelBuilder.Entity<ClubCategory>()
    .Property(nameof(_categoryId))
    .HasColumnName("CategoryId")
    .HasColumnType("INT")
    .IsRequired();

modelBuilder.Entity<ClubCategory>()
    .Property(cc => cc.CategoryId)
    .HasField(nameof(_categoryId))
    .UsePropertyAccessMode(PropertyAccessMode.Field);

modelBuilder.Entity<ClubCategory>()
    .Property(cc => cc.OtherId)
    .HasField(nameof(_categoryId))
    .UsePropertyAccessMode(PropertyAccessMode.Field);

However the resulting generated MySQL query when accessing an instance of this class

SELECT `c`.`ClubId`, `c`.`CategoryId`, `c`.`OtherId`, `c`.`CategoryId`
FROM `club2category` AS `c`

is obviously completely broken. Not only does it specify CategoryId twice but it also tries to access a fictional column named OtherId that doesn't exist in the database :|

So what do I need to change in Fluent API to successfully map both properties to the same MySQL column? Or is it possible at all? Any help would be hugely appreciated :)

pomelo-entityframeworkcore-mysql fluent entity-framework mysql c#
1 Answers

Going through all the intellisense suggestions of the PropertyBuilder one method caught my attention: ValueGeneratedOnAddOrUpdate().

From the documentation:

Configures a property to have a value generated when saving a new or existing entity.

Adding this to the PropertyBuilder chain seems to tell the Entity Framework that the value is automatically generated by the database upon saving. Therefore EF excludes this property from it's query during insert or update. Essentially allowing you to map multiple of these "readonly" properties to the same database column and use them in your queries which is exactly what I asked for in my question. This also allows me to remove the private backing field.

My ClubCategory class now looks like this:

public class ClubCategory : IClubFilterLinker
{
    public int ClubId { get; set; }

    public int CategoryId { get; set; }

    public int OtherId { get; set; }
}

And this is the corresponding Fluent API mapping:

modelBuilder.Entity<ClubCategory>()
    .Property(cc => cc.CategoryId)
    .HasColumnName("CategoryId")
    .HasColumnType("INT")
    .IsRequired();

modelBuilder.Entity<ClubCategory>()
    .Property(cc => cc.OtherId)
    .HasColumnName("CategoryId")
    .HasColumnType("INT")
    // Prevent EF from using this property in insert / update statements.
    .ValueGeneratedOnAddOrUpdate();
user_97457320
Related