.NetCore8 - Entity Framework - Mysql

Question

Can you help me solve the problem with my code in .NetCore 8 with Entity Framework, database MySql where I use package Pomelo.EntityFrameworkCore.MySql.

Models:

public abstract class EntityBase
{
    public int Id { get; set; }
    public DateTime Created { get; protected set; } = DateTime.UtcNow;
    public DateTime Updated { get; set; } = DateTime.UtcNow;
}

public class Employee : EntityBase
{
    public string Name { get; set; }
    public PersonalData PersonalData { get; set; }
} 

public class PersonalData
{
    public Address Address { get; set; }    
    public IEnumerable<Child> Children { get; set; }
}

public class Child
{
    public string Name { get; set; }
    public DateTime DateOfBirth { get; set; }
    public int Height { get; set; }
}

public class Address
{
    public string Street { get; set; }
    public string City { get; set; }
    public int PostalCode { get; set; }
}

Packages (up to date):

<ItemGroup>
    <PackageReference Include="Ardalis.SmartEnum" Version="7.0.0" />
    <PackageReference Include="AutoMapper" Version="12.0.1" />
    <PackageReference Include="AutoMapper.Extensions.Microsoft.DependencyInjection" Version="12.0.1" />
    <PackageReference Include="Microsoft.AspNetCore.OpenApi" Version="8.0.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="8.0.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="8.0.1">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="8.0.1" />
    <PackageReference Include="Newtonsoft.Json" Version="13.0.3" />
    <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="8.0.0-beta.2" />
    <PackageReference Include="Pomelo.EntityFrameworkCore.MySql.Json.Microsoft" Version="8.0.0-beta.2" />
    <PackageReference Include="Swashbuckle.AspNetCore" Version="6.5.0" />
  </ItemGroup>

AppDbContext.cs

public class AppDbContext : DbContext
{

    public AppDbContext(DbContextOptions<AppDbContext> options)
        : base(options)
    {}

    public DbSet<Employee> Employees => Set<Employee>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());        
    }

    public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = new CancellationToken())
    {
        return await base.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
    }

    public override int SaveChanges()
    {
        return SaveChangesAsync().GetAwaiter().GetResult();
    }
}

EmployeeConfiguration.cs

public class EmployeeConfiguration : IEntityTypeConfiguration<Employee>
{
    public void Configure(EntityTypeBuilder<Employee> builder)
    {
        builder.Property(e => e.PersonalData)
            .HasConversion(
                v => ConvertToDatabase(v),
                v => ConvertFromDatabase(v))
            .HasColumnType("json"); 
    }
            
    private static string ConvertToDatabase(PersonalData personalData)
    {
        return JsonSerializer.Serialize(personalData, default(JsonSerializerOptions));
    }

    private static PersonalData ConvertFromDatabase(string jsonData)
    {
        var result = JsonSerializer.Deserialize<PersonalData>(jsonData, default(JsonSerializerOptions));
        if (result == null)
        {
            throw new SerializationException("Unable to deserialize provided string");
        }
        return result;
    }
}

Query

public async Task<IEnumerable<EmployeeResponse>> GetEmployeeChildAfterYear(int afterYear)
    {       
        var employees = await _context.Employees
             .Where(i => i.PersonalData.Children != null && i.PersonalData.Children.Any(c => c.DateOfBirth.Year >= afterYear))
             .ToListAsync(); 

        return _mapper.Map<IEnumerable<EmployeeResponse>>(employees);
    }

I'm getting an error:

The LINQ expression 'c => c.DateOfBirth.Year >= __afterYear_0' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

If I use:

builder.OwnsOne(e => e.PersonalData, b => {
            b.ToJson(); 
            b.OwnsMany(p => p.Children);
            b.OwnsOne(e => e.Address);
        });

I'm getting an error:

The EF Core 7.0 JSON support isn't currently implemented. Instead, there is support for a more extensive implementation.

But I`m using:

Entity Framework Core .NET Command-line Tools 8.0.1

And ToJson() still isn`t supported in pomelo: https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/1752

Answer 1

I edited Employee model

public class Employee : EntityBase
{    
    public string Name { get; set; } = null!;  
    public Address Address { get; set; }
    public IEnumerable<Child>? Children { get; set; }
} 

EmployeeConfiguration.cs

    public class EmployeeConfiguration : IEntityTypeConfiguration<Employee>
    {
        public void Configure(EntityTypeBuilder<Employee> builder)
        {
            // MySQL
            builder.Property(e => e.Address)
                .HasColumnType("json");                    
            
            builder.Property(e => e.Children)
                .HasColumnType("json")
                .UseJsonChangeTrackingOptions(MySqlCommonJsonChangeTrackingOptions.RootPropertyOnly);
        }
    }

Query

    public async Task<IEnumerable<EmployeeResponse>> GetEmployeesChildAfterYear(int afterYear)
    {   
        DateTime afterYearDate = new DateTime(afterYear,1,1);

        var employees = await _context.Employees
                                    .Where(i => i.Children != null && 
                                                EF.Functions.JsonExtract<DateTime>(i.Children, "$[0].DateOfBirth") > afterYearDate)
                                    .ToListAsync();

        return _mapper.Map<IEnumerable<EmployeeResponse>>(employees);
    }