Display result from the same table which has missing records.

;WITH CTE AS
(
  SELECT 1 AS FirstNo, MAX(MyNumber) AS LastNo
  FROM A
  UNION ALL
  SELECT FirstNo +1 As FirstNo, LastNo
  FROM CTE 
  WHERE FirstNo<LastNo
)
  SELECT FirstNo AS MyNumber
  FROM CTE
  EXCEPT 
  SELECT MyNumber
  FROM A
  OPTION (MAXRECURSION 0)

DataTable dt = new DataTable();
DataColumn dc = new DataColumn("ID", Type.GetType("System.Int32"));
dt.Columns.Add(dc);
dt.Rows.Add(1);
dt.Rows.Add(2);
dt.Rows.Add(3);
dt.Rows.Add(5);
dt.Rows.Add(6);
dt.Rows.Add(8);
dt.Rows.Add(10);

int maxid = dt.AsEnumerable().Max(x=>x.Field<int>("ID"));

var qry = Enumerable.Range(1, maxid).Except(dt.AsEnumerable().Select(x=>x.Field<int>("ID")));
foreach(var n in qry)
{
    Console.WriteLine("{0}", n);
}