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.
