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!

This is probably something best done in the database, it's going to be laborously slow running queries RBAR for duplicate names. Load the data into a staging table and use set-based queries to identify duplicate names and some additional logic to count number of existing matching names and append new count to each duplicate before then inserting new rows. — Stu, Feb 05 '22 at 12:03
This would then also allow to solve the concurency problem, that two clients may in parallel determine "account9" to be the next free name and both try to add it in the next step. — Klaus Gütter, Feb 05 '22 at 12:08
You will struggle to implement that logic so I suggest you consider escalating this "requirement". Trying to find duplicates for "suesmith" means you must ignore values like "suesmithsonian" while considering "suesmith2" and "suesmith11". Can username contain numeric characters when read from the file? Dynamically altering values during import can be a risky thing to do - you will need good use cases to understand and test your process. — SMor, Feb 05 '22 at 14:02
@SMor yep unfortunately the username from file can also contain numeric characters. I'll take it into account. Thanks! — Alex, Feb 05 '22 at 14:59

score 1 · Accepted Answer · answered Feb 05 '22 at 18:46

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%'.

answered Feb 05 '22 at 18:46

T N

4,322
1
5
18

That solves it! Thank you so so much for this! Didn't have this on my mind at all. Appreciate it and wish you the best!! – Alex Feb 06 '22 at 09:16
1

@Alex - One follow-up note: As is typical for many systems, if your usernames are case-insensitive (suesmith and SueSmith are considered equivalent, your logic will need to handle this. One approach is to pass in an appropriate StringComparer to the Contains() functions. Another is to convert username and existingUsernames to lowercase prior to the check. Your GetSimilarUsernames...() function would also need to be case insensitive. – T N Feb 06 '22 at 17:24
Thank you again for the advices! Helped a lot! – Alex Feb 07 '22 at 15:15

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

1 Answers1