views:

122

answers:

1

I have a following tables/classes structure in Linq to entities.

Books
{
 bookId,
 Title
}

Tags
{
  TagId
  Tag
}

BooksTags
{
 BookId
 TagId
}

Now I need to write a query which gives me result like this

Class Result
{
 bookId,
 Title,
 Tags
}

Tags should be comma separated text from the tags table by joining all three tables. How to get it done.

Thanks

Parminder

+2  A: 
(from b in books
select new { BookId = b.BookId,
             Title = b.Title,
             Tags = bookTag.Where(bt => bt.BookId == b.BookId).Select(bt => tags.Single(t => t.TagId == bt.TagId).TagTitle)
           }).ToList().Select( t => new 
           { BookId = b.BookId,
             Title = b.Title,
             Tags = string.Join(", ", t.Tags.ToArray())
           }
Stephan
thanks stephanbut i get the errorLINQ to Entities does not recognize the method 'System.String Join(System.String, System.String[])' method, and this method cannot be translated into a store expression.regardsParminder
Parminder
In that case you'll want to pull back the Tags as a List<Tag> and then join them on the client side. I'll edit to show.
Stephan
That was just awesome. Fantastic.Thanks a lot StephanParminder
Parminder
as the tables are joined in the model, so i cut it down bit(from b in context.Books select new { BookId = b.BookId, Title = b.Title, Tags = b.BooksTags.Select(bt => bt.Tags.Tag) }).ToList().Select(t => new { BookId = t.BookId, Title = t.Title, Tags = string.Join(", ", t.Tags.ToArray()) });thanks
Parminder