0

Is there anyway to map multiple SQL Server databases in a single EF context? For instance I'm trying to do something like this

select order from context.Orders
where context.Users.Any(user => user.UserID == order.UserID)

And I'd like to get generated SQL along the lines of:

select .. from store.dbo.order where userID in 
(select userID from authentication.dbo.user)

and note that the database names are different - store in one place, authentication in the other.

I've found a few articles that deal with multiple schema ('dbo' in this case), but none dealing with multiple database names.

Improve this question
0

3 Answers 3

Reset to default
1

As a potential workaround, you could create a view of the table from the second database in the first database and point your mappings to the view.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Yeah, I would just do a view that links in the tables from the other database.
0

I'm pretty sure this isn't possible. The context derives from DbContext.

A DbContext instance represents a combination of the Unit Of Work and Repository patterns such that it can be used to query from a database and group together changes that will then be written back to the store as a unit. DbContext is conceptually similar to ObjectContext.

Configuration (connection string, schema, etc) for a DbContext is specific to a single database.

Improve this answer

Comments

0

It's not possible. A notion of context is below notion of a database, and allowing this would probably be a bad practice. Allowing such a thing could cause developers to forget that they are dealing with two databases, and to take care about all performance implications that come from that.

I imagine you should still be able use two contexts and write elegant code.

var userIds = AuthContext.Users
    .Where(user => user.Name = "Bob")
    .Select(user => user.UserId)
    .ToList();

var orders = StoreContext.Orders
    .Where(order => userIds.Contains(order.UserId))
    .ToList()

First execute query on authentication database context, in order to provide parameters for second query.

Improve this answer

5 Comments

FYI - I tried your suggestion out and got the following error: "The specified LINQ expression contains references to queries that are associated with different contexts." There are quite a few stackoverflow items for that error - all indicating that you can't mix contexts inside a single LINQ, and that you need to separate the queries, and pass an ienumerable from the first query results to the second.
Principle remains the same. Separate this query in two parts (first retrieving IDs, and then querying over it)
But that did make me wonder if the type of database separation we have is a bad practice or not. We have databases separated by product silo - but we also have common concerns (like users and authorizations) in yet a different database but the same database instance, with the intent to share that info with the various product silos. You can write a single SQL query that spans the databases, so I expected similar functionality in EF, and I'm really surprised that EF doesn't support it.
I'm not a specialist for database design, but I think that for such separation of concerns database schemas were intended. Not to say that separating databases is a bad design, but each approach has it's bad side. I don't take allowing a single database to be fault for green field projects, but I can imagine it's frustrating for you.
fyi - I tried to upvote you but I don't have sufficient stackoverflow reputation yet.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.