DBContext and Repository Pattern run slowly

Question

in my solution there are two projects: a .net core class library named "Infrastructure" and a .net core web application (mvc) named "Admin". In the "Infrastructure" prj I've implemented the Repository Pattern for Application DBContext like:

public partial class ApplicationDbContext : DbContext
{
   ....
}

Interfaces:

public interface IGenericRepository<T> where T : class
{
    T Add(T t);
    ....
}

public interface ICustomersRepository : IGenericRepository<Customers>
{
    Customers GetCustomerByCompanyCode(string companyCode);
    void InsertOrUpdate(Customers toSync);
}

Generic:

public class GenericRepository<T> : IGenericRepository<T>, where T : class
{
    protected ApplicationDbContext _context;

    public GenericRepository(ApplicationDbContext context)
    {
        _context = context;
    }

    public virtual T Add(T t)
    {
        _context.Set<T>().Add(t);
        _context.SaveChanges();
        return t;
    }

    ....
}

Specific:

public class CustomersRepository : GenericRepository<Customers>, ICustomersRepository
{
    public CustomersRepository(ApplicationDbContext context) : base(context)
    {
    }
    ....
}

In the Web Application: - Startup.cs

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<ApplicationDbContext>(options => options.UseNpgsql(Configuration.GetConnectionString("DefaultConnection"))); 
    ....
    services.AddScoped(typeof(IGenericRepository<>), typeof(GenericRepository<>));
    services.AddScoped<ICustomersRepository, CustomersRepository>();
    ....
}

- CustomersController.cs

private readonly ICustomersRepository rep;

public CustomersController(ICustomersRepository rep, ....)
{
    this.rep = rep;
    ....    
}

public async Task DoImport()
{
    ....
    rep.InsertOrUpdate(mapper.Map<Customers>(item.Result));
    ....
}

It works but very slowly: 300.000 insert in few hours. If I don't use Repository Pattern but try to insert calling ApplicationDBContext directly like:

using (var _context = new ApplicationDbContext())
{
     _context.Add(toSync);
     _context.SaveChanges();
}

the work time is: 300.000 insert in 6 minutes.

Why is this happening? Is the Repository Pattern the best choice to separate Data Access Layer in .net core projects? There are other solutions to do this? Tnx in advance.

Does anyone have an idea? It would seem that a new instance of the dbcontext at each insert increases performance. — f4x, Jan 09 '19 at 10:52

score 0 · Answer 1 · answered Jan 09 '19 at 11:07

Entity Framework and Repository patterns are useful for single record manipulation like an interactive program where the user creates a record, edits a field, adds a linked record and so on.

If you want to import any number of records without user interaction, both EF and the repository pattern add so much additional overhead, they are useless. EF will make a roundtrip to the database for every change. So if you add 300K records, that's 300K trips back and forth. That's insane.

What you need is a feature called BULK INSERT.

Refer to the SqlBulkCopy class to do it in your code.

Every database has their own tool to do this, so you need to read your databases manual, but that tool is incredibly fast. If you can use the specific tool for your database, 300K records should be a matter of seconds, not minutes.

Thanks for answer but this is just an example, a very simple performance counter. The main question is: why dependency injection of DBContext is lower than new instance? Meanwhile I found this [link](https://stackoverflow.com/questions/33041113/c-sharp-entity-framework-correct-use-of-dbcontext-class-inside-your-repository), that advises a new instance in all repository functions implementations. It would be a good thing for .net core DI too? — f4x, Jan 09 '19 at 13:04

DBContext and Repository Pattern run slowly

1 Answers1