如何将SQL代码“有”条件转换为LinqToSQL或LinqToEntites？

Could you tell me how to translate the following SQL code to Linq To SQL or Linq To Entites?

你能告诉我如何将以下SQL代码翻译成Linq To SQL或Linq To Entites吗?

The correct SQL code is:

正确的SQL代码是:

select CollectId,url,userid,pubtime from Collect group by url,collectid,userid,pubtime having pubtime >= (select max(pubtime) from collect d where d.url = collect.url ) order by Collect.pubtime desc

从收集组中选择CollectId,url,userid,pubtime by url,collectid,userid,pubtime,pubtime> =(select multip(pubtime)from collect d where d.url = collect.url)order by Collect.pubtime desc

The database table script is:

数据库表脚本是:

if exists (select * from sysobjects where id = OBJECT_ID('[Collect]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [Collect]

if exists(从sysobjects中选择*,其中id = OBJECT_ID('[Collect]')和OBJECTPROPERTY(id,'IsUserTable')= 1)DROP TABLE [收集]

CREATE TABLE [Collect] ( [CollectId] [int] IDENTITY (1, 1) NOT NULL, [Url] [nvarchar] (200) NULL, [UserId] [nvarchar] (50) NULL, [PubTime] [datetime] NULL)

CREATE TABLE [Collect]([CollectId] [int] IDENTITY(1,1)NOT NULL,[Url] [nvarchar](200)NULL,[UserId] [nvarchar](50)NULL,[PubTime] [datetime] NULL )

ALTER TABLE [Collect] WITH NOCHECK ADD CONSTRAINT [PK_Collect] PRIMARY KEY NONCLUSTERED ( [CollectId] ) SET IDENTITY_INSERT [Collect] ON

ALTER TABLE [收集] WITH NOCHECK ADD CONSTRAINT [PK_Collect] PRIMARY KEY NONCLUSTERED([CollectId])SET IDENTITY_INSERT [Collect] ON

INSERT [Collect] ([CollectId],[Url],[UserId],[PubTime]) VALUES ( 1,'www.sohu.com','Mike','2008-10-10 0:00:00') INSERT [Collect] ([CollectId],[Url],[UserId],[PubTime]) VALUES ( 2,'www.echina365.com','Lily','2008-10-15 0:00:00') INSERT [Collect] ([CollectId],[Url],[UserId],[PubTime]) VALUES ( 3,'www.php.com','Tom','2008-10-20 0:00:00') INSERT [Collect] ([CollectId],[Url],[UserId],[PubTime]) VALUES ( 4,'www.echina365.com','YaoMing','2008-10-23 0:00:00') INSERT [Collect] ([CollectId],[Url],[UserId],[PubTime]) VALUES ( 5,'www.echina365.com','Mike','2008-10-25 0:00:00') INSERT [Collect] ([CollectId],[Url],[UserId],[PubTime]) VALUES ( 6,'www.sohu.com','Jack','2008-10-26 0:00:00') INSERT [Collect] ([CollectId],[Url],[UserId],[PubTime]) VALUES ( 7,'www.echina365.com','Tracy','2008-11-2 0:00:00') INSERT [Collect] ([CollectId],[Url],[UserId],[PubTime]) VALUES ( 8,'www.php.com','YaoMing','2008-11-5 0:00:00')

INSERT [收集]([CollectId],[Url],[UserId],[PubTime])VALUES(1,'www.sohu.com','Mike','2008-10-10 0:00:00') INSERT [收集]([CollectId],[Url],[UserId],[PubTime])VALUES(2,'www.echina365.com','Lily','2008-10-15 0:00:00') INSERT [收集]([CollectId],[Url],[UserId],[PubTime])VALUES(3,'www.php.com','Tom','2008-10-20 0:00:00') INSERT [收集]([CollectId],[Url],[UserId],[PubTime])VALUES(4,'www.echina365.com','YaoMing','2008-10-23 0:00:00') INSERT [收集]([CollectId],[Url],[UserId],[PubTime])VALUES(5,'www.echina365.com','Mike','2008-10-25 0:00:00') INSERT [收集]([CollectId],[Url],[UserId],[PubTime])VALUES(6,'www.sohu.com','Jack','2008-10-26 0:00:00') INSERT [收集]([CollectId],[Url],[UserId],[PubTime])VALUES(7,'www.echina365.com','Tracy','2008-11-2 0:00:00') INSERT [收集]([CollectId],[Url],[UserId],[PubTime])VALUES(8,'www.php.com','YaoMing','2008-11-5 0:00:00')

SET IDENTITY_INSERT [Collect] OFF

SET IDENTITY_INSERT [收集]关闭

1 个解决方案

#1

Since your "having" condition isn't actually on an aggregated column, couldn't you just use the "where" clause?

由于您的“拥有”条件实际上不在聚合列上,您不能只使用“where”子句吗?

select distinct CollectId, url, userid, pubtime
from Collect
where pubtime >= (select max(pubtime) from collect d where d.url = collect.url)
order by Collect.pubtime desc

This gets the same result given the dataset you've supplied. The LINQ statement then becomes reasonably simple:

根据您提供的数据集,这会得到相同的结果。然后,LINQ语句变得相当简单:

var rows = (from c in Collect
where c.PubTime >= (
    from d in Collect
    where d.Url == c.Url
    select d.PubTime).Max()
orderby c.PubTime descending
select c).Distinct();

I could be misinterpreting your intent though. Perhaps my version of the query doesn't do exactly what you want. If so, leave me a comment and I'll delete the answer so as not to confuse the issue.

我可能会误解你的意图。也许我的查询版本并不能完全符合您的要求。如果是这样,请给我留言,我会删除答案,以免混淆问题。

#1