0

Given this Linq query to connect 3 tables just to retrieve the CategoryName I end up with the 2 categories but also with the same title twice. I suspect I need a group by to eliminate the duplicate title and keep the 2 categories.

From ca In be_Categories Join c In be_PostCategory On ca.CategoryID Equals (c.CategoryID) 
Join p1 In be_Posts On c.PostID Equals (p1.PostID) Where p1.PostRowID = 1002
Select ca.CategoryName, p1.title

I have test the query in LinqPad and this is what I get: enter image description here

I suspect I need a Group By to get the query working right so i can return the 2 categories but just one of everything else. I've been reading various resources on Group By but am no closer to getting it to work.

Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
Ashok Padmanabhan
  • 2,110
  • 1
  • 19
  • 36

1 Answers1

1

From the query and the results it looks like there is 2 Categories that meet the filter criteria, Group By will not make them go away, you might be missing a join which might filter out one, but most likely you need more filter criteria to eliminate one of the Category names

Here's a link that might help you do it in one query the .Aggregate()

Using LINQ to concatenate strings

Community
  • 1
  • 1
bowlturner
  • 1,968
  • 4
  • 23
  • 35
  • I'm trying to figure out a way to get both categories while still getting a single title etc. Any other way to filter it down to 1 title and 2 categories? – Ashok Padmanabhan Jul 23 '14 at 19:45
  • How do you expect the data to look? If a title fit into 10 categories, you'd get 10 rows back each paired with the title. Do you want all the categories concatenated together for one line returned? – bowlturner Jul 23 '14 at 20:07
  • Yes - all the categories for a single title. – Ashok Padmanabhan Jul 23 '14 at 20:13
  • I would take your current result set and manipulate it to how you want the data to look before returning it. I'm sure there is a way to 'continue' the query to make it in one but I don't have anything close to be able to start playing with it to get it right. – bowlturner Jul 23 '14 at 20:30
  • 1
    added a link that I think will help. – bowlturner Jul 23 '14 at 20:43