What is the best way to store Units.net quantity in DB?

[alirezakamranian]

I saw too many approaches to store quantity in Db like storing value and Unit name in db record and convert it to expected quantity with library's methods.
but i wanna know is there any beter way?

[lipchev]

It's an interesting question to which of course there isn't one good answer. Let me try to break-down the different options:

1. Using a single column for storing the Value of the quantity, converted to some base unit (e.g. storing all values as MassUnit.Gram or VolumeUnit.Milliliter)- this is probably the only option that can support direct database queries (anything else would require some form of a CASE switching query on the set of applicable units). This option assumes that the UI is responsible for actually selecting an appropriate display unit for the quantity.
2. Using two columns mapped to the {Value, Unit} pair: this assumes that the user is expecting to see the quantity in the same unit as it was entered. The slight problem here is when dealing with the non-default abbreviations- if the UI supports the input of the quantity using all of the unit abbreviations, then you wouldn't be able to restore the exact quantity string (e.g. expecting "ts." instead of "tsp" for the MetricTeaspoon). This is of course not a huge issue, but something to keep in mind when mapping custom abbreviations.
3. Using a single string-based column- the code for parsing a quantity from string is still very slow, I doubt it would beat option 2) (but would be curious to see a comparison).

[angularsen]

Good summary, thanks for writing it out. We could maybe add this to our wiki as it comes up every now and then.

[angularsen]

I would personally do something along number 2, and serialize decimal Value and string UnitName similar to how we do JSON: https://github.com/angularsen/UnitsNet/wiki/Serializing-to-JSON,-XML-and-more

I would also, if I only used a few quantities and units, choose my own custom serialization format to avoid any breaking changes in names of UnitsNet, which do happen. Something like this:

IQuantity myQuantity = ...;

var myDbEntity = new MyDbEntity { 
    Value = myQuantity.Value,
    UnitName = myQuantity switch { 
        LengthUnit.Meter => "Length:Meter",
        LengthUnit.Centimeter => "Length:Centimeter",
        MassUnit.Kilogram => "Mass:Kilogram",
        // ... and any other units we want to support serializing
        _ => throw new NotImplementedException("Unit not supported for serialization to SQL: " + myQuantity.Unit);
    }
};

You would then have to parse it back out with a simple string.Split and similar switch conditions.
It's a boring one time job, but then you are in full control of any future changes to the library and will get compile errors if anything is renamed or removed.

[alirezakamranian]

Thanks a lot man

[angularsen]

Dropped a wiki here, pasting in the above.
https://github.com/angularsen/UnitsNet/wiki/Saving-to-database