how to initialize count the line of the table?

Question

Hi,

I have the table with key IdProjet and I added the column to count each line in controller;

my problem is that when the table is empty the ProjectCount is not filled because it is null. If I manually fill the 1st of my table it works fine.

If I initialise ProjectCount=0, it will always be taken into account;

Do you have any suggestions for fixing my problem?

Thanks in advance

   [HttpPost]
        public async Task<ActionResult<ProjetModel>> PostAsync([FromBody] ProjetModel projet)
        {            
            
			ProjetCount projetcount = await IncrementIdAsync();
            projet.ProjetCount = projetcount.ProjCount;

            _context.Add(projet);
            await _context.SaveChangesAsync();
            return CreatedAtAction(nameof(Get), new { id = projet.IdProjet }, projet);
        }

        //Take the number 
        private async Task<ProjetCount> IncrementIdAsync()
        {
            int max = await _context.ProjetModels.MaxAsync(d => d.ProjetCount) + 1;
            return new ProjetCount() { ProjCount = max };
        }

 public class ProjetModel
    {
        [Key]
        public int IdProjet { get; set; }        
        public int ProjetCount { get; set; }
        public string? ProjetCountId {
            get
            {
                return $"{ProjetCount.ToString().PadLeft(3, '0')}";
            }
            set { }
        }
    }

 public class ProjetCount 
	{ 
		 public int ProjCount { get; set; } 
	 }

Answer 1

It seems like you want to initialize the ProjetCount property with a value of 0 if the table is empty. To achieve this, you can modify your IncrementIdAsync method to handle the case when the table is empty. You can check if there are any records in the table and conditionally set the value of ProjetCount. Here's how you can modify your code:

// Take the number 
private async Task<ProjetCount> IncrementIdAsync()
{
    int max = 0;

    if (await _context.ProjetModels.AnyAsync())
    {
        max = await _context.ProjetModels.MaxAsync(d => d.ProjetCount) + 1;
    }

    return new ProjetCount() { ProjCount = max };
}

In this modified code:

We check if there are any records in the ProjetModels table using _context.ProjetModels.AnyAsync(). If there are no records, max will remain 0, which means the table is empty.

If there are records in the table, we calculate the maximum value for ProjetCount and increment it by 1.

Finally, we return a ProjetCount instance with the calculated value of max, which will be 0 if the table is empty or the next value if there are existing records.

With this change, ProjetCount will be initialized to 0 when the table is empty, and it will be incremented properly for new records.

Answer 2

just use nullability:

int max = (await _context.ProjetModels.MaxAsync(d => (int?) d.ProjetCount) ) ?? 0 + 1;

but this is a poor design unless this is a single user system, concurrent requests may get the same value.

Answer 3

@AgaveJoe have you any suggestions about the remarks from @Bruce (SqlWork.com) ?

Thanks in advance