2

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
khoailang
  • 725
  • 1
  • 15
  • 32
  • Can you state the problem more clearly. Is the problem that objects generated from views don't have PK properties? Does the linked server have anything to do with this? I believe there are workarounds for the views missing PK's issue. – Nick.Mc Jul 13 '15 at 04:46
  • hi Nick.McDermaid, yes, the objects have no PK, have no navigation properties. Can you drive me to a guide? – khoailang Jul 13 '15 at 06:59
  • Here is one explanation on how to trick EF into thinking your view has a PK: http://stackoverflow.com/questions/1013333/entity-framework-and-sql-server-view. The accepted answer is a bit of a workaround. Further down it explains how you can achieve it without hacking your view. – Nick.Mc Jul 13 '15 at 13:41
  • thank you all, I realize I don't need to use the "linked server". I am considering to use the "replication" instead. – khoailang Jul 22 '15 at 09:43

2 Answers2

1

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).

bubi
  • 6,414
  • 3
  • 28
  • 45
  • hello bubi, I didn't run the EF Power Tools in the central database A. I run the tool in the database B. The classes generated don't have navigation properties. You mean I will manually add navigation properties? – khoailang Jul 13 '15 at 10:11
  • The navigation properties are generated automatically. Wich DBMS is B? Are you using EDMGEN? – bubi Jul 13 '15 at 10:24
  • no, I am using the EF Power Tools, navigation properties are not generated for views objects. SQL Server 2012 for all A and B. Thanks – khoailang Jul 13 '15 at 16:52
  • thank you all, I realize I don't need to use the "linked server". I am considering to use the "replication" instead. – khoailang Jul 22 '15 at 09:43
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)SQL Synonym Creation

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.

Pastor Cortes
  • 192
  • 5
  • 13