CODEWITHSUNDEEP
c#vb.netlinq
Bastien Vandamme
Bastien Vandamme

Reputation: 18485

LinQ with JSON strings

Lets say I have a table USER in a SQL database with this structure:

FirstName    LastName    Age   Address   
------------`----------- ----- ----------------------------------------------------------------------------------------------------
Homer        Simpson     40    {"streetAddress": "21 2nd Street", "city": "New York", "state": "NY", "postalCode": "10021-3100"}
Homer        Simpson     40    {"streetAddress": "742 Evergreen Terrace", "city": "Springfield", "state": "NV", "postalCode": "89001"}

I would like to create a LinQ query to get all Homer Simpson living in Nevada. I'm using VB.NET and Entity Framework so I start with dbContext:

dbContext.Users.Where(Function(f As User) f.FirstName = "Homer",
                                          f.LastName = "Simpsom",
                                          f.Address.Contains("\"state\": \"NV\"")) 'This is where I block

Is this correct ? Yes it is but is this the good way to do it?

Upvotes: 0

Views: 110

Answers (1)

Avner Shahar-Kashtan
Avner Shahar-Kashtan

Reputation: 14700

What you're doing in your database schema is taking data that's structured in a relational, row/column SQL format, and putting inside one column data that's structured as a hierchical, key-value document. Naturally, this limits your ability to query the internal JSON data intelligently, and you're relying on a simple substring search which is fragile - what happens if there's no space between "state": and "NV"? Your query could break.

One possibility to improve this is to break the JSON into a relational structure. From your sample data, it seems like your data is relatively straightforward. You could create an Address table, save the JSON into it, each field into a column, and link that to your User table - or even put the Address columns directly in the User table.

Incidentally, JSON isn't the only non-relational hierarchical key-value format that I've seen people use. MS SQL has supported XML-typed fields since 2005, and includes special syntax to query the XML intelligently. I see that MS SQL 2016 will support JSON in a similar fashion, allowing you do query the data in the field, rather than the text. However, I don't know if other databases have that support as well.

Upvotes: 2

Related Questions