Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# LINQ
we have below SQL tables :
 
Tag
--------------------------------------
ID	|	tagName	|	UserID
--------------------------------------
01	|	c++	|	03
02	|	c#	|	03
03	|	asp.net	|	04
04	|	programming|	05
05	|	vb	|	07
06	|	php	|	07
07	|	java	|	12
08	|	class	|	12
 
////////////////////////////////

TagRelation
------------------------------
ID  |	tagID	|   topicID
------------------------------
01  |   01      |   22
02  |   02      |   22
03  |   08      |   22
04  |   06      |   12
05  |   02      |   12
06  |   04      |   104
07  |   02      |   104
08  |   01      |   104
09  |   02      |   17
10  |   05      |   17
 

////////////////////////////////
 
now I wanna get the number of repeating each tags in `TagRelation` table for eeach users with LINQ query.
I've used the follwoing query , but it doesn't compile, it has some errors.
DataClassesDataContext dbc = new DataClassesDataContext();
var query = from q in dbc.Tags
			where (q.UserID == this.UserID)
			from c in dbc.TagRelation
			where (c.tagID == q.id)
			select new
			{
				tagName = q.name.Distinct(),
				repeatNumber = c.Count()
			};
Actually I expected the following result :
 
result(if UserID==3)
-------------------------
tagName	|	repeatNumber
-------------------------
c++	|	02
c#	|	01
How can I do it?
Thanks
Posted 1-May-11 2:52am
Edited 1-May-11 2:55am
v2
Comments
Mark Nischalke at 1-May-11 8:58am
   
When you say "it has some errors", it is very helpful to include what those errors are.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Based on
SELECT T.tagName, TMP.myCount 
FROM Tag T INNER JOIN
(SELECT R.tagID, COUNT(*) as myCount FROM TagRelation R
GROUP BY R.tagID
HAVING COUNT(*) > 1) AS TMP ON T.ID = TMP.tagID
 
Linq should look like
 
var tmpArray = from p in TagRelation
         group p by p.tagID into g
         where g.Count() > 1
         select new { g.Key, myCount = g.Count() };
var resultArrary = (from d in Tag
        join a in tmpArray on a.Key equals d.ID
        select new { d.tagName, a.myCount }).ToArray();
 
If you can improve query, be my guest.
Of course you may go self-destuctive and put it into 1 statement Smile | :)
  Permalink  
v4
Comments
yesotaso at 1-May-11 13:52pm
   
It seems I missed the User ID part but I guess you can filter rest.
Mohammad Dayyan at 2-May-11 15:41pm
   
Thanks, but I could find the following query and it works : http://stackoverflow.com/questions/5848263/get-count-and-distinct-items-with-linq-query

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 480
1 mhegazy94 460
2 Ravi Bhavnani 190
3 Kornfeld Eliyahu Peter 185
4 Shemeemsha RA 160
0 Sergey Alexandrovich Kryukov 7,205
1 OriginalGriff 6,801
2 CPallini 5,350
3 George Jonsson 3,644
4 Gihan Liyanage 2,797


Advertise | Privacy | Mobile
Web02 | 2.8.140922.1 | Last Updated 1 May 2011
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100