József Takács
Software Developer
14 December 2022JSON columns are supported since SQL Server 2016 and from EF7 also mapping JSON columns are supported, to map JSON columns we need to use owned entity types(OwnsOne and OwnsMany) and call once ToJson() when configuring the type in the OnModelCreating method in DbContext. Let's see an example where we have a user who can set some application settings and log his business trips. As application settings the user can set his default language, theme and choose visible columns with sort direction and default value to filter from some of large tables. For business trips the user should give a name and can add transits.
We will have two tables in our database AppUser and Trip, and both tables will have JSON columns. The models which are used, are the following:
The AppUser has some basic information, trips and user settings, the user settings will store as JSON in the column.
public class AppUser
{
public Guid Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public UserSettings? UserSettings { get; set; }
public ICollection<Trip> Trips { get; set; }
}
The UserSettings has language, Theme and a list of TableDisplayedColumns, the theme and the tables with visible columns will store as JSON in columns, together inside of the UserSettings.
public class UserSettings
{
public string Language { get; set; }
public Theme Theme { get; set; }
public ICollection<TableDisplayedColumns>? Tables { get; set; }
}
public class Theme
{
public string Name { get; set; }
public int FontSize { get; set; }
}
public class TableDisplayedColumns
{
public string Table { get; set; }
public List<TableColumn> Columns { get; set; }
}
public class TableColumn
{
public string Name { get; set; }
public string? DefaultValue { get; set; }
public string? SortDirection { get; set; }
}
The Trip has a name, a final destination and a list of TransitDetail, this transitions list will be also stored as JSON.
public class Trip
{
public Guid Id { get; set; }
public string Name { get; set; }
public string FinalDestination { get; set; }
public Guid UserId { get; set; }
public AppUser User { get; set; }
public ICollection<TransitDetail>? Transits { get; set; }
}
public class TransitDetail
{
public string StartingPoint { get; set; }
public string Destination { get; set; }
public string TransportationMode { get; set; }
public decimal TicketPrice { get; set; }
}
We went through our entities and models and we know how do we want to store in the database, the next step is to configure in OnModelCreating. To configure we need just one call to ToJson() and using OwnsOne and OwnsMany.
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<AppUser>()
.OwnsOne(
appUser => appUser.UserSettings, ownedNavigationBuilder =>
{
ownedNavigationBuilder.ToJson();
ownedNavigationBuilder.OwnsOne(settings => settings.Theme);
ownedNavigationBuilder.OwnsMany(
settings => settings.Tables,
ownedOwnedNavigationBuilder => ownedOwnedNavigationBuilder.OwnsMany(table => table.Columns));
});
modelBuilder.Entity<Trip>().OwnsMany(trip => trip.Transits, ownedNavigationBuilder => ownedNavigationBuilder.ToJson());
}
Now we need to run Add-Migration and Update-Database commands in the Package Manager Console to create tables in our database. After we created the tables it's time to add some data.
To add some data I will use API controllers, but you can add in any other way. For adding a user run the following code:
[HttpPost]
public void AddAppUser()
{
AppUser appUser = new AppUser
{
Id = Guid.NewGuid(),
FirstName = "F1",
LastName = "L1",
UserSettings = new()
{
Language = "ro",
Theme = new Theme { Name = "Dark", FontSize = 16 },
Tables = new List
{
new TableDisplayedColumns
{
Table = "Users",
Columns = new List
{
new TableColumn { Name = "FirstName", DefaultValue = null, SortDirection = "asc" },
new TableColumn { Name = "LastName", DefaultValue = null, SortDirection = null }
}
},
new TableDisplayedColumns
{
Table = "Books",
Columns = new List
{
new TableColumn { Name = "Name", DefaultValue = null, SortDirection = "asc" },
new TableColumn { Name = "PublishingYear", DefaultValue = "2022", SortDirection = null }
}
}
}
}
};
_appUserRepository.AddAppUser(appUser);
}
And the code from repository is just like for a normal entity.
public void AddAppUser(AppUser appUser)
{
_context.AppUsers.Add(appUser);
_context.SaveChanges();
}
Run and after we can see the data in our database.
We are adding two trips, for simplicity I copied the UserId from the AppUser table, from the user that we added recently.
[HttpPost]
public void AddTrip()
{
Guid userId = Guid.Parse("EC16EA91-EE2B-4D3B-9425-EB2EA58A41A1");
Trip trip = new Trip
{
Id = Guid.NewGuid(),
UserId = userId,
Name = "Sign contract",
FinalDestination = "Munich",
Transits = new List()
{
new TransitDetail { StartingPoint = "Oradea", Destination = "Cluj-Napoca", TransportationMode = "train", TicketPrice = 10.10m },
new TransitDetail { StartingPoint = "Cluj-Napoca", Destination = "Munich", TransportationMode = "flight", TicketPrice = 40.10m }
}
};
_tripRepository.AddTrip(trip);
Trip trip2 = new Trip
{
Id = Guid.NewGuid(),
UserId = userId,
Name = "Extend contract",
FinalDestination = "London"
};
_tripRepository.AddTrip(trip2);
}
Now we have data in our database, let's make some queries to get our data. Querying JSON columns is like querying any other table using LINQ.
Get users by theme.
public List GetUsersByTheme(string themeName)
{
return _context.AppUsers.Where(u => u.UserSettings != null && u.UserSettings.Theme.Name.ToLower() == themeName.ToLower()).ToList();
}
The result of getting users by the dark theme.
The result of getting users by the light theme.
We are updating the user theme, in this case, the EF update just the sub-document. The code for updating the theme:
The controller:
[HttpPost]
[Route("UpdateUserTheme")]
public void UpdateUserTheme()
{
Guid userId = Guid.Parse("EC16EA91-EE2B-4D3B-9425-EB2EA58A41A1");
Theme theme = new Theme { Name = "Light", FontSize = 16 };
_appUserRepository.UpdateUserTheme(userId, theme);
}
The repository:
public void UpdateUserTheme(Guid userId, Theme theme)
{
AppUser appUser = _context.AppUsers.FirstOrDefault(u => u.Id == userId);
if (appUser != null)
{
appUser.UserSettings.Theme = theme;
_context.SaveChanges();
}
}
We can see the user theme is updated.
Second, update the whole user settings, in this case, the EF update the whole document.
The controller:
[HttpPost]
[Route("UpdateUserSettings")]
public void UpdateUserSettings()
{
Guid userId = Guid.Parse("EC16EA91-EE2B-4D3B-9425-EB2EA58A41A1");
UserSettings userSettings = new UserSettings
{
Language = "ro",
Theme = new Theme { Name = "Dark", FontSize = 16 },
Tables = new List
{
new TableDisplayedColumns
{
Table = "Books",
Columns = new List
{
new TableColumn { Name = "Name", DefaultValue = null, SortDirection = "asc" },
new TableColumn { Name = "PublishingYear", DefaultValue = "2022", SortDirection = null }
}
}
}
};
_appUserRepository.UpdateUserSettings(userId, userSettings);
}
The repository:
public void UpdateUserSettings(Guid userId, UserSettings userSettings)
{
AppUser appUser = _context.AppUsers.FirstOrDefault(u => u.Id == userId);
if (appUser != null)
{
appUser.UserSettings = userSettings;
_context.SaveChanges();
}
}
We can see the user settings is updated.
Note: Some complex queries are not supported yet, for example, if we want to get the trips by the number of transits where we use Count(), won't work, or Any(), and so on. These features will be implemented in the next releases.
One of the benefits to use JSON columns is when you have more JOINs and you know you need to use them together, if you need to filter them frequently is better to use individual columns. Querying data in JSON columns could be complicated. Other scenarios to use JSON columns are for logging, permissions and configurations.
That was about it how to store as JSON in EF, how to query and some advice on when to use JSON columns, you can find the source code on GitHub.
Share