Optimizing database access using LINQ I have the following method that I am trying to optimize. Currently, the method works great, however, it takes a little over 3 hours to complete. I feel like, using some nifty LINQ joins, it should be able to run in minutes. Here is the method:
    public async Task UpdateEmailTable(IEnumerable&lt;StudentEmail&gt; students)
    {
        Console.WriteLine(&quot;Updating Email table&quot;);
        var studentEmails = students.ToList();
        int totalStudents = studentEmails.Count;
        int currentCount = 0;
        float lastPercentage = -1;

        foreach (var student in studentEmails)
        {
            var isIn = await _context.BarcDemoGraphicEmail.AnyAsync(z =&gt; z.Sridentifier == student.StudentId);

            if (!isIn)
            {
                var ar = await _context.Barcaccount.FirstOrDefaultAsync(z =&gt; z.SrIdentifier == student.StudentId);

                await _context.BarcDemoGraphicEmail.AddAsync(new BarcDemoGraphicEmail
                {
                    Sridentifier = student.StudentId,
                    Email = student.EmailAddress,
                    Updatedate = DateTime.Now,
                    Aridentifier = ar == null ? &quot;&quot; : ar.ArIdentifier
                });
            }
            else
            {
                var rec = await _context.BarcDemoGraphicEmail.Where(z =&gt; z.Sridentifier == student.StudentId)
                    .FirstAsync();
                rec.Email = student.EmailAddress;
                rec.Updatedate = DateTime.Now;
            }

            await _context.SaveChangesAsync();

            int percentage = (int)Math.Round((float)currentCount / totalStudents * 100);
            if (lastPercentage != percentage) Console.Write(&quot;\r&quot; + percentage + &quot;%&quot;);
            lastPercentage = percentage;
            currentCount++;
        }
    }

So this method is called with a list of student ids and emails. This list does not come from the database. This list comes from a web call and must be passed into the method. It then iterates through the list, one at a time. It takes the student's id and checks to see if it exists in the BarcDemoGraphicEmail table, there by setting the isIn flag.
If the student ID is NOT in the BarcDemoGraphicEmail table, it pulls the account information from another table called BarcAccount. It then inserts this student's email into the BarcDemoGraphicEmail using the information from the BarcAccount table and the email from the students current iteration.
If the student ID IS in the BarcDemoGraphicEmail table, it simply pulls that record and updates the UpdateDate field and the Email field from the current iteration.
As I said earlier, this method currently works fine, however it takes way too long. How can I optimize my code (or possibly optimize the database) so this executes faster?
This is a console .NET Core 3.1 C# application using Entity Framework Core 3.1. The database is a SQL Server database.
Some quick remarks:

DemoGraphic isn't a compound word so there shouldn't be a capital letter in the middle. However, Barcaccount is so it should be called BarcAccount. Same for Sridentifier, Updatedate, Aridentifier.

Use meaningful variable names. I have no idea what ar is, or sr.

UpdateEmailTable(IEnumerable&lt;StudentEmail&gt; students) and then var studentEmails = students.ToList(); Use properly named and typed parameters. Your method requires a List&lt;StudentEmail&gt; studentEmails (or perhaps ICollection&lt;StudentEmail&gt; studentEmails), write it like that and make the calling code to pass the correct argument.

This method is peppered with Console.WriteLine. This means it mixes business logic and UI. Bad idea. At least change this to use a logging system, and then configure that so it outputs to the command line.



You say this takes three hours, which suggests either a massive volume of data or particularly slow code (or perhaps your tables are not correctly indexed?). Perhaps implementing logic that uses SqlBulkInsert could solve the performance issue. Perhaps take the incoming data and store it in a table, and then apply one or two SQL queries to update the data in BarcDemoGraphicEmail.