1

One of my small database management projects (written in delphi) used sql queries to find the first free id of mysql table.

Example: I have to find the first free id (hole) in a table like this:

| id   | Col1 |
|------|------|
| 5101 | ABC  |
| 5102 | BCD  |
| 5103 | CDE  |
| 5105 | EFG  |  first missing id
| 5106 | GHI  |
| 5108 | ILM  |

The code should find the first free id 5104

Here's how I'd do it in SQL (in old project): 

SELECT
  MIN((doc.id + 1)) AS nextID
FROM (doc
  LEFT JOIN doc doc1
    ON (((doc.id + 1) = doc1.id)))
WHERE (ISNULL(doc1.id) AND (doc.id > 5000))

Now, which I am rewriting in c # language, I need to convert sql statements into a LINQ query (which uses Devart dotConnect for mysql Entity Framework). Starting from here:

DC db = new DC();
var nums = db.Documentos.OrderBy(x => x.Id);
Domenico Formoso
  • 137
  • 7
  • But you need it to add a row with such value? or for other purpose? – Tiago Silva Feb 23 '19 at 14:53
  • I have to fill in the empty rows – Domenico Formoso Feb 23 '19 at 14:56
  • If you are using DB generated Ids simply add results till the desired ID – Tiago Silva Feb 23 '19 at 14:59
  • This smells like an X/Y question. Normally database IDs are not reused. It will be allmost impossible to make this concurrency-safe. Is your Db single use only? – H H Feb 23 '19 at 17:30
  • the database is multi-user, but I use it only for extraordinary activity – Domenico Formoso Feb 23 '19 at 17:35
  • 2
    Which type of LINQ are you using? As you see in the answers, people easily assume you can do this all in memory but I'm pretty sure you want the database to do the heavy lifting as it did with the SQL statement. – Gert Arnold Feb 24 '19 at 15:56
  • @Gert Arnold must do the datatbase work, using System.Linq – Domenico Formoso Feb 24 '19 at 18:17
  • OK, but as said above, it's unusual, and not recommended, to reuse database IDs. Why do holes in the ID range matter? – Gert Arnold Feb 24 '19 at 18:42
  • @Gert Arnold a maintenance application must find the missing ids (deleted ?) – Domenico Formoso Feb 25 '19 at 08:05
  • Well, still doesn't explain why. Anyway, I would create a scalar-valued database function that returns the available ID. Faster than LINQ and therefore less sensitive to concurrency. But never forget that database IDs (surrogate keys) with meaning to the business domain is a major architectural flaw. – Gert Arnold Feb 25 '19 at 09:53

3 Answers3

0

From Can LINQ be used to find gaps in a sorted list?: 

var strings = new string[] { "7", "13", "8", "12", "10", "11", "14" };
var list = strings.OrderBy(s => int.Parse(s));
var result = Enumerable.Range(list.Min(), list.Count).Except(list).First(); // 9

Basically, order the list. Then create an array of sequential numbers (1,2,3...) from the minimum all the way to the max. Check for missing values in the list, and grab the first one. That's the first missing number.

KyleMit
  • 30,350
  • 66
  • 462
  • 664
0

This can give you all gaps within your table

var nums= (new List<int> (){1,2,3,25,4,5,6,7,8, 12, 15,21,22,23}).AsQueryable();

nums
  .OrderBy(x => x)
  .GroupJoin(nums, n=> n + 1, ni => ni, (o,i)=> new {o, i})
  .Where(t=> !(t.i is IGrouping<int, int>))
  .Dump();

.Net Fiddle

Derviş Kayımbaşıoğlu
  • 28,492
  • 4
  • 50
  • 72
0

Another method (similar to what you're using now).
Assume you have an array of integers (or another type of collection) like this: 

var myIDs = new int[] { 5101, 5113, 5102, 5103, 5110, 5104, 5105, 5116, 5106, 5107, 5108, 5112, 5114, 5115 };

If it's not already ordered, the OrderBy() it:

myIDs = myIDs.OrderBy(n => n).ToArray();

Extract the first number that is less than (next number) + 1: 

int result = myIDs.Where((n, i) => (i < myIDs.Length - 1) && (n + 1 < myIDs[i + 1])).FirstOrDefault();

If none of the members of this collection satisfy the condition, take the last one and add 1: 

result = result == default ? myIDs.Last() + 1 : result;
Jimi
  • 29,621
  • 8
  • 43
  • 61