将 SQL 转换为 Linq - 如何？
是否可以将此 SQL 转换为 LINQ？
SqlCommand cmd = new SqlCommand(@"
WITH new AS (
SELECT [UserSessionSequenceID],
[SessionGuid],
SiteID,
IP,
UrlTitle,
Url,
Referer,
BrowserWidth,
BrowserHeight,
[Timestamp],
ROW_NUMBER() OVER (PARTITION BY [SessionGuid]
ORDER BY UserSessionSequenceID DESC) AS sort
FROM [tblSequence]
WHERE SiteID = @siteID
AND [Timestamp] > DATEADD(mi, -@minutes, GETDATE()))
SELECT TOP(@resultCount)
n.*
FROM new n
WHERE n.sort = 1
AND NOT EXISTS (SELECT NULL
FROM tblSequence s
WHERE s.siteid = n.siteid
AND s.sessionguid = n.sessionguid
AND [TimeStamp] <= DATEADD(mi, -@minutes, GETDATE()))
ORDER BY n.usersessionsequenceid DESC
");
cmd.Parameters.Add("@resultCount", SqlDbType.Int).Value = resultCount;
cmd.Parameters.Add("@minutes", SqlDbType.Int).Value = minutes;
cmd.Parameters.Add("@siteID", SqlDbType.Int).Value = siteID;
我有一个包含从 SQL 中选择的所有字段的类，名为“SimpleSession”。预先感谢您的任何帮助。
Is it possible to convert this SQL to LINQ?
SqlCommand cmd = new SqlCommand(@"
WITH new AS (
SELECT [UserSessionSequenceID],
[SessionGuid],
SiteID,
IP,
UrlTitle,
Url,
Referer,
BrowserWidth,
BrowserHeight,
[Timestamp],
ROW_NUMBER() OVER (PARTITION BY [SessionGuid]
ORDER BY UserSessionSequenceID DESC) AS sort
FROM [tblSequence]
WHERE SiteID = @siteID
AND [Timestamp] > DATEADD(mi, -@minutes, GETDATE()))
SELECT TOP(@resultCount)
n.*
FROM new n
WHERE n.sort = 1
AND NOT EXISTS (SELECT NULL
FROM tblSequence s
WHERE s.siteid = n.siteid
AND s.sessionguid = n.sessionguid
AND [TimeStamp] <= DATEADD(mi, -@minutes, GETDATE()))
ORDER BY n.usersessionsequenceid DESC
");
cmd.Parameters.Add("@resultCount", SqlDbType.Int).Value = resultCount;
cmd.Parameters.Add("@minutes", SqlDbType.Int).Value = minutes;
cmd.Parameters.Add("@siteID", SqlDbType.Int).Value = siteID;
I have a class containing all the fields selected from the SQL called "SimpleSession". Thanks in advance for any help.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
老实说，我宁愿为此语句创建一个视图并在 Linq2Sql 中使用这个简单的视图......
To be honest, I would rather create a View for this statement and use that simple view in Linq2Sql...
事实上，很多时候没有理由将复杂的 SQL 转换为某些 LINQ 等效项（即使是在兼容语句的情况下）。
LinqToSql 需要在运行任何 LINQ 命令时动态创建 SQL 命令。 那么，如果我们已经知道哪一个是最好的 SQL 查询来解决，为什么我们会想要开销呢？
我们可以轻松创建调用某些视图或存储过程并将其按类型返回的 LINQ 方法对象。您的程序将继续完全面向对象，但没有不必要的开销。
In fact, many times there isn't no reason to convert complex SQL to some LINQ equivalent (even in case of compatible statements).
LinqToSql needs to dynamically create the SQL commands when running any LINQ command. So, do why we would want to overhead if we already know which is the best SQL query to solve?
We can easily create LINQ methods that call some View or Stored Procedure and returns it as typed objects. Your program will continue to be all fully object oriented but without unecessary overheads.
使用它
对于第一个查询，您可以在 tblSequence.AsEnumerable() 中的 seq 中
其中 seq.Field("SiteID") == siteID
&& seq.Field("时间戳") > DateTime.Now.AddMinutes(分钟)
选择新的
{
seq.UserSessionSequenceID,
seq.SessionGuid,
seq.SiteID,
序列IP，
seq.UrlTitle,
seq.Url,
seq.Referer，
seq.BrowserWidth,
seq.浏览器高度，
seq.时间戳
};
对于 row_number 请参阅此 如何投影Linq 查询结果中的行号
for first query u can use this
from seq in tblSequence.AsEnumerable()
where seq.Field("SiteID") == siteID
&& seq.Field("Timestamp") > DateTime.Now.AddMinutes(minutes)
select new
{
seq.UserSessionSequenceID,
seq.SessionGuid,
seq.SiteID,
seq.IP,
seq.UrlTitle,
seq.Url,
seq.Referer,
seq.BrowserWidth,
seq.BrowserHeight,
seq.Timestamp
};
for row_number refer this How To Project a Line Number Into Linq Query Results