使用 Linq to Sql 按顺序查找漏洞

发布于 2024-12-09 13:22:20 字数 349 浏览 1 评论 0原文

我正在针对 SQL Server Compact 数据库使用 Linq to Sql。我需要一种快速的方法来找到基于整数的列中的第一个孔，或者如果不存在最大数字+ 1。

如果我使用 SQL 执行此操作，我会执行如下操作：

SELECT IdLegacy+1 FROM FLUID AS t1
LEFT JOIN FLUID as t2
ON t1.IdLegacy = t2.IdLegacy+1
WHERE t2.IdLegacy IS NULL

基本上我需要在 Linq to Sql 中类似的东西来实现同样的事情。因为每次插入都会调用它，所以我需要它快速且更优雅:-D。

谢谢

原文

I am using Linq to Sql against a SQL Server Compact database.
I need a fast way to find the first hole in an integer based column or if none exist the highest number + 1.

If I was doing it using SQL I would do something like this:

SELECT IdLegacy+1 FROM FLUID AS t1
LEFT JOIN FLUID as t2
ON t1.IdLegacy = t2.IdLegacy+1
WHERE t2.IdLegacy IS NULL

Basically I need something similar in Linq to Sql to achieve the same thing. As it will be called on every insert, I need it to be fast and preferable elegant :-D.

Thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

摘星┃星的人 2024-12-16 13:22:20

左外连接在 LINQ to SQL 中看起来像这样

from t1 in fluid
join t2 in fluid on t1.LegacyId + 1 equals t2.LegacyId into t3
from maybeGap in t3.DefaultIfEmpty()
where maybeGap == null
select new { t1 = t1 }

maybeGap 现在反映了来自 Fluid 的左外连接 记录。 SQL Compact 的 LINQ 提供程序可能受到限制，因为 SQL Compact 非常有限，但这就是它的具体细节。

您可以使用这个小测试用例来测试它：

var list = new List<int> { 1, 2, 3, 5 };

var q =
    from x in list
    join y in list on x + 1 equals y into y
    from z in y.DefaultIfEmpty()
    where z == 0
    select x + 1
    ;

foreach (var item in q)
    Console.WriteLine(item);

打印 4 和 6，只需忽略最后一个，因为它始终存在，并且没有简单的方法可以防止这种情况发生使用 SQL Compact 不支持的窗口函数。

A left outer join looks like this in LINQ to SQL

from t1 in fluid
join t2 in fluid on t1.LegacyId + 1 equals t2.LegacyId into t3
from maybeGap in t3.DefaultIfEmpty()
where maybeGap == null
select new { t1 = t1 }

maybeGap now reflects a record that's a left outer join from fluid. It might be that the LINQ provider for SQL Compact is limited as SQL Compact is very limited but this is the nuts and bolt of it.

You can test it using this little test case:

var list = new List<int> { 1, 2, 3, 5 };

var q =
    from x in list
    join y in list on x + 1 equals y into y
    from z in y.DefaultIfEmpty()
    where z == 0
    select x + 1
    ;

foreach (var item in q)
    Console.WriteLine(item);

Prints 4 and 6, just ignore the last as it will always be there and there's no easy way to prevent that from occurring without using window functions which aren't supported by SQL Compact.

回复收藏 0 原文