Trying to determine how to handle internal Object mapping into and out of database
Closed this issue · 3 comments
In looking at the upcoming featureset of Sql Server 2016 where you can now store JSON natively in a column, as well as the fact that this exists across other databases as well, I'm wondering what it would take to add an attribute to an embedded POCO Property to specify that the data/child object should be serialized as JSON when going into the database, and then deserialized when coming out as part of a query. In the case of existing Sql Server versions lower than 2016 or even other databases like sqlite, one could envision storing the data in a nvarchar(max) or varchar(max) column. Taking it a step further, what about making it a arbitrary serialization/deserialization attribute. For example, I could serialize as Xml, Json, or even CSV. Thoughts? Is this something that could potentially be useful to anyone else?
I don't think it would be compatible with the sqlfu design mindset. In ver 3 alpha I had the ability to register mappers for value objects (to db and back), but I've decided it would be a very misused feature.
Can you elaborate on your thinking for how you think it might be misused. That potentially sounds like exactly what I was describing, only for reference objects, so I'd be curious on your reasoning. Also am curious if you've thought about how SqlFu will support JSON columns in the 3.0 release?
My exact usage is for a scenario where we want to embed an array of dynamic metadata to a specific row instance that I wouldn't want to manage externally in another table from the sheer number of these "property" lookup tables that this entails across all of our different tables. We initially thought about going with a document database like mongo, but the lack of true joins and the ease of sql just don't warrant this. Plus the addition of the json functionality in Sql 2016 fits in extemely well with our model.
SqlFu is a bunch of helpers on top of ADO.Net . I don't want it to resemble an ORM much. I didn't know about that feature of sql server 2016, however it doesn't change many things because "In SQL Server 2016, JSON will be represented as NVARCHAR type." . So, the corresponding .Net type will still be string.
For building sql using the strongly typed builder, I'll probably add a couple of new extension methods. I haven't written about it, but it's simple enough to add your own methods that will generate sql.