Visual Studio database project can't create a view over a synonym

Question

I have the situation where I'm working in a new database, built using a Visual Studio 2010 database project, which has a number of synonyms defined which point to our legacy database.

Our plan is to create views over these synonyms; the tables being referenced are horrible, so the views will mangle the data into a better format; after that we will use Entity Framework to provide an ORM for these views (the idea being that we can then swap the views for real tables as we migrate data).

Problem: I can script the synonyms in the database project with no problems. However, when I try to create a view which references these synonyms, I am confronted with an error similar to:

Error  1  SQL03006: View: [dbo].[Person] has an unresolved reference to object [dbo].[ma_contact].

...where [dbo].[Person] is the new view and [dbo].[ma_contact] is the synonym for the legacy table.

Workaround: Place all view creation scripts in Script.PostDeployment.sql, doing the if-exists-then-drop-then-create logic manually.

This is less than ideal, although it's livable-with for now. Anyone have any ideas as to how to do these views "properly"?

Answer 1

Have you tried adding a database reference to your original database? You'd have to extract its format into either a dacpac (for SSDT SQL Projects) or a DBSchema file (for DB Projects). Once you've done that, store it in some place the project can find it. We used a "Schemas" folder at the root of our DB Projects so all projects could reference it. In your project, right click the "References" folder and add a Database Reference. Search for your DBSchema/Dacpac file and use that, along with the name of the database. That should let your synonym resolve properly so you can reference it in your views.