JSON columns/properties

[kswierszczyk]

Contact Details

[email protected]

Detailed description?

First off, WONDERFUL project! One thing I would like to see is making use of JSON objects. An example might be having the User entity include a MetaData or ExtraData property that is a typed object and uses the newer JSON features from EF and SQL server so that we can store complex objects (such as UserPreferences) as JSON. This would utilize the ef core ToJson feature where the generated queries use JSON_VALUE behind the scenes while LINQ code remains simple (_context.Users.Where(x=>x.UserPreferences.IsEnrolledInNotifications) for example

Feature Type

.NET

Code of Conduct

• I agree to follow this project's Code of Conduct

[tjementum]

Thanks for the feedback. I've converted your issue to a discussion :)

There is already a very small sample that uses EF Core Json in the form of the User.Avatar.

public sealed class User : AggregateRoot<UserId>
{
    ...
    public Avatar Avatar { get; private set; } = default!;
    ...

}

public sealed record Avatar(string? Url = null, int Version = 0, bool IsGravatar = false);

As you can see, the Users table has an Avatar column that is a JSON column. This is a feature of EF Core that allows you to store complex objects in a single column.

How to query a complex object

I don’t have an example to query a complex object, but it’s so dead simple that it just works as you would expect. Here is a full guide on how to implement it. I just tried to make an example of how you can query all users that are using Gravatars.com for their user profile. It’s not really usable, so I don’t want to check it in. So, I made this guide instead.

1. Create an API endpoint in AccountManagement.Api/Users/UsersEndpoints.cs

group.MapGet("/get-gravatar-users", async Task<ApiResult<UserResponseDto[]>> (ISender mediator)
    => await mediator.Send(new GetGravatarUsers())
).Produces<UserResponseDto[]>();

2. Create a new command as AccountManagement.Application/Users/GetGravatarUsers.cs

using Mapster;
using PlatformPlatform.SharedKernel.ApplicationCore.Cqrs;

namespace PlatformPlatform.AccountManagement.Application.Users;

public sealed record GetGravatarUsers : IRequest<Result<UserResponseDto[]>>;

public sealed class GetGravatarUsersHandler(IUserRepository userRepository)
    : IRequestHandler<GetGravatarUsers, Result<UserResponseDto[]>>
{
    public async Task<Result<UserResponseDto[]>> Handle(GetGravatarUsers request, CancellationToken cancellationToken)
    {
        var users = await userRepository.GetGravatarUsers(cancellationToken);
        return users.Adapt<UserResponseDto[]>();
    }
}

3. Add this to the IUserRepository interface in AccountManagement.Domain/Users/IUserRepository.cs

Task<User[]> GetGravatarUsers(CancellationToken cancellationToken);

4. Implement it in the UserRepository class in AccountManagement.Infrastructure/Users/UserRepository.cs

public Task<User[]> GetGravatarUsers(CancellationToken cancellationToken)
{
    IQueryable<User> users = DbSet;
    return users.Where(u => u.Avatar.IsGravatar).ToArrayAsync(); // This is the magic, to query complex object. So dead simple that you almost don't have to learn anything.
}

This last line shows how you can use EF Core Json. Now start the AppHost in PlatformPlatform and go to https://localhost:9000/swagger/index.html#/Users/GetApiAccountManagementUsersGetGravatarUsers. This is how the SQL looks when you query this:

If you have not already seen it, I can highly recommend this video https://www.youtube.com/watch?v=_8iH5QnkIJo about the feature you mentioned.

Mapping of complex objects to DTOs

Bonus. I’m using Mapster to map Domain entities to DTOs… and this is equally magical. Look at how the mapping from User.Avatar.Url to UserDto.AvatarUrl is done in AccountManagement.Application/Users/GetUser.cs:

using Mapster;

public sealed class GetUserHandler(IUserRepository userRepository)
    : IRequestHandler<GetUserQuery, Result<UserResponseDto>>
{
    public async Task<Result<UserResponseDto>> Handle(GetUserQuery request, CancellationToken cancellationToken)
    {
        var user = await userRepository.GetByIdAsync(request.Id, cancellationToken);
        return user?.Adapt<UserResponseDto>() ?? Result<UserResponseDto>.NotFound($"User with id '{request.Id}' not found.");
    }
}

public sealed record UserResponseDto(
    string Id,
    DateTimeOffset CreatedAt,
    DateTimeOffset? ModifiedAt,
    string Email,
    UserRole Role,
    string FirstName,
    string LastName,
    string Title,
    bool EmailConfirmed,
    string? AvatarUrl // This is just a flat property and not a complex object
);

Mapster simply does the mapping by convention and figures out that User.Avatar.Url should be mapped to UserDto.AvatarUrl. No configuration!