On Model First, how to set the Decimal field size.

Historically I have been used to creating my tables in SQL then importing them into LS, however recently I have ben trying to make the tables in the LS model first, then syncing back to the database.  it seems a very handy way to work,but I have run into a couple of problem, both related to Decimal fields.

Sometimes in my database I use the decimal fo quantities where I set the size to be 10,2 or something similar.  But I cannot find how to set this in the model.

Sometimes, we use the money type in SQL, which correctly maps to a decimal in LS. But again, how do I do this is I work model first?

In both cases I seem to have to create a decimal field in the model, then go into SQL to set the field size or set to money. 

I am sure this is a lack of knowledge on my behalf, so I would be happy to learn how to do this correctly.

It can be done but is a little fiddly.  Basically you need to create a special type in the designer, which you will map to decimal in your domain model but to money (or whatever) in your database.  Here is what you need to do:

* Go into the LightSpeed Model explorer (the tree view), right-click the root node and choose Add New User Defined Type.

* Give the new UDT a name such as Money.

* Set its CLR Type Name to System.Decimal.

* Set its Is Standard Data Type to False.

* Set its Database Type Name to money.  Or decimal(...,...) -- whatever you want.  Note we pass this to the database as a string so this is not guaranteed to be portable between databases!

Now you can create properties in your model of type Money.  They will still be decimal in LightSpeed but when you do an Update Database they will be created as money.

One niggle with this is that when you do an Update Database it will keep offering to change the column type to money even though it already is.  It is probably safe to let this happen because the ALTER TABLE will be a no-op -- but it will create a bit of noise especially if you are using migrations.