i have table
id pagenane username
i want this sql query
select pagename, count(*) as num from pagestat where username='name' group by pagename
how can i do it by linq?
views:
54answers:
2
+2
A:
Well, try this:
var query = from row in context.pageStat
where row.UserName == "name"
group row by row.PageName into g
select new { PageName = g.Key, Count = g.Count() };
Jon Skeet
2010-07-07 08:28:39
how can i return this value from function? something like return query.ToList(); but this is anonymous type
kusanagi
2010-07-07 08:44:18
@kusanagi: If you're using .NET 4 you could use tuples; otherwise you could just create your own named type with the pagename and count as properties... basically a named version of the anonymous type.
Jon Skeet
2010-07-07 08:47:38
Note that `row.UserName == "name"` will be case-sensitive. Perhaps not what you want for a username, and (depending on the DB collation) perhaps not what the original SQL query did. Use `row.UserName.Equals("name", StringComparison.OrdinalIgnoreCase)` for an insensitive comparison; it's supported in both L2S and L2E.
Craig Stuntz
2010-07-07 12:39:23
A:
var retVal = (from s in dataContext.YourTable where
s.UserName.Equals("name")
group s by s.PageName into k
select new {PageName = k.Key, Count = k.Count() }).ToList();
Serkan Hekimoglu
2010-07-07 08:31:06
`s` won't be in scope in the `select` clause. At that point you're dealing with *groups* rather than an individual record. Fortunately you can use the key of the group.
Jon Skeet
2010-07-07 08:32:07