Optimizing ASP.NET + EF6 for Bulk Insertion and Massive Data Selection: A Practical Guide

Introduction

A few days ago, one of our clients' accounting officials uploaded an Excel file with student bills and payments — a routine task, done every semester. Except this time, it wasn't routine. The validation API took almost 2 minutes just to process 200 rows, checking each one for duplicates against a database holding over 4 million records. Once validated, hitting "confirm" kicked off the real nightmare: 10+ minutes of waiting, staring at a loading spinner, hoping the connection wouldn't time out before the save finished.

Multiply that by every accounting officer, every semester, every batch of a few thousand students — and you've got a feature that was technically working, but practically unusable at scale.

So I decided to dig in. What I found wasn't one bug — it was a stack of small, very common EF6 mistakes, each one quietly compounding the next: row-by-row duplicate checks hitting the database individually, SaveChanges() called inside a loop, and query patterns that scaled linearly (or worse) with row count instead of staying flat.

After rewriting the pipeline, the same validation that took 2 minutes for 200 rows now handles 5,000 rows in 20 seconds. Saving, which used to take 10+ minutes, now finishes in 1 minute 20 seconds — for a batch 25x larger than before.

This article walks through exactly what was slow, why it was slow, and the specific EF6 techniques — bulk insertion, batched lookups, and smarter querying for massive data selection — that got it there.

The Setup: What We Are Trying to Achieve

Quick Context:

An accounting officer uploads an excel file containing the students' bills and payments. One API accepts the file and validates it and check duplicates and returns a summary (Slow). Upon confirmation, another API inserts all the data from the uploaded excel file to the database (Suuuuuuuuuuper Slow).

Why Is It Harder than a typical CRUD API:

As an institute grows, its data volume grows exponentially and reaches a point when normal database operations become expensive both in terms of memory and time consumption. Therefore, any sort of database operations that involves massive tables require extra care and optimization technique.

In This Article, We Will...

1. Learn some tricks to make the data loading lighter and faster.
2. Learn what to avoid.
3. Learn how to optimize bulk insertions.
4. Learn about some cool data structures.

Tricks to Optimize Data Loading:

This case is very much prevalent in massive tables that have many related tables via foreign keys, and you want to show the related data to the user in a report. For example, in a university, hundreds of courses are offered in each semester. Each offered course is related to faculties, students, curriculums etc. The offered class table might not be that big but if I want to show all the related data to the user, then it becomes a tough job.

Traditional Approach to This Case:

A very naive approach to develop this report would be to use the Include() method to load all the related data.

dbContext.OfferedClass
    .Include(x=>x.Curriculum)
    .Include(x=>x.ClassRegisteredByStudent)
    .Include(x=>x.ClassTakenByFaculty)

Pitfall of This Approach

While using Include()function makes the developer's life easier in the short term, it doesn't take much time to turn into a nightmare!!

Why?

Because Include() returns a Cartesian product (or Cross Join) for the tables. Suppose there are 10 offered class and 10 curriculums. EF doesn't fetch 10+10 rows for OfferedClass and Curriculum. It rather returns 10x10=100 rows. And it gets multiplied the more tables you include!

via GIPHY

What Should We Do?

Simple! We avoid using that method! Now let's discuss about the optimization, shall we?

Optimization 1: Avoid using Include method and use the Contains method instead.

Let me explain. We don't need to be a genius to understand that the number of rows in a child table is almost always bigger than its parent table. That means, in our case the Curriculum table is a parent of the OfferedClass table. So, it's very likely that we are loading the same row from the Curriculum table by using the Include method because there could be many OfferedClass from the same Curriculum. So, what should we do? The idea is to make a list of the unique primary keys of the Curriculum table. Then load the Curriculums where the IDs of each entity belongs to the list.

var curriculumIdList=offeredClassList.Select(x=>x.CurriculumId).Distinct().ToList();
var curriculumList= dbContext.Curriculum.Where(x=>curriculumIdList.Contains(x.Id)).ToList();

Under the hood, EF runs a magic called relationship fix-up. That is, against the same context instance, EF checks each newly-loaded entity's foreign keys against entities already in that identity map. If a OfferedClass.CurriculumId matches a Curriculum.Id that's already tracked, EF wires up both sides: OfferedClass.Curriculum gets set, and Curriculum.OfferedClasses gets that OfferedClass added to it.

⚠️ Warning:

Two things to note here:

1. Both data must be loaded using the same db context.
2. Must turn off Lazy Loading. Otherwise, EF might load redundant data or duplicate data while insertion/update operations. dbContext.Configuration.LazyLoadingEnabled = false;

Optimization 2: Avoid loading data inside any loop.

Let's stick to our previous example. Suppose I also want to show the user how many students have registered to that offered class, how many classes each faculty has taken of each offered class etc.

The Naive Approach (Bad)

In such scenario, it's very much intuitive to iterate over the list of classes and for each class, we load the necessary data.

Why is it bad?

Well, simply put, database lives in storage devices and they are slow. Or at least not as fast as RAM. And each time we query to the database, a connection must be made, the SQL server reads the query, prepares the data, sends it back, entity framework reads and deserializes it. Multiply the time taken in the whole process by 1000 and the impact becomes vivid.

Solution: Load All The Data in A List/Iterable and Then Use it.

Instead of loading related data of each row one by one, we load them all at once.

var classIdList=offeredClassList.Select(x=>x.Id).ToList();
var studentRegistration=dbContext.StudentRegistration
    .Where(x=>classIdList.Contains(x.ClassId))
    .GroupBy(x=>x.ClassId)
    .Select(x=>new{ClassId=x.Key, RegistrationCount=x.Count})
    .ToList();

foreach(var offeredClass in offeredClassList){
    offeredClass.RegistrationCount=studentRegistration.FirstOrDefault(x=>x.ClassId==offeredClass.Id)?.RegistrationCount??0;
}

But wait! We can do better!

Optimization 3: Use Cool Data Structures!

Back in my University days when I was learning competitive programming, I learned about STL in C++ that provided some fancy data structures. Two of them were, Map and Set. In C#, we have Dictionary and HashSet. These bad boys help data lookups lightning fast! You see, looking up on an array/list is expensive. As much as O(N) expensive. So, clearly, looking up on a list inside a loop makes the algorithm O(N²). We can use these data structures to improve it.

Dictionaries instead of FirstOrDefault or Where Method

A Dictionary<TKey, TValue> in .NET is a hash table. On lookup, it computes key.GetHashCode(), uses that to jump straight to the right bucket, then does an equality check within that (usually tiny) bucket. No scanning the whole collection — that's what makes it constant time regardless of how many items are in the dictionary. Dictionary lookup complexity is O(1) on average case. 🤯

Let's Improve Our Last Code Snippet

var classIdList=offeredClassList.Select(x=>x.Id).ToList();
var studentRegistration=dbContext.StudentRegistration
    .Where(x=>classIdList.Contains(x.ClassId))
    .GroupBy(x=>x.ClassId)
    .Select(x=>new{ClassId=x.Key, RegistrationCount=x.Count})
    .ToDictionary(x=>x.ClassId,x=>x.RegistrationCount);

foreach(var offeredClass in offeredClassList){
    offeredClass.RegistrationCount=studentRegistration.TryGetValue(offeredClass.Id, out var count)?count:0;
}

The key idea is to identify the columns we need filter on and create a key by concatenating the columns into a string. And this approach is not only limited to finding one element. You can use this approach to find a list that satisfies multiple criteria. All you have to do is to group the list by the filtering columns and convert it to a dictionary.

HashSet for Checking Whether a Data Exists

Let's head back to our first example about our accountant. Our code has to check for duplicate transactions in both excel and database. Let's consider the database part. A payment is considered duplicate if another payment with the same studentId, Date and Amount Exist. If any row from the excel data is found to be duplicate, we must mark it and highlight it in the UI.

Naive Approach:

var transactionsFromDb= //load transactions from db (only of the students in the excel)
foreach(var item in excelData){
    if(transactionsFromDb.Any(x=>x.StudentId==item.StudentId && x.Date==item.Date && Math.Abs(x.Amount-item.Amount)<0.01)){
        item.IsDuplicate=true;
    }
}

Smarter Approach Using HashSet

The idea is to look for the columns that are to be matched and create a key by concatenating them in a string.

var transactionsFromDb= //load transactions from db (only of the students in the excel)
var transactionSet=transactionsFromDb
    .Select(x=>$"{x.StudentId}_{x.Date}_{x.Amount.ToString("F2")}")
    .ToHashSet();
foreach(var item in excelData){
    var key=$"{item .StudentId}_{item .Date}_{item .Amount.ToString("F2")}";
    if(transactionSet.Contains(key)){
        item.IsDuplicate=true;
    }
}

Optimization 4: Select Only the Necessary Columns.

Suppose the User table has 100 columns. Some columns even have long texts. But on report, we only need to show a user's full name and the username. In that case, we only need to select the two columns from the database and not the whole table.

Why?

Selecting more columns means more RAM consumption, more CPU cycles to process the data. A combination that we certainly want to avoid, right? Of course, this means we need to declare many custom classes for very small use-cases. But when performance is not optional, we must not cut corners in coding. Example:

var offeredClassIdList=offeredClassList.Select(x=>x.Id).ToHashSet();
var facultyList=dbContext.User.Where(x=>offeredClassIdList.Contains(x.Id))
    .Select(x=>new UserShortInfo{
    Id=x.Id,
    UserName=x.UserName,
    FullName=x.FullName
}).ToList();

Now Let's Talk About Bulk Insertion:

Bulk insertion is a feature where lightning-fast speed is expected. And it comes with many caveats. It is very much different from inserting a single row. Few things that slow down the process:

1. Entity validations in the database layer. Such as data types, data consistency etc.
2. Database transaction isolation level.
3. Attempting to insert too much data in a single transaction.
4. Saving each change one by one.

Let's fix them:

Fix-1: Disable Entity Framework's Safety Nets on save.

Whenever you're trying to save a data, Entity framework tries to validate it. Which is of course an expensive operation for bulk insertions. We should create a solid custom validator that validates each property, checks nullability, data size, relationships etc. And we need to turn off the dbcontext's Entity Validator. dbContext.Configuration.ValidateOnSaveEnabled = false; Now, the data validation is completely on your hand. Make no mistakes! We also need to disable Proxy Creation and Lazy loading to prevent loading unnecessary data.

  dbContext.Configuration.LazyLoadingEnabled = false;
  dbContext.Configuration.ProxyCreationEnabled = false;

Fix-2: Choosing the Proper Transaction Isolation Level.

Putting too strict isolation level can make the db operation slow. It may even lock the entire table and put the user in a deadlock situation. Also in most cases, we need dirty-reads to get the ID of the inserted row. I personally prefer the ReadUncommitted isolation level.

Fix-3: Insertion by Chunk Instead of All at Once.

Having too much uncommitted data consumes a lot of memory, rendering the process slow. Therefore, we need to insert data in multiple chunks and commit them.

❌Bad Approach

using(var transaction=dbContext.Database.BeginTransaction(IsolationLevel.ReadUncommitted)){
    foreach(var item in itemList){
        //.... validation and saving logic 
        dbContext.SaveChanges();
    }
    transaction.Commit();
}

✅Better Approach (Slightly)

The idea is to choose the size of a chunk. Calculate the number of chunks. Then select each chunk and insert. For each chunk, we need to re-initialize the dbContext and begin new transactions to free up the memory. The chunk size may depend on the use-case.

int chunkSize=100;
int chunkCount=Math.Ceiling((double)itemList.Count/chunkSize);
for(int index=0;index<chunkCount;index++){
    var chunk=itemList.Skip(index*chunkSize).Take(chunkSize).ToList();
    using(var dbContext=new DbContext())
    {
        dbContext.Configuration.ValidateOnSaveEnabled = false;
        dbContext.Configuration.LazyLoadingEnabled = false;
        dbContext.Configuration.ProxyCreationEnabled = false;
        using(var transaction=dbContext.Database.BeginTransaction(IsolationLevel.ReadUncommitted)){
            foreach(var item in chunk){
                //.... validation and saving logic 
                dbContext.SaveChanges();
            }
            transaction.Commit();
        }
    }
}

Fix-4: Save Many Data at Once Instead of One by One

Saving each data individually becomes really expensive at a large scale because it involves the database service over and over again. We should save all the data at once. We can store the items in a list. Then add the list into the database and save changes. This is much faster. Let's fix our previous code, shall we?

int chunkSize=100;
int chunkCount=Math.Ceiling((double)itemList.Count/chunkSize);
for(int index=0;index<chunkCount;index++){
    var insertedPayments=new List<Payment>();
    var chunk=itemList.Skip(index*chunkSize).Take(chunkSize).ToList();
    using(var dbContext=new DbContext())
    {
        dbContext.Configuration.ValidateOnSaveEnabled = false;
        dbContext.Configuration.LazyLoadingEnabled = false;
        dbContext.Configuration.ProxyCreationEnabled = false;
        using(var transaction=dbContext.Database.BeginTransaction(IsolationLevel.ReadUncommitted)){
            foreach(var item in chunk){
                //.... validation and saving logic 
                insertedPayments.Add(item);
            }
            dbContext.Payments.AddRange(insertedPayments);
            dbContext.SaveChanges();
            transaction.Commit();
        }
    }
}

Conclusion

None of these fixes were exotic. No new library, no infrastructure upgrade, no throwing more hardware at the problem — just paying attention to how EF6 actually talks to the database and removing the patterns that quietly turn a fast query into a slow one at scale.

The results speak for themselves:

• Validation: ~1.7 rows/sec → ~250 rows/sec (~150x)
• Saving: ~0.3 rows/sec → ~62 rows/sec (~190x)

What used to be a 2-minute wait for 200 rows and a white-knuckle 10-minute save is now a validation pass on 5,000 rows in 20 seconds and a save in 1 minute 20 seconds. Accounting officials went from dreading this upload to not thinking about it at all — which, honestly, is the best compliment a backend feature can get.

If you're dealing with something similar, here's the checklist to walk away with:

1. Avoid using Include as much as possible.
2. Preload all the data instead of loading them inside a loop.
3. Use dictionary and hashset to boost data lookups.
4. Select only the necessary data.
5. Turn off EF's safety nets in bulk insertions.
6. Choose the proper isolation level.
7. Don't try to insert too much data at once.
8. Don't try to save each data individually. Rather save them in chunks.

If you've come this far, thank you so much for your time! I'd genuinely love to hear how you've handled EF6 performance at scale — drop a comment if you've got a war story or a technique I didn't cover.

Thank you!