How do I translate the SQL "have" condition in LinqToSQL or LinqToEntites?
Could you please tell me how to translate the following SQL code to Linq To SQL or Linq To Entites?
Correct SQL code:
select CollectId, url, userid, pubtime from collect group url, collectid, userid, pubtime having pubtime> = (select max (pubtime) from collect d where d.url = collect.url) order by Collect.pubtime desc
Database table script:
if exists (select * from sysobjects where id = OBJECT_ID ('[Collect]') and OBJECTPROPERTY (id, 'IsUserTable') = 1) DROP TABLE [Collect]
CREATE TABLE [Collect] ([CollectId] [int] IDENTIFICATION (1, 1) NOT NULL, [Url] [nvarchar] (200) NULL, [UserId] [nvarchar] (50) NULL, [PubTime] [datetime] NULL )
ALTER TABLE WITH NOCHECK ADD CONSTRAINT [PK_Collect] PRIMARY KEY NON-DISTRIBUTED ([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', 'Volume', '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
source to share
Since your "presence" condition is not actually in the aggregated column, could 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
You get the same result if you provided a dataset. Then the LINQ statement becomes simple enough:
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 misinterpret your intentions. Perhaps my version of the request is not doing exactly what you want. If so, please leave me a comment and I will delete the answer so as not to confuse the problem.
source to share