Entity Framework Code First, Navigation Property between different contexts/databases

Question

Hi I have 2 data contexts that maps different schemas on a SQL Server database, but then I need to create 1 sdf database file (SQL Compact) per schema and use the same data contexts, and I have some entities related like this:

//context 1
class A
{
    int Id
    ...
    ICollection<B> Bs
}

//context 2
class B
{
    int Id
    ...
}

On server it's easy I just need to specify the table for this relation, but on clients I have this entities splitted on different databases.

So I need a navigation property on 1 entity (A) from context 1 (database_A.sdf) to relate with 1 entity (B) from context 2 (database_B.sdf).

Thanks in advance.

I tried to edit my mappings but I didn't saw any option to do that on the Fluent API, only map totable. — Pedro Simões, Jan 15 '13 at 15:22
I have a relationship between them but I need to split in 2 different databases, in sdf (SQL Compact). — Pedro Simões, Jan 15 '13 at 15:24

score 3 · Answer 1 · edited May 23 '17 at 12:31

3

Anwering my own question, It's not possible to do what I need, because one context only can link to only one database, one way to do it it's to have an database attached like in SQLite, but with SQL Compact it's impossible.

Sources: SQLite - How do you join tables from different databases? SQL Compact 3.5 attach multiple DB/ cross-db query?

edited May 23 '17 at 12:31

Community

1
1

answered Jan 16 '13 at 10:24

Pedro Simões

228
2
10

score 1 · Accepted Answer · answered Jan 15 '13 at 15:32

1

Those classes you are implemented are not Contexts those are Entities. The Context in EF should inherit from ObjectContext or DbContext, In your case I think you have 2 separate Entities in 2 different Database. You can do this to pointing several database

// Associate with first entity
public Context1 : ObjectContext
{
    prop IDbSet<A> ADbSet{ get; set; }
    ...
}

// Associate with Second entity
public Context2 : ObjectContext
{
    prop IDbSet<B> BDbSet{ get; set; }
    ...
}

public void ChangeDb(string dbName)
{
    Context1 context = new Context1();
    context.ChangeDatabase(dbName);
}

Hope this help.

answered Jan 15 '13 at 15:32

saber

6,209
12
51
88

I answered this question just before you edit your post, so I thought you want to access to different database. – saber Jan 15 '13 at 15:44
Ok no problem, ChangeDatabase still doesn't help because A is in one database and B in another. – Pedro Simões Jan 15 '13 at 15:48
I your case, you need to read about sql server cross database query. I think this will help you to pass this issue. (http://www.mssqltips.com/sqlservertip/1717/handling-cross-database-joins-that-have-different-sql-server-collations/) – saber Jan 15 '13 at 15:54
Thanks Saber, but I need it for SQL Compact. – Pedro Simões Jan 15 '13 at 16:47
Recently I asked a question about cross database querying, check those answer (http://stackoverflow.com/questions/14341782/cross-database-querying-in-ef) – saber Jan 15 '13 at 16:49
What I need is somethinng like this: http://stackoverflow.com/questions/6824717/sqlite-how-do-you-join-tables-from-different-databases – Pedro Simões Jan 15 '13 at 16:49
Yes exactly, just check my question – saber Jan 15 '13 at 16:51

Entity Framework Code First, Navigation Property between different contexts/databases

2 Answers2

Linked