Useful SQL statements when writing EF Core 5 migrations

Entity Framework Core 5 is a great ORM and I love how efficient and concise it is. With the migrations mechanism enabled, you can generate the next migration based on changes applied to your model. This is so cool, but when it comes to other database objects, you are on your own. I mean – you can still use migrations, but you have to figure out a SQL statement yourself. Let’s take a look at some cool statements you can use with the SQL Server database.

CREATE OR ALTER

This is a very powerful command that literally just check if a database object exists, and it alters it or creates a new one based on that fact.

So instead of writing a statement like this:

I can use CREATE OR ALTER, like this:

Additionally, CREATE OR ALTER does not reset permissions, so you don’t need to re-apply them, which would be necessary with the first script.

CREATE OR ALTER can be used with the following objects:

  • STORED PROCEDURES (including natively compiled)
  • FUNCTIONS (Transact-SQL, including natively compiled)
  • TRIGGERS
  • VIEWS

Other database objects like tables or indexes cannot be handled with that statement. You can find more information about it in this Microsoft post.

Note: CREATE OR ALTER is available from SQL Server 2016 SP1.

DROP IF EXISTS

DROP IF EXISTS is a useful statement that can be used for many database objects. It will check if the object exists and if it does, it will drop it – all in one statement.

Instead of writing a statement like this:

Now we can write a simple one-liner:

Clean and simple. DROP IF EXISTS will work for many database objects, like:

  • AGGREGATE
  • ASSEMBLY
  • VIEW
  • DATABASE
  • DEFAULT
  • FUNCTION
  • INDEX
  • PROCEDURE
  • ROLE
  • RULE
  • SCHEMA
  • SECURITY POLICY
  • SEQUENCE
  • SYNONYM
  • TABLE
  • TRIGGER
  • TYPE
  • USER
  • VIEW

You can read more about it in this Microsoft post. Also Note: DROP IF EXISTS is available from SQL Server 2016.

Hope you like it, maybe you have some SQL statements you find useful at your work? Give me a shout or leave a comment. Cheers! 😉 

Leave a Reply

Your email address will not be published. Required fields are marked *