While working with Entity Framework Code First model, I’ve faced with a need to extend the functionality of the database by writing some T-SQL code. In this scenario my database (primarily, database tables) would be created by EF Migration. Stored procedures, functions and views will be created in another separate database project.
Now SSDT needs to know what tables would be in my database in order to resolve the references correctly and to allow me to write queries. Unfortunately SSDT does not have any capability to connect to existing database and to provide a possibility to write to code upon this database. Th only option is to use reference either to a SSDT database project or a Data-tier application (DACPAC file). I will use the last option.
- Create new database project in your solution.
- Extract your database as a DACPAC file (of course, after a successful migration of EF model).
- Right-click References node inside database project in Solution Explorer.
- Choose Add Database Reference…
- Choose Data-tier Application (.dacpac) and select the file you’ve extracted in step 2.
- Select “Same database” option in Database location dropdown list.
There are a couple of tricks you can do to make you life a bit easy.
- You can write a script that will drop stored procedures and extract a “clean” DACPAC (only tables). This can be done, for example, with PowerShell script (look at my previous post Extracting DACPAC From SQL Server Database With PowerShell
- By default SSDT copies DACPAC file from original folder to your project. But I want that DACPAC would stay in one place, in the folder with PowerShell extractor. This time I need to go to Properties of my newly created database reference and change Copy Local parameter to False.
- In addition, if you’re bored with warnings about unresolved references, you can switch them off by checking a box in the Add Database Reference dialog or in Properties window by setting Suppress Reference Warnings parameter to False.