Entity Framework - is AnyAsync() the most efficient approach? Or is Contains() better?

Question

Hi all;

My situation is I have 4 different model objects that each have a property:

public string UniqueId { get; private set; }

This UniqueId must be unique across all 4 tables (sort of a super unique key).

First, I do set it to be unique in each model (which avoids the issue of my having to handle yes there's one record with it on an update):

builder.HasIndex(c => c.UniqueId).IsUnique();

In my DbContext.SaveChanges() I am doing the following:

List<string> campaignUniqueIds = new();
List<string> cantonUniqueIds = new();
List<string> stateUniqueIds = new();
List<string> countryUniqueIds = new();

var upsertedCampaignUniqueIds = ChangeTracker.Entries<Campaign>()
    .Where(c => c.State == EntityState.Added || c.State == EntityState.Modified)
    .Select(c => c.Entity.UniqueId).ToList();
countryUniqueIds.AddRange(upsertedCampaignUniqueIds);
stateUniqueIds.AddRange(upsertedCampaignUniqueIds);
cantonUniqueIds.AddRange(upsertedCampaignUniqueIds);

// 3 more calls to get from the other 3 models.

And then I validate it with:

if (await Campaigns.AnyAsync(campaign => campaignUniqueIds.Contains(campaign.UniqueId)))
    throw new InvalidOperationException($"One or more of the uniqueId(s) '{string.Join(", ", campaignUniqueIds)}' is already in use by a campaign.");

// 3 more checks for the other 3 models

Is this the most efficient approach? I'm pretty sure building up the lists to check is good. And the fundamental approach of build 4 lists, verify each list against the appropriate table is good.

But is the call AnyAsync() going to give me efficient SQL? Or should I instead be crafting a call using Contains()?

thanks - dave

Answer 1

This code does not answer your question but shows a somewhat basic example of an interceptor.

In regards to interceptors, here is one working off Microsoft NorthWind database where in Customers Model there is a Region property. On SaveChanges if the Region equals KP ignore, do not save but you could handle it differently too. I scrapped out some SeriLog from the code too if wondering about the name.

using System.Collections.Generic;
using System.Diagnostics;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using Microsoft.EntityFrameworkCore.Diagnostics;

namespace EntityHelpers.Classes
{
    /// <summary>
    /// SaveChangesInterceptor interceptor which in it's current state
    /// has commented out code which works, provides alternate possibilities
    ///
    /// Objective here is to assert for Region property equal to specific value
    /// and if so reject the save.
    /// </summary>
    public class LoggingSavingChangesInterceptor : SaveChangesInterceptor
    {
        public override ValueTask<InterceptionResult<int>> SavingChangesAsync(DbContextEventData eventData, InterceptionResult<int> result, CancellationToken cancellationToken = new())
        {

            if (InspectCustomerRegion(eventData.Context.ChangeTracker.Entries()))
            {
                result = InterceptionResult<int>.SuppressWithResult(0);
            }

            return new ValueTask<InterceptionResult<int>>(result);
        }

        private static bool InspectCustomerRegion(IEnumerable<EntityEntry> entries)
        {
            foreach (EntityEntry entry in entries)
            {
                if (entry.State != EntityState.Modified) continue;
                if (entry.Entity is not Customer customer) continue;

                /*
                 * Here we are asserting for a single condition
                 * What makes more sense is to use data annotations and
                 * not perform assertion in an override of SaveChanges using
                 * this interceptor
                 */
                if (customer.Region == "KP")
                {
                    return true;
                }

            }

            return false;
        }
    }
}

Then

builder.Services.AddDbContextPool<Context>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection"))
        .AddInterceptors(new LoggingSavingChangesInterceptor())
        .EnableSensitiveDataLogging());

Answer 2

@David Thielen, Welcome to Microsoft Q&A, If you want to check the UniqueId if it is unique across all 4 tables, I recommend that you could combine all the result string then do the checking.

Here is a code example you could refer to.

          MyContext context = new MyContext();
          Campaign campaign1=new Campaign() { UniqueId="1001" };
          Campaign campaign2 = new Campaign() { UniqueId = "1002" };
          Country country1 = new Country() { UniqueId = "1001" };
          Country country2 = new Country() { UniqueId = "101" };
            State state1 = new State() { UniqueId = "101" };
            State state2 = new State() { UniqueId = "102" };
            context.Add(campaign1);
            context.Add(campaign2);
            context.Add(country1);
            context.Add(country2);
            context.Add(state1);
            context.Add(state2);

            //DisplayStates(context.ChangeTracker.Entries());
            var upsertedCampaignUniqueIds = context.ChangeTracker.Entries<Campaign>()
    .Where(c => c.State == EntityState.Added || c.State == EntityState.Modified)
    .Select(c => c.Entity.UniqueId).ToList();
            var upsertedstateUniqueIds = context.ChangeTracker.Entries<Country>()
    .Where(c => c.State == EntityState.Added || c.State == EntityState.Modified)
    .Select(c => c.Entity.UniqueId).ToList();
            var upsertedcountryUniqueIds = context.ChangeTracker.Entries<State>()
    .Where(c => c.State == EntityState.Added || c.State == EntityState.Modified)
    .Select(c => c.Entity.UniqueId).ToList();
            List<string> result = upsertedCampaignUniqueIds.Concat(upsertedstateUniqueIds).Concat(upsertedcountryUniqueIds).ToList();
            var duplicateStrings = result.GroupBy(s => s)
                                 .Where(g => g.Count() > 1)
                                 .Select(g => g.Key)
                                 .ToList();
            if (duplicateStrings.Count > 0)
            {
                Console.WriteLine("the following  uniqueId already exists：");
                foreach (string str in duplicateStrings)
                {
                    Console.WriteLine(str);
                    
                }
            }
            else
            {
                Console.WriteLine("there is no same UniqueId in the three tables");
            }

Based on the testing, we could find that some uniqueid already exists.

Best Regards,

Jack

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.