![\"Flowchart](\"https:\/\/blog.wiseowls.co.nz\/wp-content\/uploads\/2022\/03\/flowchart-step-4-1024x453.png\")
<\/figure>\nThe catch is, we don’t know full list of attributes. And, probably, we should not care – perfect data model for frequently changing e-commerce websites would be hard to deduce and likely very painful to maintain. We could just lump it all together and chuck it into a schema-less data store like Cosmos DB or Mongo, but for the sake of argument we’ll imagine the client was adamant they needed a SQL Server because it’s a business decision they made a long time ago.<\/p>\n
Not a huge drama<\/h2>\n
SQL Server has functions to work with JSON data – all we have to do is put it into a sufficiently big column. We can save heaps of time creating one simple data model and calling it a day.<\/p>\n
Moving on to coding, let us focus on data retrieval and skip the API part for now – it’s a whole different can or worms and will distract us from our point. From here on we’ll be exploring ways to query the data<\/p>\n
Test Bench<\/h2>\n
We’ve created a small EF Core project where DBContext looks something like this:<\/p>\n
class SHDbContext : DbContext\n{\n\tpublic DbSet<WishlistEntry> WishlistEntries { get; set; }\n\n\tprotected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)\n\t{\n\t\tbase.OnConfiguring(optionsBuilder);\n\t\toptionsBuilder.UseSqlServer(Environment.GetEnvironmentVariable(\"DB_CONNSTR\")).LogTo(Console.WriteLine);\n\t}\n}<\/code><\/pre>\nwhere WishlistEntry<\/code> is defined like so:<\/p>\nclass WishlistEntry\n{\n\n\tpublic int Id { get; set; }\n\tpublic int CustomerId { get; set; }\n\t[MaxLength(300)]\n\tpublic string ItemSource { get; set; }\n\tpublic string ItemData { get; set; }\n\n\t[DatabaseGenerated(DatabaseGeneratedOption.Computed)]\n\tpublic string ItemCategory { get; set; }\n}\n\nclass ItemData\n{\n\tpublic string Sku { get; set; }\n\tpublic string Name { get; set; }\n\tpublic List<Category> Category { get; set; }\n}\n\nclass Category\n{\n\tpublic string Id { get; set; }\n\tpublic string Name { get; set; }\n}<\/code><\/pre>\nwith setup out of the way, let us define a sample entry and write a query to pick all entries that contain a category “Connected Home & Housewares”:<\/p>\n
{\n \"sku\": 185267,\n \"name\": \"Duracell - D Batteries (4-Pack)\",\n \"type\": \"HardGood\",\n \"price\": 9.99,\n \"upc\": \"041333430010\",\n \"category\": [\n {\n \"id\": \"pcmcat312300050015\",\n \"name\": \"Connected Home & Housewares\"\n },\n {\n \"id\": \"pcmcat248700050021\",\n \"name\": \"Housewares\"\n },\n {\n \"id\": \"pcmcat303600050001\",\n \"name\": \"Household Batteries\"\n },\n {\n \"id\": \"abcat0208002\",\n \"name\": \"Alkaline Batteries\"\n }\n ],\n \"shipping\": 5.99,\n \"description\": \"Compatible with select electronic devices; D size; DURALOCK Power Preserve technology; 4-pack\",\n \"manufacturer\": \"Duracell\",\n \"model\": \"MN1300R4Z\",\n \"url\": \"http:\/\/www.bestbuy.com\/site\/duracell-d-batteries-4-pack\/185267.p?id=1051384046551&skuId=185267&cmp=RMXCC\",\n \"image\": \"http:\/\/img.bbystatic.com\/BestBuy_US\/images\/products\/1852\/185267_sa.jpg\"\n}<\/code><\/pre>\nOption one – JsonConvert.Deserialize<\/h2>\n
The naive approach to the desired outcome would be to simply try and deserialise the field and pray Entity Framework would work its magic:<\/p>\n
var context = new SHDbContext();\n\/\/var sql = context.WishlistEntries.Select(wle => JsonConvert.DeserializeObject<ItemData>(wle.ItemData)\n\/\/ .Category.Any(c => c.Name == \"Connected Home & Housewares\")); \/\/The LINQ expression 'c => c.Name == \"Connected Home & Housewares\"' could not be translated. <\/code><\/pre>\nUnfortunately, this would not even compile as EF has no idea what to do with JsonConvert<\/code>. We’ll end up having to materialise the query and handle filtering on the client side:<\/p>\nvar data = context.WishlistEntries.Select(wle => JsonConvert.DeserializeObject<ItemData>(wle.ItemData))\n .AsEnumerable()\n .Where(d => d.Category.Any(c => c.Name == \"Connected Home & Housewares\"))\n .ToList();<\/code><\/pre>\nThis will work as expected, and some developers will probably stop here, but this is in fact a time bomb. When our wish lists get big enough, the clients will put a serious strain on SQL Server and the network fetching the whole table in its entirety. Not nice.<\/p>\n
Option two – write a Value Converter<\/h2>\n
Technically this is a variation of the previous option but leveraging EF Core Value Conversion<\/a> capability for nicer looking query. To enable this, we’ll override OnModelCreating<\/code> and add a few more lines to DB Context:<\/p>\nclass SHDbContext : DbContext\n{\n\tpublic DbSet<WishlistEntry> WishlistEntries { get; set; }\n\n\tprotected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)\n\t{\n\t\tbase.OnConfiguring(optionsBuilder);\n\t\toptionsBuilder.UseSqlServer(Environment.GetEnvironmentVariable(\"DB_CONNSTR\")).LogTo(Console.WriteLine);\n\t}\n\n\tprotected override void OnModelCreating(ModelBuilder modelBuilder)\n\t{\n\t\tbase.OnModelCreating(modelBuilder);\n\t\tmodelBuilder.Entity<WishlistEntry>()\n\t\t .Property(e => e.ItemData)\n\t\t .HasConversion((itemData) => JsonConvert.SerializeObject(itemData), str => JsonConvert.DeserializeObject<ItemData>(str));\n\t}\n}\n\/\/ ...\n\/\/ finally, use the new converter like so:\nvar data = context.WishlistEntries.AsEnumerable().Where(d => d.ItemData.Category.Any(c => c.Name == \"Connected Home & Housewares\")).ToList();\n \/\/ also not ideal as we still have to filter on client side, effectively scanning all tabl<\/code><\/pre>\nThis simply shifts the JSON conversion code up the food chain, so developers won’t see it often. Unfortunately, this “out of sight – out of mind” approach won’t improve our query plan.<\/p>\n
Option three – JSON_VALUE function<\/h2>\n
It gets pretty clear at this stage that we need to be able to filter by json fields on SQL Server side. Luckily, Microsoft provides a few json-handling capabilities<\/a>. JSON_VALUE<\/a> function that allows extracting one value out of the blob via given path selector. This is going to eliminate the need to land our query and should hugely improve performance. Entity Framework does not support this function by default, but it should be very easy to add<\/a>:<\/p>\n\/\/ in the context class\n \/\/ adding static function to the DB Context\n[DbFunction(\"JSON_VALUE\", IsBuiltIn = true, IsNullable = false)]\npublic static string JsonValue(string expression, string path) => throw new NotImplementedException();<\/code><\/pre>\nour query then becomes something like this:<\/p>\n
var data = context.WishlistEntries.Where(d => SHDbContext.JsonValue(d.ItemData, \"$.category[0].name\").Contains(\"Connected Home & Housewares\")).ToList();<\/code><\/pre>\nAnd this is the correct answer. However, we can further improve performance by adding an index. Indexing the whole json blob will not help, but we can create a computed column with the value we care about and index that instead:<\/p>\n
-- add computed column\nALTER TABLE WishlistEntries ADD ItemCategory AS JSON_VALUE(ItemData, '$.category[0].name')\n-- index the value\nCREATE INDEX IX_ItemCategory ON WishlistEntries(ItemCategory) INCLUDE(CustomerId, ItemSource, ItemData);\n-- finally, test if the query picks up index by running the following and checking the Actual Execution Plan:\n\/* test queries *\/\nSELECT * FROM [WishlistEntries] WHERE JSON_VALUE(ItemData, '$.category[0].name') = 'Connected Home & Housewares'<\/code><\/pre>\nThis enhancement also happens to simplify our code a bit as we can now rely on computed column value when writing queries in EF:<\/p>\n
\/\/ add computed column to WishlistEntry\nclass WishlistEntry\n{\n\n\tpublic int Id { get; set; }\n\tpublic int CustomerId { get; set; }\n\t[MaxLength(300)]\n\tpublic string ItemSource { get; set; }\n\tpublic string ItemData { get; set; } \n\n\t[DatabaseGenerated(DatabaseGeneratedOption.Computed)]\n\tpublic string ItemCategory { get; set; }\n}\n\n\/\/ finally the query will be like:\nvar data = context.WishlistEntries.Where(wle => wle.ItemCategory == \"Connected Home & Housewares\").ToList();\n<\/code><\/pre>\nConclusion and area for improvement<\/h2>\n
Keen eyed readers may have noticed that we hardcoded query to first array item. This was a deliberate simplification we opted for to illustrate the point. It should be possible to query an array using OPENJSON … CROSS APPLY like so, but we haven’t explored this avenue far enough to see if we’d ultimately succeed:<\/p>\n
var data = context.WishlistEntries.FromSqlRaw(@\"SELECT TOP 1 wishlist.*\n FROM [WishlistEntries] as wishlist\nCROSS APPLY OPENJSON(ItemData, '$.category') WITH (CatName VARCHAR(200) '$.name') as categories\nWHERE categories.CatName = {0}\", \"Connected Home & Housewares\").ToList();\n<\/code><\/pre>\nEven though our application is far from complete we are in a good place. Equipped with these techniques, we can handle almost anything users throw at us and are still able to query data fast enough. The code for this exercise is available on GitHub<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"