Dynamic database schema / expando object

[percramer]

Hi,

in my application there is the option to change the database from within the . So i have a schema that can change. So i can't create fixed dto classes for my tables. Most frameworks of course need this of course, so i was wondering if anybody might have a solution for this. Can FusionCache work with ExpandoObject for example

[jodydonetti]

Hi @percramer

in my application there is the option to change the database from within the

Within the 😅 ?

Can FusionCache work with ExpandoObject for example

FusionCache is based on the core abstractions IMemoryCache (for L1) and IDistributedCache (for L2), see here.

So anything that can be stored in a MemoryCache or serialized to a byte[] to be stored inside an implementation of IDistributedCache should work, including an ExpandoObject if the above assumptions are correct.

Did you try it?

An alternative of using an ExpandoObject would be to use either a Dictionary<K, V>, or a JsonObject/JObject or something like that.

Hope this helps.

UPDATE (5 min later)

I tried this piece of code (with an L1 only configuration):

dynamic expando1 = new ExpandoObject();
expando1.Name = "John";
expando1.Age = 30;

cache.Set("aaa", expando1);

dynamic expando2 = cache.GetOrDefault<ExpandoObject>("aaa");

Console.WriteLine($"NAME: {expando2.Name}");
Console.WriteLine($"AGE : {expando2.Age}");

And it printed this:

So it seems to work. Having said that, dynamic & friends are strange beasts, so I would be careful about running towards it, and I'd do a lot of tests about different usages, scenarios, L1 only, L1 + L2, etc.

[percramer]

Hi, thank you for testing it!

Within the 😅 ?

haha sorry. Within the end user application. So the user is allowed to add fields to tables from the end user gui (not every user of course, there is a user rights system). And i will then do a adhoc schema change to alter the database.

Most of the application works with direct SQL server communication and data tables in c#, so that gives no issues. But most frameworks like this one are more focused on fixed classes for dto objects etc. Which i can totally understand, but the dynamic nature of my application is one of the plus points which is used quite a lot.

So anything that can be stored in a MemoryCache or serialized to a byte[] to be stored inside an implementation of IDistributedCache should work, including an ExpandoObject if the above assumptions are correct.

Ok, then a different route might be a custom class design (or the jsonObject you mentioned). If the demand mostly is that it should be serializable to a byte[]

I do like to start using caching though, so now it is time to try to find a workable setup :-) Another thing is the option to cache an entire list of data (for combo boxes etc.). But that is already mentioned in a different discussion (and you also answered that).

[jodydonetti]

Most of the application works with direct SQL server communication and data tables in c#, so that gives no issues. But most frameworks like this one are more focused on fixed classes for dto objects etc. Which i can totally understand, but the dynamic nature of my application is one of the plus points which is used quite a lot.

Got it, but it seems to work with ExpandoObject, at least in my very brief test.
One thing I didn't understand is if you tried it and it didn't work, or you have yet to try it but feel like it would not work.

So anything that can be stored in a MemoryCache or serialized to a byte[] to be stored inside an implementation of IDistributedCache should work, including an ExpandoObject if the above assumptions are correct.

Exactly!
L1 only? Any object instance will work.
L1+L2? They must also be serializable to byte[], by using either an existing serializer (Json, Protobuf, MemoryPack, etc) or creating your own.

I do like to start using caching though, so now it is time to try to find a workable setup :-)

Yeah I suggest you do, because if the type of queries you do are cache-friendly you'll definitely get a big perf boost!

If you haven't seen it yet, I suggest to take a look at this recent community standup for Data API Builder from Microsoft, where they recently integrated caching (using FusionCache 😬).
It's all interesting, but if you want to go straight at the before/after numbers skip to 24min: with a cache duration of even just 1s, look at those numbers, it's insane.

Oh and use FusionCache or anything else really, but if you go with an alternative I'd suggest to watch out for Cache Stampede protection: without that you'll definitely have problems.

Hope this helps.

ps: if one of my answers satisfied you, please mark it 🙂