Cross-server, cross-database, multi-table federated queries

• The recent project used a cross-database multi-table search, the project is using MVC4+EF, the project used a lot of databases, each database has to build an edmx file, resulting in a large number of project EDMX files, so that a project is very large and complex, undoubtedly exacerbated the complexity of the programmer to write code.
• It can be more complicated if it's a multi-table check on a different server.
• Let me talk about my own ideas:

Home if we have 3 servers, the server A that operates the database, the second server B192.168.1.136, the third server C192.168.1.125

Note: Shut down the firewall on the server (query error)

We set up a User_ta table on the UserA database on a server, a USER_TB table for the UserB database on B, and a UserC table for the USER_TC database on C

User_ta table:

USER_TB table:

USER_TC table:

Instance code:

Select *  from(Select    * from OpenDataSource(    'SQLOLEDB',    'Data source=192.168.1.136; User Id=sa; password=000'). UserB.dbo.UserTB asT1) asTT1Join(Select    * from OpenDataSource(    'SQLOLEDB',    'Data source=192.168.1.125; User Id=sa; Password=123'). UserC.dbo.User_TC asT1) asTt2 onTt1. Userid=Tt2. Userid

Although the above code can be queried, but gives a tedious feeling, then we should simplify it, we think of the view.

First, Server B and C tables are generated in Server a view

Create ViewView_user_b as(Select    * from OpenDataSource(    'SQLOLEDB',    'Data source=192.168.1.136; User Id=sa; password=000'). UserB.dbo.UserTB asT1) astt1)Create ViewView_user_c as ( Select    * from OpenDataSource(    'SQLOLEDB',    'Data source=192.168.1.125; User Id=sa; Password=123'). UserC.dbo.User_TC asT1)

Then we can simplify the query:

Select *  from  as  T1  Join as on T1. UserID=T2. Userid

This is the implementation of cross-server, cross-database multi-table search

Cross-server, cross-database, multi-table federated queries