How to generate sequential string names like "account1", "account2" if that particular account name already exists

Question

I have a requirement that states the following:

"The system shall verify if the username from the imported file is already in use, and shall generate the username according to the duplicate format
 Example: If username suesmith is in use, the system shall use suesmith1 as the username
. If suesmith1 is in use, the system shall utilize suesmith2 as the username"

Basically let's say that in my Users table I already have user suesmith and I have a function to import new users from a csv file. If in that file suesmith it's present, it should behave like in that requirement.

I've started with the following:

var usernameFromFile = userInfoFromFile.Username;
var existingUsernames = this.accountCreationService.GetSimilarUsernamesBasedOnProvidedUsernameFromGlobal(userInfoFromFile.Username, serverInfoForGlobalDatabase, globalDatabaseName).Where(x => x.StartsWith(usernameFromFile)).ToArray(); 

// userInfoFromFile is an object that gets populated with data from file and this object is used to create a new user account, so in this object I'll need to save the username

// The function GetSimilarUsernamesBasedOnProvidedUsernameFromGlobal runs a sql query : SELECT us.Username FROM Users us where us.Username LIKE '%usernameFromFile%' and after the query that returns a string[], I've placed a Where condition to retrieve only those usernames that start with the username from file (so excluding the ones like 12suesmith or abcsuesmith)

And I continued like this:

                    var usernamesInCriteria = new Dictionary<string, int?>();
                    ///var usernamesInCriteria = new List<string>();

                    for (int i = 0; i < existingUsernames.Length; i++)
                    {
                        var stringAfterUsername = existingUsernames[i].Substring(usernameFromFile.Length);
                       
                        int numberInUsername;
                        var isNumber = int.TryParse(stringAfterUsername, out numberInUsername);

                        if (stringAfterUsername.IsNullOrEmpty() || isNumber)
                        {
                            usernamesInCriteria.Add(existingUsernames[i], numberInUsername);
                            ///usernamesInCriteria.Add(existingUsernames[i]); //in this list the same username (if exists) and usernames followed by numbers only will be stored     
                        }                                
                    }

                    foreach (var pair in usernamesInCriteria)
                    {
                        if (pair.Key == userInfoFromFile.Username)
                        {

                        }
                    }

Basically for every username from that existingUsernames array, I've checked to see if there is a number substring after the usernameFromFile, and if so, added those existing usernames to a dictionary (and by stringAfterUsername.IsNullOrEmpty() included also the existing username like suesmith)

But further I'm not sure exactly what to do, now that I've achieved all the similar usernames that end with a number. So I don't know exactly how to verify that suesmith username already exists, then if it exists, append 1 to it, then check if suesmith1 exists, append 2 and so on... If you could please give me an idea how to start!

Answer 1

How about:

if (existingUsernames.Contains(username))
{
    username = Enumerable.Range(1, Int32.MaxValue)
        .Select(i => $"{username}{i}")
        .Where(s => !existingUsernames.Contains(s))
        .First();
}

In your existing username query, you should drop the first % from LIKE '%usernameFromFile%'.