Using Distinct in LINQ to SQL on the primary key

1
votes

I have a system that uses tags to categorize content similar to the way Stack Overflow does. I am trying to generate a list of the most recently used tags using LINQ to SQL.

(from x in cm.ContentTags
    join t in cm.Tags on x.TagID equals t.TagID
    orderby x.ContentItem.Date descending select t)
    .Distinct(p => p.TagID) // <-- Ideally I'd be able to do this

The Tags table has a many to many relationship with the ContentItems table. ContentTags joins them, each tuple having a reference to the Tag and ContentItem.

I can't just use distinct because it compares on Tablename.* rather than Tablename.PrimaryKey, and I can't implement an IEqualityComparer since that doesn't translate to SQL, and I don't want to pull potential millions of records from the DB with .ToList(). So, what should I do?

3
c#linq-to-sql

3 Answers

1
votes

You could write your own query provider, that supports such an overloaded distinct operator. It's not cheap, but it would probably be worthwhile, particularly if you could make it's query generation composable. That would enable a lot of customizations.

Otherwise you could create a stored proc or a view.

0
votes

Use a subselect:

var result = from t in cm.Tags
             where(
                   from x in cm.ContentTags
                   where x.TagID == t.TagID
                  ).Contains(t.TagID)
             select t;

This will mean only distinct records are returned form cm.Tags, only problem is you will need to find some way to order result

0
votes

Use:

.GroupBy(x => x.TagID)

And then extract the data in:

Select(x => x.First().Example)