Linq and Entity Migrations with custom schemas in PostgreSQL

Question

With PostgreSQL, I can query tables in a specific schema or across multiple schemas at the same time. I am wondering how to handle two scenarios:

• Do you specify either multiple schemas or one schema when executing a query using Linq for EF?
  • I think I can execute some voodoo connection string, but it looks like it can get very ugly very quickly.
• How do you migrate objects for multiple schemas?
  • You can specify a schema as a DataAnnotation for an object (via [Table] ), but you cannot specify multiple schemas at once (from what I can say).

Any help is generally much appreciated.

Answer 1

For # 1, there is no way to work with schemas directly in the Linq-To-Entities query. I believe that they add some features here in EF6, but I assume that you do not want to wait until then.

There is one work that I used in SQL Server 2008 using Synonyms (I believe PostgreSQL also has Synonym functionality).

For example, if your table names are dbo.Address and read.Address , you must create a Synonym for read.Address with the name read.Address_read , then you will add Synonym to your EF directories, instead of the table itself. This gives you the ability to execute any query on multiple schemas at the same time, because EF believes that these tables have different names.