I have an existing database and need to add additional tables etc. I have no control over the database so I thought I would create my tables within the same database but under a different schema. From what I understand this would allow me to create my tables and not worry about conflicting names etc.
But how would I get access to both schemas at the same time through EF? Would I essentially have two different connections? Or would I have two different contexts?
The other option I have is to create another database but I don't know what pros and cons this has. Of course I would need to create INNER JOINS between my tables and the other tables. Is it possible to use inner joins in EF between 2 different Schemas or Databases?
The database I am using is SQL Server 2008 R2.
If you want to use join between tables from two schemas both these tables must be defined in the same context (that also means single connection will be used).
Once you use two different context types to map each schema separately or once you use two databases you will not be able to use entities from other schema in Linq-to-entities / ESQL queries and you will not be able to use navigation properties to table from other schema. Workarounds in such case are complicated because you must use alias or database view to get the table from the second database to the first one.
EF can work only with database objects mapped to current context and it can open connection only to single database (it also cannot use database prefix to access tables from different database on the same server).