Using two separate databases on same SQL server

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
flycast
Posts: 19
Joined: Mon 04 Feb 2019 19:39

Using two separate databases on same SQL server

Post by flycast » Mon 26 Jul 2021 14:21

Is there a way for a database novice to use tables from two different databases using the Visual Studio entity developer addin? I need to do some joins using database Foo.Documents and database Bar.Locations. I have created separate models for Foo and Bar but not together.

Database Foo is managed and updated by a source that I do not want to touch.
Database Bar holds supplemental data that I have created and have control over.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Using two separate databases on same SQL server

Post by Shalex » Tue 27 Jul 2021 10:06

There is no way to add tables from different databases in the same model. Please use two separate models.

flycast
Posts: 19
Joined: Mon 04 Feb 2019 19:39

Re: Using two separate databases on same SQL server

Post by flycast » Tue 27 Jul 2021 12:48

I discovered one way at the end of the day yesterday. I just finished testing it this morning. Create a view on the server. The view can be a query that uses any databases that the user has access to. When you update (and save) the Entity Designer model you can then add the view as what looks like a table.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Using two separate databases on same SQL server

Post by Shalex » Fri 30 Jul 2021 08:40

Views allow you to SELECT data but you may encounter issues to UPDATE data in the corresponding tables.

Post Reply