Same query, multiple dbcontext

Question

0.00/5 (No votes)

See more:

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.

Posted 8-Mar-17 2:21am

Steve Van Lint

Updated 12-Mar-17 23:31pm

Add a Solution

3 solutions

Solution 1

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.

Posted 8-Mar-17 2:49am

Dave Kreskowiak

Comments

Richard Deeming 9-Mar-17 12:09pm

Not sure I'd agree with that.

EF only cares about the tables and columns defined in the model. You can have other objects in the database that EF doesn't know or care about. You can even have other columns in the EF tables that EF doesn't know or care about.

The only problem you'd have is if you're inserting a record through EF, and one of the unknown columns is required and doesn't have a default value.

You might want to turn off migrations if you're not using EF to define the database. That way, you won't get the __MigrationHistory table.

But in general, so long as all of the tables and columns for the entities in your EF model exist, and have compatible data types, it should just work.

Solution 2

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?

C#

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:

C#

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

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

C#

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

Posted 9-Mar-17 6:21am

Richard Deeming

Comments

Steve Van Lint 9-Mar-17 13:27pm

This would indeed eliminate the need to create a new DbContext for each database server but I would still need to perform the same query x times for each database server.

I would still need to be able to perform updates on the database.

Currently I am trying an approach with linked servers where I create on db1 the linked servers for db2 and db3 and then perform a union query with raw SQL.

Thanks for the suggestion though

Steve Van Lint 13-Mar-17 5:34am

I will use this conditional approach to creating the DbContext after all in combination with linked servers, see my latest answer.

Thx for the tip

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Steve Van Lint · Accepted Answer · 2017-03-12T23:31:00

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.