Friday, June 14, 2024

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 { getset}

    public string Title { getset}

    public string[] Tags { getset}

    public DateTime[] Visits { getset}
I have Tags and Visits as arrays.

When the table is created, EF Core uses the following query.
    [Title] nvarchar(max) NOT NULL,
    [Tags] nvarchar(max) NOT NULL,
    [Visits] nvarchar(max) NOT NULL,
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"))
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))
This generated the following query.
SELECT [p].[Id], [p].[Tags], [p].[Title], [p].[Visits]
FROM [Posts] AS [p]
    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!
Hope this helps! 

Happy Coding.


No comments:

Post a Comment