Is there any way to implement cross database querying in Entity Framework? Let's imagine I've two Entities User and Post, User entity is in database1 and Post is in database2, which means those entities are in separate databases. How should I get user's posts in Entity Framework ?
With cross-database queries, you can seamlessly query data from any database in the cluster, regardless of which database you are connected to. Cross-database queries can eliminate data copies and simplify your data organization to support multiple business groups on the same cluster.
EF Core 6.0 performance is now 70% faster on the industry-standard TechEmpower Fortunes benchmark, compared to 5.0. This is the full-stack perf improvement, including improvements in the benchmark code, the . NET runtime, etc. EF Core 6.0 itself is 31% faster executing queries.
I know this is an old question, but this is actually possible. If the databases are on the same server, then all you need to do is use a DbCommandInterceptor
.
As an example, if I attach a DbCommandInterceptor
to MyContext
, I can intercept all command executions and replace the specified table(s) in the query with my full-db paths.
public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) { // Here, I can just replace the CommandText on the DbCommand - but remember I // want to only do it on MyContext var context = contexts.FirstOrDefault() as MyContext; if (context != null) { command.CommandText = command.CommandText .Replace("[dbo].[ReplaceMe1]", "[Database1].[dbo].[Customers]") .Replace("[dbo].[ReplaceMe2]", "[Database2].[dbo].[Addresses]") .Replace("[dbo].[ReplaceMe3]", "[Database3].[dbo].[Sales]"); } base.ReaderExecuting(command, interceptionContext); }
The nice thing also about this approach is that the EF Model Mapping still works properly and respects column attributes, requires no views, and requires no stored procedures.
EF context does not support cross database queries. You need to expose posts in database1 through SQL View (or synonym) and use it as part of that database.
You can use ExecuteStoreQuery
, like:
var myOb = context.ExecuteStoreQuery<PlainOldClrObject>(
@"select *
from db1.dbo.table1 t1
join db2.dbo.table2 t2
on t2.t1_id = t1.id
where t1.id = {0}",
table1Id).FirstOrDefault();
You'd have to define a PlainOldClrObject
class with the columns as properties with getters/setters, like:
class PlainOldClrObject
{
public int Id ( get; set; }
public int Name ( get; set; }
...
}
No, you can't. You will have to create to contexts and do the joining your self. See here.
You could resolve to database trickery, creating a view in one database the reflects a table in the other one.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With