"The 'List<NNN>' property 'PPP' could not be mapped ..." error on trying to seed the model with relations in Entity Framework

Question

Entity:

using CommonSolution.Entities.Task;
using Microsoft.EntityFrameworkCore;

namespace EntityFramework.Models;

[System.ComponentModel.DataAnnotations.Schema.Table("tasks_custom_folders")]
[Microsoft.EntityFrameworkCore.EntityTypeConfiguration(typeof(TaskCustomFolderModel.Configuration))]
public class TaskCustomFolderModel
{

  /* [ Theory ] `Guid.NewGuid()` return the string of 36 characters. See https://stackoverflow.com/a/4458925/4818123 */
  [System.ComponentModel.DataAnnotations.Key]
  [System.ComponentModel.DataAnnotations.MaxLength(36)]
  public string ID { get; set; } = Guid.NewGuid().ToString();

  
  /* ━━━ Title ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ */
  [System.ComponentModel.DataAnnotations.MaxLength(TaskCustomFolder.Title.MAXIMAL_CHARACTERS_COUNT)]
  public string Title { get; set; } = null!;
  
  
  /* ━━━ Relatives ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ */
  /* ─── Parent ───────────────────────────────────────────────────────────────────────────────────────────────────── */
  /* [ Theory ] Although generally the explicit specifying of navigation property is optional, it is required for seeding. */
  /* [ Theory ] `Guid.NewGuid()` return the string of 36 characters. See https://stackoverflow.com/a/4458925/4818123 */
  [System.ComponentModel.DataAnnotations.MaxLength(36)]
  public string? ParentID { get; set; }
  
  public TaskCustomFolderModel? Parent { get; set; }
  
  
  /* ─── Children ─────────────────────────────────────────────────────────────────────────────────────────────────── */
  public List<TaskCustomFolderModel> Children { get; set; } = [];
  
  
  /* ━━━ Order Among Siblings ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ */
  public uint OrderAmongSiblings { get; set; }
  
  
  /* ━━━ Configuration ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ */
  public class Configuration : Microsoft.EntityFrameworkCore.IEntityTypeConfiguration<TaskCustomFolderModel>
  {

    private const string TABLE_NAME = "TasksCustomFolders";

    public void Configure(
      Microsoft.EntityFrameworkCore.Metadata.Builders.EntityTypeBuilder<TaskCustomFolderModel> builder)
    {

      builder.ToTable(Configuration.TABLE_NAME);

      builder.
          Property(taskCustomFolderModel => taskCustomFolderModel.Title).
          IsRequired(TaskCustomFolder.Title.IS_REQUIRED);

      builder.
          Property(taskCustomFolderModel => taskCustomFolderModel.Parent).
          IsRequired(TaskCustomFolder.Parent.IS_REQUIRED);
      
      builder.
          Property(taskCustomFolderModel => taskCustomFolderModel.ParentID).
          IsRequired(TaskCustomFolder.Parent.IS_REQUIRED);
      
      builder.
          Property(taskCustomFolderModel => taskCustomFolderModel.Children).
          IsRequired(TaskCustomFolder.Children.IS_REQUIRED);
      
      builder.
          Property(taskCustomFolderModel => taskCustomFolderModel.OrderAmongSiblings).
          IsRequired(TaskCustomFolder.OrderAmongSiblings.IS_REQUIRED);
    }
  }
}

Error when I'm trying to seed the database by calling base.Database.EnsureCreated();:

System.InvalidOperationException: 'The 'List' property 'TaskCustomFolderModel.Children' could not be mapped because the database provider does not support this type. Consider converting the property value to a type supported by the database using a value converter. See https://aka.ms/efcore-docs-value-converters for more information. Alternately, exclude the property from the model using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.'

According the error, the database provider does not support something, however below example which I have used for the reference and which also has relations works fine!

public class MenuItem
{
    public int Id { get; set; }
    public string? Title { get; set; }
    public int? ParentId { get; set; }
    public MenuItem? Parent { get; set; }
    public List<MenuItem> Children { get; set; } = new();
}

using Microsoft.EntityFrameworkCore;
 
public class ApplicationContext : DbContext
{
    public DbSet<MenuItem> MenuItems { get; set; } = null!;
 
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite("Data Source=helloapp.db");
    }
}

using (ApplicationContext db = new ApplicationContext())
{
    db.Database.EnsureDeleted();
    db.Database.EnsureCreated();
 
    MenuItem file = new MenuItem { Title = "File" };
    MenuItem edit = new MenuItem { Title = "Edit" };
    MenuItem open = new MenuItem { Title = "Open", Parent = file };
    MenuItem save = new MenuItem { Title = "Save", Parent = file };
 
    MenuItem copy = new MenuItem { Title = "Copy", Parent = edit };
    MenuItem paste = new MenuItem { Title = "Paste", Parent = edit };
 
    db.MenuItems.AddRange(file, edit, open, save, copy, paste);
    db.SaveChanges();
}

using (ApplicationContext db = new ApplicationContext())
{
    var menuItems = db.MenuItems.ToList();
    Console.WriteLine("All Menu:");

    foreach (MenuItem m in menuItems)
    {
        Console.WriteLine(m.Title);
    }

    Console.WriteLine();
    
    var fileMenu = db.MenuItems.FirstOrDefault(m => m.Title == "File");

    if (fileMenu != null)
    {
        Console.WriteLine(fileMenu.Title);

        foreach (var m in fileMenu.Children)
        {
            Console.WriteLine($"---{m.Title}");
        }
    }
}

So it looks like the problem in something else, so for now I will not say which DB provider I am using.

Database context classes with seeding code:

public class RemoteDatabaseContext : DatabaseContext
{
  public RemoteDatabaseContext()
  {
    // base.Database.EnsureDeleted();
    base.Database.EnsureCreated();
  }
  
  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
  {
    base.OnConfiguring(optionsBuilder);

    optionsBuilder.UseNpgsql("Host=localhost;Port=5432;Username=postgres;Password=pass1234");
  }

  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
    modelBuilder.
        Entity<TaskCustomFolderModel>().
        HasData(TaskCustomFolderModel.FromBusinessRulesEntities(SampleTasksCustomFoldersRepository.TaskCustomFolders));
  }
}

public abstract class DatabaseContext : Microsoft.EntityFrameworkCore.DbContext
{
  public DbSet<TaskModel> TasksModels { get; internal set; } = null!;
  public DbSet<TaskCustomFolderModel> TaskCustomFoldersModels { get; internal set; } = null!;
  public DbSet<LocationModel> LocationModels { get; internal set; } = null!;
  
  public DbSet<PersonModel> PeopleModels { get; internal set; } = null!;
}

Why you are set IsRequired via fluent API while maximal characters count via attributes?

Short answer: because of the Clean Architecture and because TaskCustomFolderModel and other Entity Framework models must change due to changes in Business Rules without manual code editing while possible.

Long answer:

According the Clean Architecture, the Business Rules must NOT depend on any frameworks while the TaskCustomFolderModel depends. So, besides the TaskCustomFolderModel class there is the one from the business rules:

public class TaskCustomFolder
{
  public required string ID { get; init; }
  
  /* ━━━ Title ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ */
  public required string title { get; set; }
  
  public abstract record Title
  {
    public const bool IS_REQUIRED = true;
    public const byte MINIMAL_CHARACTERS_COUNT = 1;
    public const byte MAXIMAL_CHARACTERS_COUNT = Byte.MaxValue;
  }
  
  /* ━━━ Relatives ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ */
  /* ─── Parent ───────────────────────────────────────────────────────────────────────────────────────────────────── */
  public TaskCustomFolder? parent { get; set; }
  
  public abstract record Parent
  {
    public const bool IS_REQUIRED = false;
  }
  
  /* ─── Children ─────────────────────────────────────────────────────────────────────────────────────────────────── */
  public List<TaskCustomFolder> children { get; set; } = [];
  
  public abstract record Children
  {
    public const bool IS_REQUIRED = false;
  }
  
  /* ━━━ Order Among Siblings ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ */
  public required uint orderAmongSiblings { get; set; }
  
  public abstract record OrderAmongSiblings
  {
    public const bool IS_REQUIRED = true;
    public const uint MINIMAL_VALUE = 1;
    public const uint MAXIMAL_VALUE = UInt32.MaxValue;
  }
  
  /* ━━━ Path ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ */
  public string path
  {
    get
    {
      TaskCustomFolder currentDepthLevel = this;
      List<string> pathSegments = [ currentDepthLevel.title ];

      while (currentDepthLevel.parent is not null)
      {
        currentDepthLevel = currentDepthLevel.parent;
        pathSegments.Add(currentDepthLevel.title);
      }

      return String.Join("/", pathSegments);
    }
  }
}

If we'll change MAXIMAL_CHARACTERS_COUNT of some property, no need to edit the TaskCustomFolderModel because it refers to the business rules via attribute:

public class TaskCustomFolderModel
{
  // ...   [System.ComponentModel.DataAnnotations.MaxLength(TaskCustomFolder.Title.MAXIMAL_CHARACTERS_COUNT)]
  public string Title { get; set; } = null!;
}

However, there is no the Required attribute which accepts the parameter like TaskCustomFolder.Children.IS_REQUIRED. So it could be done only via fluent API (or creating of the new attribute).

Answer 1

It's actually quite simple, but the exception message could be a lot more helpful.

The exception is caused by

builder.
   Property(taskCustomFolderModel => taskCustomFolderModel.Children).
   IsRequired(TaskCustomFolder.Children.IS_REQUIRED);

This makes EF treat Children as a reference property to another type, just like Parent is. However, List<TaskCustomFolderModel> is not a mapped type. Although that is quite obvious the exception message could state that IsRequired can't be applied to collection properties.

The configuration will be valid if you remove IsRequired calls from collection properties.

You may object that the property should be required, but from the viewpoint of data modelling that requirement doesn't make sense. There's no way to enforce a 1:1..n relationship in a SQL-based database, so EF doesn't offer configuration statements to support it. Making the collection not null is nothing but an application convenience. As for stored data, there's no difference between an empty or a null collection.