Use Custom SQL OnModelCreating and/or immediately after

user_550314 0
Viewed 103

I wish to add custom SQL to my model creation.

(Right now I want to do that because I have used strongly typed ids in my domain model; so now ef core won't let me use .UseIdentityAlwaysColumn() on them. (As of 2021 this is a still-open issue). Even it it did, I also want to add specific Postgres sequence options).

A simple workaround is just a single line of Alter Table Alter Column... sql straight after the model creation.

I can see that MigrationBuilder.Sql() can do custom sql. So

  • Can ModelBuilder do custom Sql? I can't find it.
  • Alternatively, can I shoehorn a short Migration into the OnModelCreating()?

I wish to keep all the data definition code in sync in one place, not have most of it in OnModelCreating but bits of it elsewhere.

entity-framework-core
1 Answers

The short answer to both your questions is no. Or if I can use your phraseology, as of 2021 this is still not possible.

Seriously, EF Core is ORM, thus the main focus is on M(apping). Physical database attributes are not a priority, given the fact that one can use EF Core just to map to an exiting database (a.k.a. Database first). There is some limited support for indexes (not used by EF Core) and small set of other physical attributes, but no views, synonyms, triggers etc. The only SQL supported is in fact HasDefaultValueSql.

I wish to keep all the data definition code in sync in one place, not have most of it in OnModelCreating but bits of it elsewhere.

OnModelCreating is creating the mappings. At the time it is called, there is no real database involved. The model could be created for generating a migration, but that's only one (an completely optional) of the many usage scenarios. That's why you can't "execute" anything there. All you can do is to specify metadata (a.k.a. annotations) which then eventually are processed by the services responsible for different functionalities. Migration SQL generator is one of them, but it needs to understand these annotations when processing the corresponding operations. Which basically is the definition of supporting something or not.

In theory you could create your own annotations, provide custom metadata/fluent API for specifying them, but then you have also implement them for every database provider you want to support. This is a lot of work, practically impossible as every database provider implements the migration SQL generator for their specific attributes and DDL dialects.

So, whether what you wish it better or not, the practical approach would be to use what you got from ORM. Which currently is MigrationBuilder.Sql(). No more, no less. That's all. Period.

To recap shortly, if the questions are if there is some hidden "magic" way which you can't find, there isn't.

user_52025630
Related