Select Distinct rows using Linq

Question

The data is as follow

ID Title        Category About           Link        CategoryID
1  The Matrix   Sci-Fi   Text goes here  http://...  1 
2  The Simpsons Cartoon  Text goes here  http://...  2
3  Avengers     Action   Text goes here  http://...  3
4  The Matrix   Sci-Fi   Text goes here  http://...  1
5  The One      Sci-Fi   Text goes here  http://...  1
6  The Hobbit   Sci-Fi   Text goes here  http://...  1

I have a checkbox list containing the categories. The problem is if the user selects 'Action' and 'Sci-Fi' as category to display The Matrix will be displayed twice.

This is my try for getting unique rows in SQL Query.

select distinct title, about, link from mytable
inner join tableCategories on categoryID = tableCategoriesID
group by title, about, link

Using the LINQ,

(from table in movieTables
join x in categoryIDList
on categoryID equals x
slect table).Distinct()

Note that the categories are in a separate table linked by the categoryID. Need help displaying unique or distinct rows in LINQ.

score 0 · Answer 1 · edited May 23 '17 at 12:29

You can happily select your result into a list of whatever you want:

var v = from entry in tables
        where matching_logic_here
        select new {id = some_id, val=some_value};

and then you can run your distinct on that list (well, a ToList() on the above will make it one), based on your needs.

The following should illustrate what i mean (just paste into linqpad. if you're using VS, get rid of the .Dump():

void Main()
{
    var input  = new List<mock_entry> {
        new mock_entry {id = 1, name="The Matrix", cat= "Sci-Fi"},
        new mock_entry {id = 2, name="The Simpsons" ,cat= "Cartoon"},
        new mock_entry {id = 3, name="Avengers" ,cat= "Action"},
        new mock_entry {id = 4, name="The Matrix", cat= "Sci-Fi"},
        new mock_entry {id = 5, name="The One" ,cat= "Sci-Fi"},
        new mock_entry {id = 6, name="The Hobbit",cat= "Sci-Fi"},
    };

    var v = input.Where(e=>e.cat == "Action" || e.cat =="Sci-Fi")
                .Dump()
                .Select(e => new {n = e.name, c =e.cat})
                .Dump()
    ;

    var d = v.Distinct()
            .Dump()
    ;
}

// Define other methods and classes here
public struct mock_entry {
    public int id {get;set;}
    public string name {get;set;}
    public string cat {get;set;}
}

Another option would be to use DistinctBy from more linq as suggested in this question

Edit:

Even simpler, you can use GroupBy, and just select the first entry (you'll lose the id though, but up to you).
Here's an example that will work with the above:

var v = input.GroupBy (i => i.name)
   .Select(e => e.First ())
   .Dump()
   .Where(e=>e.cat == "Action" || e.cat =="Sci-Fi")
   .Dump()
;

will yield:

1 The Matrix Sci-Fi
3 Avengers Action
5 The One Sci-Fi
6 The Hobbit Sci-Fi

Select Distinct rows using Linq

1 Answers1