Add SQL Server JSON support through backing fields

[jovanpop-msft]

I would like to define a property of string[] type that would be serialized as JSON text in database (I don't want to have a separate table for list of tags).

I'm trying to use backing field where I would serialize/deserialize property using JSONConvert class:

    public class Blog
    {
        public int BlogId { get; set; }
        public string Url { get; set; }
        public List<Post> Posts { get; set; }

        private string _Tags;
        public string[] Tags {
            get { return JsonConvert.DeserializeObject<string[]>(this._Tags); }
            set { _Tags = JsonConvert.SerializeObject(value); }
        }

   }

However, OnModelCreating throws the following error:

InvalidOperationException: The specified field '_Tags' of type 'string' cannot be used for the property 'Blog.Tags' of type 'String[]'. Only backing fields of types that are assignable from the property type can be used.

There should be no limitation in this case since I'm defining my serialization logic in getter/setter, and EF should just persist string in Tags field into Tags column in a table.

Since we can do the similar thing using included/excluded fields, it is strange that the same functionality is not supported in backing fields:

        internal string _Tags { get; set; }

        [NotMapped]
        public string[] Tags {
            get { return JsonConvert.DeserializeObject<string[]>(this._Tags); }
            set { _Tags = JsonConvert.SerializeObject(value); }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Blog>()
                .Property(b => b._Tags).HasColumnName("Tags");
        }

Note

This feature might be used to implement JSON support in EF if we have the following:

1. Declarative way for defining complex properties that will be serialized as JSON using some kind of backing fields:

public class Blog
{
    [JSON]
    public string[] Tags { get; set; }

    [JSON]
    public Person Owner { get; set; }
}

where Person is just a plain class, e.g.:

public class Person {
   public string Name { get; set; }
   public string Surname { get; set; }
   public int Status { get; set; }
}

2. Translating Contains and Field predicates from .Where() into T-SQL with OPENJSON. As an example, imagine that we have defined tags as string[] and author object that is serialized as JSON field in column instead of the separate table. We could have the following queries:

_context.Blogs.Where(b => b.Tags.Contains("C#"));
// Translate to:
// SELECT b.*
// FROM Blogs b
//      CROSS APPLY OPENJSON(Tags) WITH (value nvarchar(400)) tags
// WHERE tags.value = 'C#'

_context.Blogs.Where(b => b.Owner.State == 1).Select(b => b.Owner.Name);
// Translate to:
// SELECT Name
// FROM Blogs b
//      CROSS APPLY OPENJSON( b.Owner ) WITH (Status int, Name nvarchar(400)) 
// WHERE State = 1

Steps to reproduce

1. Follow the instructions in getting started page: https://docs.microsoft.com/en-us/ef/core/get-started/aspnetcore/existing-db . I can copy entire code, but I believe that you already have this sample.
2. Add the string[] backing field into Blogs model class:

        private string _Tags;
        public string[] Tags {
            get { return JsonConvert.DeserializeObject<string[]>(this._Tags); }
            set { _Tags = JsonConvert.SerializeObject(value); }
        }

3. Configure backing field in OnModelCreating:

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Blog>()
                .Property(b => b.Tags)
                .HasField("_Tags")
                .UsePropertyAccessMode(PropertyAccessMode.Field);
        }

Further technical details

EF Core version: 1.1.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2015

[ikourfaln]

Awesome, Thank you for clarifying, I really need this feature.

[ajcvickers]

@jovanpop-msft In the cases where you are getting the exception EF is still mapping the Tags property because there is no code that tells EF not to map this property. If the expectation is that EF should interact through the _Tags field, then the Tags property will need to be ignored, like you do with NotMapped in the second example. The _Tags field can then be mapped using something like:

modelBuilder.Entity<Blog>().Property("_Tags");

[jovanpop-msft]

@ajcvickers Maybe I don't understand you, but according to the https://docs.microsoft.com/en-us/ef/core/modeling/backing-field#controlling-when-the-field-is-used I just need to specify that Property Tags will use field _Tags:

modelBuilder.Entity<Blog>()
    .Property(b => b.Tags).HasField("_Tags");

This actually works when backing field and property have the same type and this code works fine:

internal string _Tags;
public string Tags
{
    get { return _Tags; }
    set { _Tags = value; }
}

Hovewer, this code fails with exception:

internal string _Tags;
public string[] Tags
{
    get { return (this._Tags == null) ? null : JsonConvert.DeserializeObject<string[]>(this._Tags); }
    set { _Tags = JsonConvert.SerializeObject(value); }
}

Therefore the only difference is mismatch in the return types string vs string[].

I don't need code like:

modelBuilder.Entity<Blog>().Property("_Tags");

since backing fields that starts with _ are automatically used as regular properties (or I'm missing something in this case). Case with identical types works fine.

I can explicitly ignore Tags property, but this is not needed when I have the same types.

modelBuilder.Entity<Blog>().Ignore(b => b.Tags);

[ajcvickers]

@jovanpop-msft It has different semantics in the model. In the case where the property is mapped, then the metadata for the property in the model comes from the type of the property. So even if you say that EF should read and write that property using the field, it doesn't change that EF reasons about it using the type of the property. This impacts how APIs such as EF.Property or property entries work. In this case, EF doesn't know how to deal with string[] and so there will always be some type of exception thrown.

This is different from EF reasoning about a conceptual property of type string but having that property not represented by any actual CLR property, which is the case in your class. In that case you have to set up a property of type string and tell EF to map it to a field. The shorthand way of doing this is the code I posted.

[rowanmiller]

Closing as we already have the following issues tracking the high level scenario here:

• Simple Type Conversion Support for custom type mapping and data store to CLR type conversions #242
• JSON Support Map JSON values stored in database to EF properties #4021

As @ajcvickers outlined, backing fields need to match the data type of the property they are backing.

[weitzhandler]

And #2282 as well.
Additionally, please check out the Impatient repo, which relates here.

[weitzhandler]

@jovanpop-msft
Just read your great Code Project article, thanks for sharing. This is one of the most important features I'd want to see of EF Core.
It'll open EF to the world of NoSQL at once.