Breaking news: Why does Entity Framework not support cross-database query?

In the previous article, I mentioned that "for cross-database queries, we have not found a method implemented through LINQ to entities ". Later I think about it. Theoretically speaking, it is not difficult to implement cross-database queries. Compared with non-Cross-database queries, I only have one more database name, for example, the following non-Cross-database query statement:

 
Select[Text]FromDBO. blog_postbodyWhereID=3560
 

Cross-database query statement:

Select[Text]FromCnblogstext. DBO. blog_postbodyWhereID=3560
 

In Entity Framework, we can use totable ("table name") to specify the table name for ing. Entity Framework constructs an SQL statement based on the specified table name, if you add the Database Name and schema name (that is, totable ("database. DBO. table Name "), can cross-database queries be implemented?

Therefore, based on this experiment, we found the secret that Entity Framework cannot perform cross-database queries: Entity Framework processes the table names specified in totable, add brackets. If no schema name is specified, [DBO] is added before the table name, for example, totable ("table name"). The table name in the SQL statement is [DBO]. [Table name]. The improper handling when brackets are added has become the culprit.

We tried to assemble some special strings and cheated Entity Framework. Currently, we are using reflector in the Entity FrameworkCodeFind the murderer. Only by finding the murderer and knowing the means of committing the crime can we know whether it is possible to solve the problem.

The following code is used:

Blogdbcontext code:

 Public     Class  Blogdbcontext: dbcontext
{
    Public  Dbset  <  Posttext  >  Posttexts {  Get ;  Set  ;}

  Protected     Override     Void  Onmodelcreating (dbmodelbuilder modelbuilder)
{
Modelbuilder. Entity  <  Posttext  >  (). Totable (  "  Blog_postbody  " );
}
} 

The code for querying the LINQ to entities file is as follows:

 
Using(Blogdbcontext Context= NewBlogdbcontext ())
{
(From tInContext. posttexts
WhereT. ID= 3560
Select T. Text). firstordefault ();
}
 

1. This is a non-Cross-database query. The generated SQL statement is as follows:

 Select    Top  (  1  )
  [  Extent1  ]  .  [  Text  ]     As     [  Text  ]  
 From     [DBO].[Blog_postbody]     As     [  Extent1  ]  
  Where     3560    =     [  Extent1  ]  .  [  ID  ] 

The specified table name is blog_postbody, and the SQL statement becomes [DBO]. [blog_postbody].

2. Cross-database query:

The blogdbcontext code is changed:

Modelbuilder. Entity<Posttext>(). Totable ("Cnblogstext. DBO. blog_postbody");
 

Generated SQL statement:

 Select     Top  (  1  )
  [  Extent1  ] .  [  Text  ]     As     [  Text  ]  
  From    [Cnblogstext. DBO].[Blog_postbody]     As     [  Extent1  ]  
  Where     3560     =    [  Extent1  ]  .  [  ID  ] 

[Cnblogstext. DBO]. [DBO]. [blog_postbody] is correct when cnblogstext. DBO is added to the brackets.

Try to cheat Entity Framework and change the table name:

 
Modelbuilder. Entity<Posttext>(). Totable ("Cnblogstext]. [DBO.Blog_postbody");
 

Generated SQL statement:

 Select     Top  (  1  )
  [  Extent1  ]  .  [  Text  ]     As     [ Text  ]  
  From     [Cnblogstext]].[DBO].[Blog_postbody]     As     [ Extent1  ]  
  Where     3560     =     [  Extent1  ]  .  [  ID  ] 

Half of the brackets are added, and spoofing fails...

To be clear, you only need to find out the code that the Entity Framework processes. Yesterday, reflector struggled to find the failed code for one night...

This problem has been solved. See the truth: Why does Entity Framework not support cross-database queries (solution attached)