patterncsharpMinor

linq.Any() check on large database

Submitted by: @import:stackexchange-codereview·Mar 10, 2026·

Viewed 0 times

linqanydatabaselargecheck

Problem

Is there a way to speed up the Linq.Any check or perform the check in another way to enhance performance.

I have a mastercodes table that records unique codes for a campaign. The code below will create new codes for a campaign but checks the mastercode table to make sure that it doesn;t already exist. Once the codes have been created and added to the mastercodes table it creates an excel file for download.

The main issue is that a campaign can have 400k codes and we have several campaigns running at once. So the mastercode table can get into the millions of records. When I request 300 new codes and run the below process without the .Any() check it takes 1-2 seconds in production. However when i add the check the process times out with the "no response to user" timeout error.

I'm using the Z.EntityFrameworrk.Extensions and the post to the table happens pretty much instantly. But is there a way to code the perform the mastercode check that doesn't have a huge performance issue.

The Model

public class MasterCode
{
    public int MasterCodeId { get; set; }
    public int CampaignId { get; set; }
    public string CampaignCode { get; set; }
    public string Code { get; set; }
    public bool Used { get; set; }
    public DateTime SubmittedOn { get; set; }
}

The code

```
public static void GenerateMasterCodes(GenerateMasterCode model)
{
using (ApplicationDbContext _context = new ApplicationDbContext())
{
Campaign campaign = _context.Campaigns.Find(model.CampaignId);

List masterCodes = new List();
for (var i = 1; i m.Code == code && m.CampaignCode == campaign.CampaignCode))
{
code = GenerateMasterCode(model.StartString, model.CodeLength);
}
masterCode.Code = code;

masterCodes.Add(masterCode);
}

try
{
_context.MasterCodes.AddRange(masterCodes);
_context.BulkSaveChanges();
}
catch (Exceptio

Solution

GenerateMasterCode

I would break the GenerateMasterCode() method into two methods and would declare the Random as a static field tof the class.

From the Random documentation:

The default seed value is derived from the system clock and has finite resolution. As a result, different Random objects that are created in close succession by a call to the default constructor will have identical default seed values and, therefore, will produce identical sets of random numbers. This problem can be avoided by using a single Random object to generate all random numbers.

Instead of using a char[] to generate the random characters I would use a StringBuilder.

public static string GenerateMasterCode(string startString, int mastercodeLength)
{

    if (startString.Length == masterCodeLength) { return startString; }
    if (startString.Length > masterCodeLength) { return startString.Substring(0, masterCodeLength); }

    return startString + GenerateRandomCharacters(masterCodeLength - startString.Length);
}

private static Random rd = new Random();
private static string GenerateRandomCharacters(int length)
{
    string numericChars = "X123456789";
    int length = numericChars.Length;
    StringBuilder builder = new StringBuilder(length);
    for (int i=0; i < length; i++)
    {
        builder.Append(numericChars[rd.Next(length)]);
    }
    return builder.ToString();
}

GenerateMasterCodes

Right now you query in the while loop all of the MasterCodes although you are only interested in the Campain related MasterCodes. So you should query the MasterCodes which belongs to the Campain before you start with the for loop.

Assigning false to Used can be omitted, because that is the default value of bool.

You can use the var type for the List as well.

If you don't use that method outside of the class you should consider to make it private.

public static void GenerateMasterCodes(GenerateMasterCode model)
{
    using (ApplicationDbContext _context = new ApplicationDbContext())
    {
        Campaign campaign = _context.Campaigns.Find(model.CampaignId);
        var campainRelatedMasterCodes = _context.MasterCodes.Where(m.CampaignCode == campaign.CampaignCode);
        var masterCodes = new List();
        for (var i = 1; i  m.Code == code))
            {
                code = GenerateMasterCode(model.StartString, model.CodeLength);
            }
            masterCode.Code = code;

            masterCodes.Add(masterCode);
        }

        try
        {
            _context.MasterCodes.AddRange(masterCodes);
            _context.BulkSaveChanges();
        }
        catch (Exception ex)
        {
            ...
        }
    }
}

Code Snippets

public static string GenerateMasterCode(string startString, int mastercodeLength)
{

    if (startString.Length == masterCodeLength) { return startString; }
    if (startString.Length > masterCodeLength) { return startString.Substring(0, masterCodeLength); }

    return startString + GenerateRandomCharacters(masterCodeLength - startString.Length);
}

private static Random rd = new Random();
private static string GenerateRandomCharacters(int length)
{
    string numericChars = "X123456789";
    int length = numericChars.Length;
    StringBuilder builder = new StringBuilder(length);
    for (int i=0; i < length; i++)
    {
        builder.Append(numericChars[rd.Next(length)]);
    }
    return builder.ToString();
}

public static void GenerateMasterCodes(GenerateMasterCode model)
{
    using (ApplicationDbContext _context = new ApplicationDbContext())
    {
        Campaign campaign = _context.Campaigns.Find(model.CampaignId);
        var campainRelatedMasterCodes = _context.MasterCodes.Where(m.CampaignCode == campaign.CampaignCode);
        var masterCodes = new List<MasterCode>();
        for (var i = 1; i <= model.Count; i++)
        {
            var masterCode = new MasterCode
            {
                CampaignId = campaign.CampaignId,
                CampaignCode = campaign.CampaignCode,
                SubmittedOn = DateTime.Now
            };

            // Ensure that no code already exists for that campaign.
            var code = GenerateMasterCode(model.StartString, model.CodeLength);

            while (campainRelatedMasterCodes.Any(m => m.Code == code))
            {
                code = GenerateMasterCode(model.StartString, model.CodeLength);
            }
            masterCode.Code = code;

            masterCodes.Add(masterCode);
        }

        try
        {
            _context.MasterCodes.AddRange(masterCodes);
            _context.BulkSaveChanges();
        }
        catch (Exception ex)
        {
            ...
        }
    }
}

Context

StackExchange Code Review Q#160500, answer score: 2

Revisions (0)

No revisions yet.