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.

Support me

I appreciate it if you would support me if have you enjoyed this post and found it useful, thank you in advance.

Join My Newsletter (WIP)

Join my weekly newsletter to receive my latest blog posts and bits, directly in your inbox.

Share this bit on

Twitter LinkedIn