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?
Add a comment |
2 Answers
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() }; 
3 Comments
kusanagi
how can i return this value from function? something like return query.ToList(); but this is anonymous type
Jon Skeet
@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.
Craig Stuntz
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. 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(); 2 Comments
Jon Skeet
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.
Serkan Hekimoglu
Thank you Jon, I will be careful, fixing now.