Click here to Skip to main content
12,952,159 members (55,717 online)
Rate this:
 
Please Sign up or sign in to 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 1-May-11 2:52am
Updated 1-May-11 2:55am
v2
Comments
Mark Nischalke 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 :)
  Permalink  
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)

    Print Answers RSS
Top Experts
Last 24hrsThis month
OriginalGriff 6,239
CHill60 3,490
Maciej Los 3,083
Jochen Arndt 1,975
ppolymorphe 1,880


Advertise | Privacy | Mobile
Web02 | 2.8.170525.1 | Last Updated 1 May 2011
Copyright © CodeProject, 1999-2017
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