I'm trying to convert some old queries from stored procs + temp tables into linq + llblgen

This one is about a standard tag cloud (i know they're crap, but I have a specific need unfortunately), it's trying to provide a list of 'Tags' and the 'Amount of threads tagged'

Table looks something like this:

[Tag] [Thread] A 1000 A 2000 B 1000

A basic query would result in:

Tag A has 2 Threads Tag B has 1 Thread

This is fine until I ask:

What is the result if I base it on only items tagged with 'Tag A', the query should result in:

Tag A has 2 associated threads (thread 1000 & thread 2000) Tag B has 0 Threads (thread 1000 is not tagged with 'A', so it's not here)

Standard tag cloud...

It's definitely confusing me, and i'm trying everything to work around this, as I keep running into the same dead end, here's the code:

    

 var view = from t in linq.XThreadTags
                  from t2 in linq.XThreadTags.Where(x => x.TagId == 'A') <-actually an int
                  where t2.ThreadId == t.ThreadId
                    select t;
        
        var results = from v in view.Select(x => x.TagId).Distinct()
                      join tg in linq.XThreadTag on v equals tg.TagId
                      select new Tag
                      {
                          Name = tg.Tag,
                          TagId = tg.TagId,
                          TotalCount = view.Where(x => x.TagId == tg.TagId).Count()
                      };

dqe = 2.6.08.1114 linqSup 35 = 2.6.09.729 orm = 2.6.09.0511

     --------------ERROR---------------

[ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: Invalid column name 'ThreadID'. Invalid column name 'ThreadID'. Invalid column name 'RowID'. Invalid column name 'ThreadID'. Invalid column name 'SectionID'. Invalid column name 'ApplicationType'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.] SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) +586 SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsDataReader(ITransaction transactionToUse, IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) +180 SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsProjection(List1 valueProjectors, IGeneralDataProjector projector, ITransaction transactionToUse, IRetrievalQuery queryToExecute, Dictionary2 typeConvertersToRun) +73 SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsProjection(List1 valueProjectors, IGeneralDataProjector projector, ITransaction transactionToUse, IEntityFields fields, IPredicateExpression filter, IRelationCollection relations, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize) +460 SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider.ExecuteValueListProjection(QueryExpression toExecute) +1121 SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression) +1240 SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) +99 SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) +75 SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery1.Execute() +112 SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() +70 System.Collections.Generic.List1..ctor(IEnumerable1 collection) +471 System.Linq.Enumerable.ToList(IEnumerable1 source) +78

I think you are writing wrong your linq query. In this case you should use a group clause, and then a subquery. This is an approximate solution:

var q = from t in linq.XThreadTags
        group t by t.TagId into g
            select new
                       {
                           TagId = g.Key, 
                           NumberOfThreads = g.Count(),
                           Threads = (from t2 in linq.XThreadTags  where t2.TagId == g.Key select t2.ThreadId)
                       };

var results = q.ToList();
foreach (var x in results)
{
    Console.WriteLine("Tag {0} has {1} threads", x.TagId, x.NumberOfThreads);

    string joinedThreads =String.Join(",", x.Threads.ToArray());
    Console.WriteLine("Tag {0} has {1} associated threads ({3})", x.TagId, x.NumberOfThreads, joinedThreads);
}

Thanks mate,

From there I got to:

        var sel = from ts in linq.XThreadTags 
                  join t in linq.XThreadTag on ts.TagId equals t.TagId
                  where tagsids.Contains(ts.TagId)
                  group ts by ts.ThreadId
                  into g
                      where g.Count() == tagsids.Count()
                      select g.Key;
                
                
        var results2 = from t in linq.XThreadTag
                       join tt in linq.XThreadTags on t.TagId equals tt.TagId
                       where (sel.Contains(tt.ThreadId))
                      group t by t.TagId 
                      into g
                           where !tagsids.Contains(g.Key)
                      select new Tag
                      {
                          TagId = g.Key,
                          TotalCount = g.Count(),
                          Name = (from t2 in linq.XThreadTag where t2.TagId == g.Key select t2.Tag).FirstOrDefault()
                      };

Which works, and is reasonable when using millions of rows..

Thanks for that, Frank.