How can (DbContext) SaveChanges() be used for large number of records? - the solution for the best performance is to be sought and found

Question

It can be running SaveChanges() for large number of records, but the performance is not acceptible.

Here is an example Two tables:

[contact] (id, name, zip, city, status)

[zip_city] (id, zip, city)

Needs to update the status in table contact for all column zip, city being availabel in table zip_city

It works and the performance is acceptable when the number of records is less than 10,000.

But for +10,000 records, Visual Studio (Debugger) complains that it takes too long: The CLR was unable to transition from COM context 0xb67898 to COM context 0xb67728 for 60 seconds.... at building the query "qry"

var ctx = new DbContext();

var qry = ctx.contact.Join(ctx.zip_city, c => new { c.zip, c.city }, z => new { z.zip, z.city }, (c, z) => new { c, z })
           .Select(x => new dtoContact { id = x.c.id }).ToList();

foreach (var con in ctx.contact)
{
    if (qry.Any(x => x.Id == con.id))
    {
        con.status = "P/O";
    }
    else
    {
        con.status = "???";
    }
}

ctx.SaveChanges();

With following code it raises same message by running ctx.SaveChanges();

var ctx = new DbContext();

var zc = ctx.zip_city.ToList();

foreach (var con in ctx.contact)
{
    if (zc.Any(x => x.zip == con.zip && x.city == con.city))
    {
        con.status = "P/O";
    }
    else
    {
        con.status = "???";
    }
}

ctx.SaveChanges();

Reference: Data Transfer Object

public class dtoContact 
{
    public int id { get; set; }
    public string name { get; set; }
    public string zip { get; set; }
    public string city { get; set; }
    public string status { get; set; }
}

Info: Visual Studio (Debugger) message! google translation from VS15 German version

The Managed Debugging Assistant "" ContextSwitchDeadlock "" encountered a problem in "C: \ Projects \ Sweepstakes_EF6 \ TrafficSightAct \ bin \ Debug \ Sweepstakes.exe". Additional Information: The CLR was unable to transition from COM context 0xb67898 to COM context 0xb67728 for 60 seconds. The thread that owns the target context / apartment either waits without moving messages or processes a very long-lasting operation without moving Windows messages. Such a situation typically slows performance and may even cause the application to stop responding or to increase memory usage.

Answer 1

In this case, I would like to recommend using SqlCommand.

for example:

var ctx = new DbContext();

string sql = "update contact c inner join zip_city z " +
             "on c.plz = z.plz and c.ort = z.ort set c.status = 'P/O'; ";
sql = sql  + "update contact set status = '???' where status <> 'P/O'; ";

ctx.Database.ExecuteSqlCommand(sql);

That could take just few seconds for your 27,000 records, I think.

Please try.