EF Core 8.0: Use of Arrays with Microsoft SQL Server

I haven't really used Arrays with EF Core and then saw this nice post: A beginner’s guide to mapping arrays in EF Core 8 and wanted to try it out on Microsoft SQL Server.

Consider the below Entity.

public record Post
{
    public int Id { get; set; }
 
    public string Title { get; set; }
 
    public string[] Tags { get; set; }
 
    public DateTime[] Visits { get; set; }
}

I have Tags and Visits as arrays.

When the table is created, EF Core uses the following query.

CREATE TABLE [Posts] (
    [Id] int NOT NULL IDENTITY,
    [Title] nvarchar(max) NOT NULL,
    [Tags] nvarchar(max) NOT NULL,
    [Visits] nvarchar(max) NOT NULL,
    CONSTRAINT [PK_Posts] PRIMARY KEY ([Id])
);

Tags are Visits are created as nvarchar(max) columns, interesting.

I have inserted some data, and this is what it looks like.

Table Data

So for Microsoft SQL Server that doesn't support array types natively, EF Core is using JSON arrays.

Now I have a simple query to retrieve posts with a given tag.

List<Post> postsWithTag = await context.Posts
    .Where(x => x.Tags.Contains("Tag 1"))
    .ToListAsync();

Let's Look at the query EF generated.

SELECT [p].[Id], [p].[Tags], [p].[Title], [p].[Visits]
FROM [Posts] AS [p]
WHERE N'Tag 1' IN (
    SELECT [t].[value]
    FROM OPENJSON([p].[Tags]) WITH ([value] nvarchar(max) '$') AS [t]
)

Since the querying field is a JSON array, EF Core uses SQL OPENJSON function to parse JSON text and creates a temporary table that contains nvarchar(max) column because Tags is a string[].

Now let's consider another query on DateTime[] Visits.

List<Post> postsVisitedToday = await context.Posts
    .Where(x => x.Visits.Any(x => x.Date == DateTime.Now.Date))
    .ToListAsync();

This generated the following query.

SELECT [p].[Id], [p].[Tags], [p].[Title], [p].[Visits]
FROM [Posts] AS [p]
WHERE EXISTS (
    SELECT 1
    FROM OPENJSON([p].[Visits]) WITH ([value] datetime2 '$') AS [v]
    WHERE CONVERT(date, [v].[value]) = CONVERT(date, GETDATE())
)

This time EF Core added a filter on the temporary table that contains datetime2 column, pretty smart!

More Read:
   What's New in EF Core 8: Primitive collections

Hope this helps! 

Happy Coding.

Regards,

Jaliya