Tutorial :how to translate the SQL code “having” condition into LinqToSQL or LinqToEntites?



Question:

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

The correct SQL code is:

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

The database table script is:

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

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

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')

SET IDENTITY_INSERT [Collect] OFF


Solution:1

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

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:

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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »