ansaurus

Question

How can I get distinct records from many to many relationship using Linq to Sql

Answer 1

A:

topics
   .SelectMany(topic => topic.ArticleTopic)
   .Select(articleTopic => article)
   .Distinct()

But it is not obvious to me what you mean by top n - I can not see any ranking information in your question.

Daniel Brückner 2010-06-28 17:01:52

There is another 1:n table called ArticleRating that has a FK of ArticleId and Score. For example, I need the top 3 highest ranked articles for 3 topics but the article should only appear once for not only each topic, but for the entire result set.

NATO24 2010-06-28 17:17:13

The highest rated articles over all topics? Then there might be no article for some topics. The top rated article for each topic? In this case how do you select which topic "is done" if you encounter the same top rated article in more then one topic?

Daniel Brückner 2010-06-28 18:36:05

And what about degenerated cases like three topics T1, T2, T3 with the same two articles A1 and A2 each? Return only two articles deviating from the one article per topic rule or return one article twice deviating from the every article only once rule?

Daniel Brückner 2010-06-28 18:40:34

If I'm following you, it won't matter which topic "wins" so to speak. It should be the highest rated article over all topics. If articles A1 and A2 are showing under T1 (and they also belong to T2), then T2 should have the 3rd top article for that topic.

NATO24 2010-06-28 20:29:30

Now I got it ... i slightly misunderstood your question. You want to select the top m articles of each of the n topics. But not exactly - if an article belongs to multiple topics you want to select it only once and pick a lower rated article for the other topic(s), right?

Daniel Brückner 2010-06-28 23:24:13

Assume T1 with X, Z and A; T2 with Y, Z and B. Take top two of both topics. X for T1, Y for T2 and Z for one of both. How to decide between taking A or B? The highest rated one?

Daniel Brückner 2010-06-28 23:31:13

If one takes the highest rated, is it then okay to select more than m articles for some topics? T3 with P, Q and A. Picking A will select the third article from T3.

Daniel Brückner 2010-06-28 23:37:01

Exactly right. Highest rated should dictate whether A or B is selected. It should be fine to select more than m articles as I can see the issue if not. Thanks for hanging with me on this. I've tried several approaches, but nothing I'm satisfied with.

NATO24 2010-06-28 23:50:19

Any suggestions?

NATO24 2010-07-09 04:00:46

ansaurus

tags:

views:

answers:

How can I get distinct records from many to many relationship using Linq to Sql

related questions