Click here to Skip to main content
15,121,741 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
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
Updated 1-May-11 3:55am
v2
Comments
[no name] 1-May-11 8:58am
   
When you say "it has some errors", it is very helpful to include what those errors are.

1 solution

Based on
SQL
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

C#
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 :)
   
v4
Comments
yesotaso 1-May-11 13:52pm
   
It seems I missed the User ID part but I guess you can filter rest.
Mohammad Dayyan 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)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900