CODEWITHSUNDEEP
c#sql-servermultithreadingentity-framework-corethreadpool
Maxime Nadeau
Maxime Nadeau

Reputation: 21

Use Entity Framework Core with ThreadPool without reaching maximum SQL Server connection

I try to insert a big load of data to my SQL Server with out reaching the maximum connection.

I already tried to max the pool size in my connection string like this:

optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Echo;Max Pool Size=5000;Pooling=True;");

Is it possible to use one connection for each thread?

Here is my code

    public static void GenerateMeridien()
    {
        List<Parallele> paralleleList = null;

        using (var context = new EchoContext())
        {
            paralleleList = context.Paralleles.ToList();
        }

        foreach (Parallele parallele in paralleleList)
        {
            ThreadPool.QueueUserWorkItem(new WaitCallback(MeridienTaskCallback), parallele);
        }
    }

    private static void MeridienTaskCallback(object paralleleObj)
    {
        Parallele parallele = (Parallele)paralleleObj;

        // The context always reaches maximum connection because the garbage collector is not fast enough
        using (var context = new EchoContext())
        {
            List<Square> squareList = SquareLogic.GenerateSquare(parallele);
            context.Squares.AddRange(squareList);

            context.SaveChanges();
            Console.Write("*");
        }
    }

Upvotes: 0

Views: 235

Answers (1)

tmaj
tmaj

Reputation: 35105

Option A. Do work in parallel, save once

Do you have to save after each calculation? Can you do calculations in parallel and save after?

List<Parallele> paralleleList = null;

using (var context = new EchoContext())
{
    paralleleList = context.Paralleles.ToList();
}

List<Square> squares = DoAllWorkInParallel(paralleleList);

// Save once:
using (var context = new EchoContext())
{
    context.Squares.AddRange(squares);
    context.SaveChanges();
}

Option B. Save all the time

I think you should use Parallel.ForEach with MaxDegreeOfParallelism or use Task.WhenAll.

Parallel.ForEach

For example:

public static async Task GenerateMeridien()
{
    List<Parallele> paralleleList = null;

    using (var context = new EchoContext())
    {
        paralleleList = context.Paralleles.ToList();
    }

    Parallel.ForEach(
        source: paralleleList,
        parallelOptions: new ParallelOptions{MaxDegreeOfParallelism = x},
        body: parallele => Work(parallele));

    
}

private static void Work(Parallele parallele)
{
    using (var context = new EchoContext())
    {
        List<Square> squareList = SquareLogic.GenerateSquare(parallele);
        context.Squares.AddRange(squareList);

        await context.SaveChanges();
    }
}

Task.WhenAll

public static async Task GenerateMeridien()
{
    List<Parallele> paralleleList = null;

    using (var context = new EchoContext())
    {
        paralleleList = context.Paralleles.ToList();
    }

    var tasks = new List<Task>();
    foreach (Parallele parallele in paralleleList)
    {
        tasks.Add(WorkAsync(parallele));
    }

    await Task.WhenAll(tasks.ToArray());
}

private static async Task WorkAsync(Parallele parallele)
{
    using (var context = new EchoContext())
    {
        List<Square> squareList = SquareLogic.GenerateSquare(parallele);
        context.Squares.AddRange(squareList);

        await context.SaveChangesAsync();
    }
}

Upvotes: 1

Related Questions