Search for missing values in SQL Server table in C#-CodePudding

Please tell me how to solve this problem.

Here is an example table:

Filled with data like this:

I have a method to automatically assign a number to the CerNambe column:

public string CerNamber()
{
    var cer = DBContext.Certificate
                       .Where(p => p.CerNambe != null)
                       .OrderByDescending(p => p.Id)
                       .FirstOrDefault();

    string _cer = cer.CerNambe;

    int outCer = Convert.ToInt32(_cer.Substring(0, _cer.IndexOf('-')));
    string newCer = Convert.ToString(outCer   1   "-P/"   DateTime.Now.Year);

    return newCer;
}

But I ran into a problem. If the number is assigned by the user erroneously, it is deleted, and the numbering is violated.

Here is the question: how to find this violation and assign the missing number to the next record. But in this case, automatic numbering will not work? Since the method is looking for the last record!

CodePudding user response：

How to find this violation and assign the missing number to the next record ?

If I were you, I would modify the database schema so that I don't have to do this. But if you have to, the easiest yet ugliest way is to use RegEx to filter and find special patterns.

The solution: If your underlying database provider is SQL Server, you could use SqlMethods.Like to filter the database result set down to a manageable subset of data that can then analyze locally with RegEx.

Linq to Entities doesn't support regex because it can't convert it to SQL. You could load all of the records back into memory and then use Linq to Objects on them (perform a. ToList() to a variable, then a second LINQ query for this regex), but this meant you'll get to load every DB record into memory to run this query.

The same advice applies: modify the database schema so that you don't have to do this.

For understanding RegEx visit : System.Text.RegularExpressions.Regex Class