Entity Framework Core 7 introduced developers to JSON column support. I even wrote about it on the JetBrains .NET blog, and it has been generally well-received. However, recently a user tried my sample project, only to find that they could not filter on a collection of JSON values. Given the current Microsoft SQL Server provider, the following LINQ statement does not work.
var results = await db.Posts .Where(x => x.Tags.Any(t => t.Value == "CSharp")) .ToListAsync();
Entity Framework Core will throw the following exception.
The LINQ expression 'DbSet<Post>() .Where(p => EF.Property<List<Tag>>(p, "Tags") .AsQueryable() .Any(o => o.Value == "CSharp"))' 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'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
If you’re experiencing this issue and need to filter on a JSON array, I have several SQL queries to help you work around this issue. Let’s get started.