Update: Problem has been solved! See answer section at the bottom!

TablesI have run into a problem which I am unable solve using Linq to Sql. I want flattened data from a database which holds knowledge base type information to get as flattened data. My need is to pass it on to a databound list control to display to the user. Shown to the right are the tables which are needed basically an article and associated tags.

The Article table holds the textual articles a summary and each article has an ID which is its primary key. The textual tag(s) which may describe the articles are saved on a one by one basis in the KB_Tag table and like the article table, it has a primary key ID named Tag_ID. Linking the articles to the actual tag(s) is done by the KB_ArticleTag table which have the foreign key relationships back to the Article table and the KB_Tag table.

Download SQL 2005 database backup here.

Linq Tried

My goal is to extract each article summary and its corresponding tags in data form such as -Summary, "tag1 tag2…"-

"Data by Numbers"| "Data"
"Linq overview"    | "Linq Data C#"

But what I get with my Linq query against my databaselooks like this (shown here using the output of LinqPad):

 Linq result showing two columns Summary and tags where the tags are IEnumerable<string> and not flattened

Here is the query used: 

from ar in dc.Articles
   join atgs in dc.KB_ArticleTags on ar.Article_ID equals atgs.Article_ID into iTags
   select new
   {
       ar.Summary,

       tags = from cpt in iTags
              join tgs in dc.KB_Tags on cpt.Tag_ID equals tgs.Tag_ID
               select tgs.Text

   };

Other things Tried

  • Posted to MSDN forums (Flatten FK Tercerary Table)
  • Response from Lawrence Parker suggested using the Aggregate extension:

    from ar in dc.Articles
join atgs in dc.KB_ArticleTags on ar.Article_ID equals atgs.Article_ID into iTags
select new
{
 ar.Summary,

 tags = (from cpt in iTags
  join tgs in dc.KB_Tags on cpt.Tag_ID equals tgs.Tag_ID
  select tgs.Text).Aggregate<String, String>
   ( String.Empty,
   ( String prev, String input ) => prev + (prev == String.Empty ? input : ", " + input) )
 }

But unfortunately the Aggragate generates a runtime error "The query operator ‘Aggregate’ is not supported." by Linq parser.

Answer

Now Lawrence had the right idea with the Aggregate, but the problem lies in the fact that Linq-To-Sql cannot handle that operation on the backend due to the structure being in an IQueryable. The solution is to bring the data to the front end by using the IEnumerable data source. By bring it away from the backend, the data contained can be operated on locally on the client.

from ar in dc.Articles.AsEnumerable()
join atgs in dc.KB_ArticleTags on ar.Article_ID equals atgs.Article_ID into iTags
select new
{
 ar.Summary,

 tags = (from cpt in iTags
   join tgs in dc.KB_Tags on cpt.Tag_ID equals tgs.Tag_ID
   select tgs.Text)
    .Aggregate<String, String>
     ( String.Empty, ( String prev, String input )
      => prev + (prev == String.Empty ? input : ", " + input) )
}
  • Share/Bookmark