I have a column in the database table i.e. transaction Id. Its data type is var char(50). Now I want to save a row by combining the current date with a sequential number generated.
In SQL Server, it can be as simple as:
transactionId = CONCAT(DATEPART(MONTH, GETDATE()), DATEPART(DAY, GETDATE()), DATEPART(YEAR, GETDATE()), (SELECT COUNT(1) FROM tableName WHERE CONVERT(DATE, dateColumn) = CONVERT(DATE, getdate())) + 1)
Get the Number of transactions for that date, and add one to it. Concat that with a formatted string of Today's Date.
If you already have 3 records for today's date, the result would be:
Edit: EF suggested snippet:
string dateStr = DateTime.Today.ToString("Mdyyyy"); DateTime todayMidnight = DateTime.Today; DateTime tomorrowMidnight = DateTime.Today.AddDays(1); obj.transactionId = dateStr + context.tableName.Count(x=> x.dateColumn >= todayMidnight && x.dateColumn < tomorrowMidnight) + 1;