EF 6.1.3 with “linked server”

问题:

I am using the SQL Server 2012 and EF 6.1.3

I have a central database A and another database B which is linked to the database A. The two databases are used for two different applications.

In the database B I have some views which is exactly as some table in the central database A.

What I am expecting is that when I insert/update/delete records in views of the database B, those records will be inserted/updated/deleted in the central database A.

For the application using the database B (this is the linked server, not the central database), I am using the EF to generate views (using power tools). Code generated looks fine, but, certainly, the generated entity doesn't have primary key properties, doesn't have navigation properties as well.

Can you help with a solution?

回答1:

I had a similar situation once, my problem was with a Stored Procedure in database B (I had access to this object via Linked Server in my Central Database, let's call it Database A. It was not possible to map Database B due to a few company policies), EF 6 does not let you map this Stored Procedure in your EDMX file when using the Database First approach, so what I figured out is a way to trick Entity Framework.

It's pretty simple, I just added an SQL Synonym in Database A, this object points to the View/Stored Procedure/Table in Database B (See attached picture)

Of course, I created the synonym for the Stored Procedure in Database B in my case, then in a method I executed the stored procedure like this:

SqlParameter paramNumber1 = new SqlParameter("@firstParameter", someVariable);
//We need to create a class for the Synonym result, which origin is: [Server].[Database].[dbo].[RemoteStoredProcedure]
var result = ctx.Database.SqlQuery<classForTheSPResult>("RemoteStoredProcedure @firstParameter", paramNumber1).ToList();

If you take this approach, you can perform a Raw SQL Query from your synonym View. For further information, check some the MSDN site, the query would look like this:

using (var context = new BloggingContext()) 
{ 
    var blogs = context.Blogs.SqlQuery("SELECT * FROM dbo.Blogs").ToList(); 
}

I hope my comments have been helpful.

回答2:

EF (Power tools) uses the system tables to retrieve the schema and if you run power tools on database A the navigation informations about linked tables cannot be retrieved. Probably the best way could be that you generate the classes for database B starting from database B (deleting same classes generated starting from database A) then mix the two databases.
At the end you mix the two models (adding navigation properties from model of A to model of B and vice versa).