仅使用一个dbContext在多个数据库上执行投影 [英] Performing projections on multiple databases with only one dbContext

问题描述

我们正在将EF.Core用于当前项目。该项目有三个数据库，这是一个无法避免的真正麻烦。数据库具有相同的结构。因此，我们能够交换上下文并使用相同的EF模型进行CRUD操作。

我们有一个特别复杂的查询，将从传统ADO移植过来.NET原始SQL。问题是这是一个跨数据库查询。要复制此问题，我们真正需要的是一种从同一dbContext中的三个数据库中获取所有三个查询表的方法。

绝对的噩梦去，尝试了很多东西。是否在EF Core中继承了按层次结构的表（TPH）表。我认为无法针对多个数据库完成此操作。这是我们使用的有用站点。 http://learnentityframeworkcore.com/inheritance

因此，我们想知道是否有人可以使用EF来获得有关此方面的任何体面的信息。

解决方案

好，所以我相信我们找到了一种使用EF Core进行数据库跨数据库查询的方法，不在共享内存中。因此，我将以库存系统为例。我希望它对某些人有用。

首先需要在主表上为SQLSERVER db创建同义词，因此关联表都链接在一起。 / p>

然后我们的EF模型和派生类应如下所示。

  public abstract class Stock 
 {
 public int ID {get; set;} 
 public string stockName {get; set;} 
} 
 
 [表（ dbname.scheme.Stock，模式= dbo）] 
公共类WinterStock：股票
 {
 public WinterStock（）：base（）{} 
} 
 
 [Table（ dbname.scheme.Stock，Schema = dbo）] 
 public class SummerStock：股票
 {
 public SummerStock（）：base（ } {} 
} 
  

在我们的测试方法中，我们有...

  var query = _context.Set< WinterStock>（）。Join（_context.Set< SummerStock>（），winterStock => winterStock.stockName ，summerStock => summerStock.stockName，（summerStock，winterStock） => new {summerStock，winterStock}）; 
 var结果= query.ToList（）; 
  

We are using EF.Core for a current project. The project has three databases which is a real nuisance that can't really be avoided. The databases have the same structure. So we are able to swap contexts and use the same EF Model to do CRUD operations.

We have one particularly complex query that we are going to be porting over from traditional ADO.NET raw SQL. The problem is it is a cross database query. To replicate this issue what we really need is a way to have all three tables for the query in question, from the three databases within the same dbContext.

Absolute nightmare to get going, tried alot of stuff. Did the Table Per Hierarchy (TPH )stuff which is for inheritance in EF Core. I don't believe it can be done with regards to multiple databases. Here is a useful site that we used. http://learnentityframeworkcore.com/inheritance

So we wondered if anyone has any decent information on this can be achieved using EF.

解决方案

Ok, so I believe we have found a way to do cross db queries using EF Core with execution happening on the database and not in shared memory. So I shall do this with a stock system as an example. I hope it is of use to somebody.

First of all synonyms need to be created for SQLSERVER db, on the master table, so associated tables are all linked together.

Then our EF models and derived classes should be as follows.

 public abstract class Stock
 {  
   public int ID{get;set;}
   public string stockName{get;set;}
 }

 [Table("dbname.scheme.Stock", Schema = "dbo")]
 public class WinterStock : Stock
 {
   public WinterStock() : base() { }
 }

 [Table("dbname.scheme.Stock", Schema = "dbo")]
 public class SummerStock : Stock
 {
  public SummerStock() : base() { }
 }

In our test method we have...

  var query = _context.Set<WinterStock>().Join(_context.Set<SummerStock>(), winterStock => winterStock.stockName,summerStock => summerStock.stockName, (summerStock,winterStock) => new { summerStock, winterStock });
  var result = query.ToList();

这篇关于仅使用一个dbContext在多个数据库上执行投影的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！