0

I have 2 databases. In 1 database I have saved the names in Players table. Example:

Players

Id       Name
1        John
2        Jack

In other database I have table:

Club

Id PlayerId
1  1
1  2

I use data service to manipulate with those 2 databases.

I am interested how can I connect those 2 data tables to retrieve the names from Players data table like this:

Data
ClubId PlayerId PlayerName
1      1        John
1      2        Jack

Do I have to retrieve data from both sources and create new list in memory(controller) with this and than use model to show data from both in view?

sensei
  • 7,044
  • 10
  • 57
  • 125
  • 1
    Why are they in two databases? – Dan Bracuk Nov 24 '13 at 21:51
  • Application structure. Back end, front-end are different apps. – sensei Nov 24 '13 at 21:52
  • Then what guarantee do you have that player id 1 in the club db has anything to do with id 1 in the players db? – Dan Bracuk Nov 24 '13 at 22:03
  • I did not create database, I am only looking for the answer. – sensei Nov 24 '13 at 22:14
  • John, Have a look here http://stackoverflow.com/questions/2659942/entity-framework-4-and-multiple-database - This might help. Thanks, – Geek Nov 24 '13 at 22:38
  • Based on the link provided by Usman, it sounds like its technically possible to connect to two databases in one EDM. Even if you could, though, how would you maintain a proper PK/FK relationship in the model? Manually? That sounds problematic. – Brent M Clark Nov 25 '13 at 12:03
  • Also, why not fight this issue at the database where the real problem exists? I'm sure it's not the easy fix, but it sounds like the right one. – Brent M Clark Nov 25 '13 at 12:05

1 Answers1

0

If the player database is called Players and the club database called Clubs you could do the following: SELECT ClubId = C.ID, PlayerId = P.Id, PlayerName = P.Name * FROM Players.dbo.Players P INNER JOIN Clubs.dbo.Clubs C ON P.Id = C.PlayerId

In other words, to reference an object in another database you have to fully qualify the name: [db name].[schema].[table name]

Gavin
  • 491
  • 3
  • 5
  • Thanks for contribution. I know how to connect with SQL in ms sql management, but I need this in entity framework. – sensei Nov 24 '13 at 22:24