相同的查询，多个dbcontext [英] Same query, multiple dbcontext

问题描述

你好，



我为我们的云环境创建了一个监控系统。



环境是各个站点都有自己的数据库。由于每个站点都有多个任务在运行，因此我们需要在任务失败时进行拦截。还有其他我监控的东西，但想法是一样的。



情况如下：



我有我的监控网站（ASP.NET Core + EF Core），它从数据库服务器上的数据库获取数据。这个数据库执行预定的存储过程，从服务器上的所有数据库中捕获我想要监视的不同内容。



这很有效，也很快。我的第一个版本我在每个Web请求的所有数据库上都执行了游标，因此效率不高。



我现在面临的问题是我们正在整合新的数据库服务器，每个都有指向它的新环境。



我想避免为每个新的数据库服务器创建一个新的监控站点。



在我的新版本的监视器上，我想查询单个数据库上的每个服务器，它们存储该服务器上环境的不同错误。



到目前为止，我所做的是创建多个指向各自数据库服务器和错误数据库的DbContext类，但共享相同的实体。



要检索我的信息我在所有DbContext上执行相同的LINQ查询并在显示之前合并结果。



虽然这有效，但必须有更好的处理方式这个？



任何建议都是赞。



我尝试了什么：



我试过看类似设置的互联网，但无法找到任何匹配我的情况。

解决方案

是的，不要使用实体框架。使用它的问题是您的DbContext直接与编写上下文的数据库的确切版本相关联。如果数据库架构发生更改，则DbContext必须随之更改。这可以防止您对多个数据库使用相同的DbContext，即使它们在结构上有一个共同的表。



您必须切换到使用.NET中的Sql类针对每个单独的数据库或每个其他站点应用程序必须重做所有将其错误信息引导到专用于错误收集的单个数据库，您可以使用实体框架。

< blockquote>如果所有 DbContext 类共享相同的实体，为什么不创建一个 DbContext 类，并使用构造函数，它使用连接字符串的名称来创建指向不同数据库的实例？

  public  密封 类 ErrorsContext：DbContext 
 {
  public  ErrorsContext（ string  nameOrConnectionString）： base （nameOrConnectionString）
 {
} 
 
  public  ISet<错误>错误
 {
  get  { return 设置<错误>（）; } 
} 
} 
 
  public  密封 < span class =code-keyword> class  ErrorsContextConfiguration：DbConfiguration 
 {
  public  ErrorsContextConfiguration（）
 {
  //  不要尝试创建/更新数据库： 
 SetDatabaseInitializer（  new  NullDatabaseInitializer< ErrorsContext>（））; 
} 
} 

然后您可以使用单一方法查询任何一台服务器：

  public   static  IEnumerable<错误> SingleServerQuery（此 ErrorsContext上下文）
 {
  return  context.Errors。 ......; 
} 

然后针对每个上下文实例调用它并合并结果：

 < span class =code-keyword> public   static  IEnumerable<错误> MultiServerQuery（ params  ErrorsContext []服务器）
 {
  return  servers.SelectMany（ context = >  SingleServerQuery（context））; 
} 

我找到了一种让它工作的方法。



不同的数据库位于同一个域中，我对所有数据库都拥有必要的权限。



我要做的是创建链接服务器作为数据库对象。



我的主数据库将是db1，我将使用它来填充表中的错误。



来自db1我将创建链接服务器到db2和3（用于写入目的）。



Db2和3将有一个到db1的链接服务器，并且都有一个捕获的副本数据库，但没有表，只有存储过程。



这些SP会将他们的信息直接写入db1，在那里我将存储错误的原点（db1） ，db2或db3）。



这样我只需要查询1数据存储就可以获得所有相关信息。



为了更新环境，我需要先确定实例（列wi ll持有该信息）并有条件地对该信息进行更新。



为此，我可能会使用Richards建议并创建一个新的DbContext实例并传入连接参数

Hello,

I created a monitor system for our cloud environments.

The environments are individual sites with their own database. Since each site has several tasks running, we need to intercept when a task fails. There are other things I monitor but the idea is the same.

The situation is as follows:

I have my monitoring website (ASP.NET Core + EF Core) which gets its data from a database on the database server. This database performs scheduled stored procedures which capture the different things I want to monitor from all databases on the server.

This works well and quickly. My first version I performed a cursor on all databases with each web request, so that was not too efficient.

The problem I face now is that we are integrating new database servers, each with new environments pointing to it.

I would like to avoid creating a new monitor site for each new database server.

On my new version of the monitor I would like to query each server on the single database that stores the different errors for the environments on that server.

What I did so far is to created multiple DbContext classes pointing to their respective database server and 'error' database but share the same entities.

To retrieve my info I perform the same exact LINQ query on all DbContext and combine the results before displaying them.

Although this works, there has got to be a better way of handling this?

Any suggestion would be appreciated.

What I have tried:

I tried looking on the internet for similar setups but was unable to find any matching my situation.

解决方案

Yeah, don't use Entity Framework. The problem with using it is that your DbContext is directly tied to the exact version of the database the context was written against. If the database schema changes, the DbContext has to change with it. This prevents you from using the same DbContext against multiple databases even though they have a table structurally in common.

Either you have to switch to using the Sql classes in .NET against each separate database or each of your other site applications is going to have to be redone to all direct their error information to a single database dedicated to error collection, upon which you CAN use Entity Framework.

If all the DbContext classes share the same entities, why not just create a single DbContext class, and use the constructor which takes the name of a connection string to create instances pointing to different databases?

public sealed class ErrorsContext : DbContext
{
    public ErrorsContext(string nameOrConnectionString) : base(nameOrConnectionString)
    {
    }
    
    public ISet<Error> Errors 
    {
        get { return Set<Error>(); }
    }
}

public sealed class ErrorsContextConfiguration : DbConfiguration
{
    public ErrorsContextConfiguration()
    {
        // Don't attempt to create / update the database:
        SetDatabaseInitializer(new NullDatabaseInitializer<ErrorsContext>());
    }
}

You can then use a single method to query any one server:

public static IEnumerable<Error> SingleServerQuery(this ErrorsContext context)
{
    return context.Errors. ... ;
}

and then call that against each context instance and combine the results:

public static IEnumerable<Error> MultiServerQuery(params ErrorsContext[] servers)
{
    return servers.SelectMany(context => SingleServerQuery(context));
}

I have found a way to get this to work.

The different db's are in the same domain and I have the necessary rights on all of them.

What I will do is create linked servers as database objects.

My main db will we db1 and I will use this to populate the tables with the errors.

From db1 I will create linked servers to db2 and 3 (for write purposes).

Db2 and 3 will have a linked server to db1 and will both have a copy of the capture database but without the tables, only the stored procedures.

These SP will write their info directly to db1 and there I will store the point of origin of the error (db1, db2 or db3).

This way I need only query 1 data store to get all relevant info.

In order to update an environment I will need to first determine the instance (a column will hold that info) and conditionally on that info do an update.

For this I will probably go with Richards suggestion and create a new DbContext instance and pass in the connection parameter.

这篇关于相同的查询，多个dbcontext的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！