SQL Server EF Core execute the same query on multiple databases

zdwk9cvp 于 2023-10-15 发布在其他

关注(0)|答案(2)|浏览(88)

I have multiple databases with the same schema/context and I want to query them all. I want to execute the same query on each database. I cannot use Linked Servers in SQL because the databases are created/deleted on the fly. Is there some way I can create some extension method for the context, so the query will execute on all of them? (sequentially or parallel)

I have already done this for one table but I want something that can work for all tables, so I don't have to write the same code for each table.

ConcurrentBag<FindConstructionSearchResult> results = new();
Parallel.ForEach(connStrings, new() { MaxDegreeOfParallelism = 16 }, dbConnectionInfo =>
{
    DB_DataContext dctx = DbHelper.Get_DataDBContext(dbConnectionInfo.DbName, dbInstance);
    List<FindConstructionSearchResult> sstavby = dctx.SStavby.Where(x => x.Nazev.Contains(request.ConstructionName) || x.Znacka.Contains(request.ConstructionName)).Select(x => new FindConstructionSearchResult()
    {
        ConstructionMark = x.Znacka,
        ConstructionName = x.Nazev,
        Database = dbConnectionInfo.DbName
    }).ToList();
    foreach (var item in sstavby)
    {
        results.Add(item);
    }
});

this is pretty fast, but I don't think it is elegant.

sql-server

来源：https://stackoverflow.com/questions/77268773/ef-core-execute-the-same-query-on-multiple-databases

2条答案

按热度按时间

osh3o9ms1#

You've got the basic implementation right, just need to wrap it up in a tidy method. EG

public class ParallelEfQuery
{
    public static List<T> ExecuteQueryParallel<T>(IEnumerable<DbContext> dbContexts, Func<DbContext,IQueryable<T>> queryFactory, int maxDegreeOfParallelism = 8)
    {
        var results = new System.Collections.Concurrent.ConcurrentBag<T>();

        Parallel.ForEach(dbContexts, new ParallelOptions { MaxDegreeOfParallelism = maxDegreeOfParallelism }, dbContext =>
        {
            var query = queryFactory(dbContext);
            foreach (var item in query)
            {
                results.Add(item);
            }
        });

        return results.ToList();    
    }
}

赞(0）回复(0）举报 2023-10-15

im9ewurl2#

The final implementation I used is following:

public static partial class DbHelper
{
    public static List<ParallelEfQueryResult<T>> ExecuteQueryParallel<T>(this IEnumerable<DB_DataContext> dbContexts, Func<DB_DataContext, IQueryable<T>> queryFactory, int maxDegreeOfParallelism = 8)
    {
        ConcurrentBag<ParallelEfQueryResult<T>> results = new();

        Parallel.ForEach(dbContexts, new ParallelOptions { MaxDegreeOfParallelism = maxDegreeOfParallelism }, dbContext =>
        {
            IQueryable<T> query = queryFactory(dbContext);
            foreach (T? item in query)
            {
                ParallelEfQueryResult<T> r = new(dbContext.Database.GetDbConnection().Database, item);
                results.Add(r);
            }
        });
        return results.ToList();
    }
    public static IEnumerable<DB_DataContext> CreateAllContexts(string[] DbNames, DbHelper.DbInstanceEnum Instance)
    {
        foreach (var item in DbNames)
        {
            DB_DataContext context = Get_DataDBContext(item, Instance);
            yield return context;
        }
    }
}
public record struct ParallelEfQueryResult<T>(string Database, T Result)
{

}

"DB_DataContext" is the actual DbContext I am using. The method "Get_DataDBContext" returns requested context for given Db Name and Sql Instance. I modified the return Type so I can see from which database the result is.

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server EF Core execute the same query on multiple databases

2条答案

相关问题

热门标签

最新问答