Databases
StackOverflow2013
Postsdbo
POhow to translate the SQL code "having" condition into LinqToSQL or LinqToEntites?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POhow to translate the SQL code "having" condition into LinqToSQL or LinqToEntites?
    text
    copied!<p>Could you tell me how to translate the following SQL code to Linq To SQL or Linq To Entites?</p> <p>The correct SQL code is: </p> <blockquote> <p>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</p> </blockquote> <p>The database table script is:</p> <blockquote> <p>if exists (select * from sysobjects where id = OBJECT_ID('[Collect]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [Collect]</p> <p>CREATE TABLE [Collect] ( [CollectId] [int] IDENTITY (1, 1) NOT NULL, [Url] [nvarchar] (200) NULL, [UserId] [nvarchar] (50) NULL, [PubTime] [datetime] NULL)</p> <p>ALTER TABLE [Collect] WITH NOCHECK ADD CONSTRAINT [PK_Collect] PRIMARY KEY NONCLUSTERED ( [CollectId] ) SET IDENTITY_INSERT [Collect] ON</p> <p>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')</p> <p>SET IDENTITY_INSERT [Collect] OFF</p> </blockquote>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload