Developer bit

New in Entity Framework 8: Primitive collection properties

Starting from EF 8, EF automatically stores a collection of primitive values directly in a column (as JSON), whereas previously a separate table was required.

By Tim Deschryver Published January 09, 2024 #dotnet · #entity-framework

New in Entity Framework 8: Primitive collection properties

Starting from EF 8, EF automatically stores a collection of primitive values directly in a column (as JSON), whereas previously a separate table was required.

To query data, the OPENJSON table-valued function is used. This creates a rowset view of the JSON data, which can be compared as a "normal" relational table, and can thus be queried as you're used to.

Note

OPENJSON is more and more used in EF features to support more use-cases (or to improve the query performance). An example of this is the possibility to use inline collections while querying data, as mentioned in the release announcement.

A use case for using primitive collection properties is to save a data collection that's retrieved via its parent. If you need to query this set, it's probably better to store the collection in a seperate table. This allows you to create indecies in order to optimize search-based scenarios, that otherwise isn't possible using a JSON column.

Let's explore how this translates into code and SQL.


 
public class Person
{
    public required Guid Id { get; init; }
    public required int[] LuckyNumbers { get; init; }
}
 
/*
  CREATE TABLE [dbo].[Persons](
    [Id] [uniqueidentifier] NOT NULL,
    [LuckyNumbers] [nvarchar](max) NOT NULL,
    CONSTRAINT [PK_Persons] PRIMARY KEY CLUSTERED ([Id] ASC)
  )
*/


 
var person = new Person
{
    Id = Guid.NewGuid(),
    LuckyNumbers = [1, 3, 5, 7]
};
 
dbContext.Add(person);
dbContext.SaveChanges();
 
/*
  exec sp_executesql N'SET IMPLICIT_TRANSACTIONS OFF;
  SET NOCOUNT ON;
  INSERT INTO [dbo].[Persons] ([Id], [LuckyNumbers])
  VALUES (@p0, @p1);
  ',N'@p0 uniqueidentifier,@p1 nvarchar(4000)',
  @p0='691dbfdd-62b3-43f5-a9bb-498985ac7952',
  @p1=N'[1,3,5,7]'
*/


 
--------------------------------------+--------------+
| Id | LuckyNumbers |
--------------------------------------+--------------+
| 691dbfdd-62b3-43f5-a9bb-498985ac7952 | [1,3,5,7] |
--------------------------------------+--------------+


 
var persons = dbContext.Set<Person>()
  .Where(p => p.LuckyNumbers.Contains(3))
  .ToList();
 
/*
  SELECT [p].[Id], [p].[LuckyNumbers]
  FROM [dbo].[Persons] AS [p]
  WHERE 3 IN (
    SELECT [l].[value]
    FROM OPENJSON([p].[LuckyNumbers])
    WITH ([value] int '$') AS [l]
  )
*/

For more info and new features introced in EF 8 see the documentation.

Feel free to update this developer bit on GitHub, thanks in advance!

Support my work

Share this bit