Entity Framework Core 7 JSON Column - use a Dictionary<int, Object>

Question

In playing with EF Core 7 JSON Column I'm attempting to get to a point where my saved JSON would look like this:

{
    "Courses": [
        "123": {
            "RemoteCourseId": 123,
            "Delivery": {
                "Method": "email",
                "ModeTarget": "[email protected]",
                "Time": "13:30:00"
            },
            "Quizzes": [
                "54321": {
                    "RemoteQuizId": 54321,
                    "Credits": null,
                    "Details": {
                        "CompletedDate": null,
                        "StartDate": "2023-04-17T17:17:10.315684Z"
                    },
                    "Questions": [
                        "12345": {
                            "ExpirationDate": "2023-04-17T17:17:10.316138Z",
                            "IsCorrect": false,
                            "QuestionId": 12345,
                            "QuestionUrl": "https://stackoverflow.com",
                            "SentDate": "2023-04-17T17:17:10.316181Z"
                        }
                    ]
                }
            ]
        }
    ]
}

The challenge I'm facing is how to get identifiers, shown another way:

{
    "Courses": [
        "123": {},
        "789": {}
    ]
}

I would like to get to a point where I can specify the "123" and "789". My attempt at this is using a Dictionary like so:

public class LearnerCustomDataEntity
    {
        public int Id { get; set; }

        public int LtiUserId { get; set; }

        public LearnerCustomDataDto Data { get; set; }
    }

    public class LearnerCustomDataDto
    {
        public LearnerCustomDataProfileDto Profile { get; set; }

        public Dictionary<int, LearnerCustomDataCourseDto> Courses { get; set; }
    }

Within the DbContext I specify the JSON Column (LearnerCustomDataEntity.Data) like this:

modelBuilder.Entity<LearnerCustomDataEntity>()
                .OwnsOne(l => l.Data, onb =>
                {
                    onb.OwnsOne<LearnerCustomDataProfileDto>(lcde => lcde.Profile);
                    onb.OwnsOne(lcdc => lcdc.Courses, onb =>
                    {
                        // I'm not sure _how_ to configure this
                        onb.HasOne(l => l);
                    });
                    onb.ToJson();
                });

I don't know how to configure the OwnedNavigationBuilder for the LearnerCustomDataDto.Courses field.

My end goal is to be able to get a specific course using something like

LearnerCustomDataCourseDto dto = dbContext.LearnerCustomData.Single(l => l.Data.Courses[123]);

Is using a dictionary the correct approach here? I'm certainly open to suggestions on doing it with a dictionary or changing to something else.

Update: I've tried using a KeyedCollection implementation as it would accomplish the goal of accessing via the key, but I'm having an issue with the conversion. So, for example:

public class LearnerCustomDataDto
{
    public LearnerCustomDataProfileDto Profile { get; set; }

    public LearnerCustomDataCourseDtoCollection Courses { get; set; }
}

public class LearnerCustomDataCourseDtoCollection : KeyedCollection<int, LearnerCustomDataCourseDto>
{
    protected override int GetKeyForItem(LearnerCustomDataCourseDto item)
    {
        return item.RemoteCourseId;
    }
}

The updates to the context make it look like this:

modelBuilder.Entity<LearnerCustomDataEntity>()
                .OwnsOne(l => l.Data, onb =>
                {
                    onb.OwnsOne(lcde => lcde.Profile);
                    onb.OwnsMany(lcde => lcde.Courses);
                    onb.ToJson();
                });

However, when trying to delete an entity I get an InvalidOperationException:

System.InvalidOperationException : The requested operation requires an element of type 'Array', but the target element has type 'Object'.
Stack Trace:
[xUnit.net 00:00:46.97]            at System.Text.Json.ThrowHelper.ThrowJsonElementWrongTypeException(JsonTokenType expectedType, JsonTokenType actualType)
[xUnit.net 00:00:46.98]            at System.Text.Json.JsonElement.EnumerateArray()
[xUnit.net 00:00:46.98]            at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.IncludeJsonEntityCollection[TIncludingEntity,TIncludedCollectionElement](QueryContext queryContext, Nullable`1 jsonElement, Object[] keyPropertyValues, TIncludingEntity entity, Func`4 innerShaper, Action`2 fixup)
[xUnit.net 00:00:46.98]            at lambda_method654(Closure, QueryContext, Object[], JsonElement)
[xUnit.net 00:00:46.98]            at lambda_method653(Closure, QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator)
[xUnit.net 00:00:46.98]            at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
[xUnit.net 00:00:46.98]            at System.Linq.Enumerable.TryGetSingle[TSource](IEnumerable`1 source, Boolean& found)
[xUnit.net 00:00:46.98]            at lambda_method670(Closure, QueryContext)
[xUnit.net 00:00:46.98]            at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
[xUnit.net 00:00:46.98]            at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)

Which is triggered by:

_db.LearnerCustomData.SingleOrDefault(l => l.LtiUserId == defaultUser.Id);

Dramatist

Tracked here: github.com/dotnet/efcore/issues/29825 – Ul

Answer 1

Simplest way would be to just separate EF navigation property from JSON dictionary. Its a simple trick that should work with every ORM and every Serializer.

class ParentEntity
{
   [JsonIgnore]
   [XmlIgnore] [CsvIgnore] [FaxToCIAIgnore] //future proofing!
   public HashSet<ChildEntity> Children { get; set; }

   [NotMapped] //don't include this field in EF black magic
   public Dictionary<int, ChildEntity> JsonChildren => Children.ToDictionary(c=>c.Id, c=>c);
}

Just add some attributes for nice property names and done.

But you should define ViewModel/DTO classes anyway, you'll still have ToDictionary either in query materialization or in getter property but won't get trout slapped during code review.

Your end goal just looks like selecting entity by two ID's, so just make simple select statement.

Brittnybritton

I'm not sure I follow this - I don't want to ignore the fields, I want to take advantage of the new Json column features in EF7 – Dramatist

@ChrisRockwell were you able to solve it? I have the same need – Clitoris

Answer 2

I have solved this issue by using

List<KeyValuePair<int, ChildEntity>> 

Byssus

Answer 3

For dictionaries, I've had to resort to create lists of custom key value pairs. For whatever reason, List<KeyValuePair<string, string>> wasn't working, for example. It would just serialize as {"capacity": 4} or whatever because I was configuring like this

builder.OwnsOne(e => e.MyDictionary, b => b.ToJson())

The correct config should have been

builder.OwnsMany(e => e.MyDictionary, b => b.ToJson())

But it wouldn't compile, stating that b.ToJson() didn't exist on that builder. I still don't quite understand why. But at any rate, by creating a collection of objects, everything worked normally:

public class StringPairs
{
  public string Key { get; set; }
  public string Value { get; set; }

  public StringPairs(){}
  public StringPairs(KeyValuePair<string, string> pair)
  {
    Key = pair.Key;
    Value = pair.Value;
  }
}

I use List<StringPairs> on my entity which allowed me to configure as intended:

builder.OwnsMany(e => e.MyListOfStringPairs, b => b.ToJson())

To make it useful just create an extension method to convert back and forth:

public static class Ext
{
  public static Dictionary<string, string> AsDictionary(this IEnumerable<StringPair> pairs)
    => pairs.ToDictionary(kvp => kvp.Key, kvp => kvp.Value);

  public static List<StringPair> AsList(this IDictionary<string, string> dict)
    => dict.Select(kvp => new StringPair(kvp));

}

So this only exists in the entity layer. The application would always use a dictionary to ensure unique keys. It's not until it comes time to map an Entity for persistence that its converted to a list.

Risk