Scaffolding Enum with values from postdeploy.sql

[Kolejarz]

I'm just discovering the capabilities of scaffolding .sqlproj (SDK-style) and/or DACPAC files with EFCore Power Tools - is it possible to get contents of postdeploy.sql scripts and use them to automatically generate code? I want to treat .sqlproj as a source of truth for DB schema and use project itself to create DAL library.

My use case:

1. Database project contains separate .sql scripts for schemas, tables, stored procedures and views
2. There are separate tables aggregating values for statuses, types, tiers etc. - those should be Enum structures in C# context
3. I want to have post-deployment script populating values in those tables
4. Scaffolding should get those values from postdeploy.sql and create Enum for me

I'm interested in learning if it's even possible or what workaround can I try to make this happen :)
Am I on the right track though? I haven't found any related documentation describing similar approach, so maybe my thinking is wrong altogether?

[ErikEJ]

@Kolejarz Great find! 😉

It would be a seperate tool to do this, and you would probably need a live database as a source if you want to generate the enums (postdeploy.sql is just treated as a unstructured text file)

On a past project I can see we created and populated the enum tables in a Pre-Deployment script actually (to not include them in our model), created Enum classes in our DAL layer. I think we just created the classes manually.

How often does your enums change, and will a change not always involve changes in other places?

[Kolejarz]

So... necessity of presence of live database is actually something I want to avoid - or wire this entire process up in a way, where live database is only a short-lived instance spun up on developer machine for scaffolding purposes. Ideally creating a DAL library would require running single command where .sqproj or DACPAC is specified as target to provide straightforward way of creating and maintaining those projects. At the moment we deal with mixed approaches across teams, causing some mess that hopefully will be cleaned up a bit with introduction of database projects :)

You mentioned a separate tool and I think that's something I'm going to validate with Powershell - will report back if it makes sense after running it agains sample schema ;)

About frequency of changes - it's not something I'm really worried about (e.g. changing values of already defined enumeration), but as there are multiple DBs - we are going to need multiple DAL libraries split accross multiple projects - I'm trying to come up with a unified way of creating those libraries, while making sure that generated code is consistent accross solutions as new Enums are added. In DB they reside in a separate schema (at least most of the time :P that's also something we want to get under control with .sqlproj), so filtering them out of "regular" scaffolding shouldn't be an issue.