Error when running a recursive SQL with EntityFramework Core in Mariadb

Question

I need help on how to use/build the following in Entity Framewore Core (.NET 7).

Among others, i have one class as follows :

public class ApplicationModel{
    public int Id { get; set; }
    public string AppName { get; set; }
    public List<ApplicationModel>? Parent { get; set; }
    public List<ApplicationModel>? Son { get; set; }
}

This reflexive relationship leads to the creation of two tables (I'm using MariaDb/Pomelo 7.0.0-silver.1)

1. Table ApplicationModel
2. Table ApplicationModelApplicationModel

The goal is to run a recursive sql to build a tree showing the parent to son relation.

Please find here an example of schema and query.

In the results, you'll find that

• A is the parent of B,
• B is the parent of C and D
• D is parent of E and so on.

Question : How may i code this in my Service.cs ?

I've tried to (Using this and this) create a mariadb procedure (Name GET_DEPENDENCIES, working properly in HeidiSql) but in my Service.cs,

         var result = await _dbContext.ApplicationModels
            .FromSqlInterpolated(
            $"CALL `GET_DEPENDENCIES` (1)")
            .ToListAsync();

I have the following error :

System.ArgumentException: An item with the same key has already been added. Key: AppName
   at System.Collections.Generic.Dictionary`2.TryInsert(TKey key, TValue value, InsertionBehavior behavior)
   at System.Collections.Generic.Dictionary`2.Add(TKey key, TValue value)
   at System.Linq.Enumerable.ToDictionary[TSource,TKey,TElement](IEnumerable`1 source, Func`2 keySelector, Func`2 elementSelector, IEqualityComparer`1 comparer)
   at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable`1.BuildIndexMap(IReadOnlyList`1 columnNames, DbDataReader dataReader)
   at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__21_0(DbContext _, Enumerator enumerator)
   at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable`1.Enumerator.MoveNext()

May someone please guide me on how to do this using or not a procedure ?

Regards,

Answer 1

I got it ! (probably not the better way)

I created the exact dto i needed

public class AppDepDto
{
public string Parent { get; set; }
public string Son { get; set; }
}

In the dbContext.cs, i've added a dbset.

 public DbSet<AppDepDto> AppDepDtos { get; set; }

I had to add in the OnModelCreating the fact it hasn't any key. (Link)

builder
    .Entity<AppDepDto>(
        eb =>
        {
            eb.HasNoKey();
        });

Finally, my service just became:

public async Task<List<AppDepDto>> GetApplicationDependenciesAsync(int id)
    {
        var liste = await _dbContext.AppDepDtos
            .FromSqlInterpolated($"CALL GET_DEPENDENCIES(1)")
            .ToListAsync();


        return liste;
    }

Result are :

[   {
    "parent": "A",
    "son": "B"   },   {
    "parent": "B",
    "son": "C"   },   {
    "parent": "B",
    "son": "D"   },   {
    "parent": "D",
    "son": "E"   } ]