EF Core 3.1 Fail to query on Json Serialized Object

Question

I used json serialization to store list on ids in a field

Model:

public class Video
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual IList<int> AllRelatedIds { get; set; }
}

Context:

modelBuilder.Entity<Video>(entity =>
{
    entity.Property(p => p.AllRelatedIds).HasConversion(
    v => JsonConvert.SerializeObject(v, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore }),
    v => JsonConvert.DeserializeObject<IList<int>>(v, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore })
    );
});

It works fine, Adding, Editing, Deleting items is easy and in SQL Database it stores as json like
[11000,12000,13000]

Everything is fine BUT!! as soon as want to query on this list I get weird responses.

Where:

_context.Set<Video>().Where(t=>t.AllRelatedIds.contains(11000)) returns null however if I ask to return all AllRelatedIds items some records have 11000 value exp.

Count:

_context.Set<Video>().Count(t=>t.AllRelatedIds.contains(11000)) returns 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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync().

What's the matter with EF Core? I even tested t=>t.AllRelatedIds.ToList().contains(11000) but made no difference

What I should do? I don't want to have more tables, I used this methods hundreds of times but seems never queried on them.

Answer 1

The Json Serialization/Deserialization happens at application level. EF Core serializes the IList<int> object to value [11000,12000,13000] before sending it to database for storing, and deserializes the value [11000,12000,13000] to IList<int> object after retrieving it from the database. Nothing happens inside the database. Your database cannot operate on [11000,12000,13000] as a collection of number. To the database, its a single piece of data.

If you try the following queries -

var videos = _context.Set<Video>().ToList();
var video = _context.Set<Video>().FirstOrDefault(p=> p.Id == 2);

you'll get the expected result, EF Core is doing it's job perfectly.

The problem is, when you query something like -

_context.Set<Video>().Where(t=> t.AllRelatedIds.Contains(11000))

EF Core will fail to translate the t.AllRelatedIds.Contains(11000) part to SQL. EF Core can only serialize/deserialize it because you told it to (and how). But as I said above, your database cannot operate on [11000,12000,13000] as a collection of integer. So EF Core cannot translate the t.AllRelatedIds.Contains(11000) to anything meaningful to the database.

A solution will be to fetch the list of all videos, so that EF Core can deserialize the AllRelatedIds to IList<int>, then you can apply LINQ on it -

var allVideos = _context.Set<Video>().ToList();
var selectedVideos = allVideos.Where(t=> t.AllRelatedIds.Contains(11000)).ToList();

But isn't fetching ALL videos each time unnecessary/overkill or inefficient from performance perspective? Yes, of course. But as the comments implied, your database design/usage approach has some flaws.