Use LINQ to convert comma separated strings in a table into a distinct collection of values

I'm working through this MVC3 tutorial and have entered the genre of a film as a comma separated string.

In part 6 we take the genres from the table to populate a drop down list.

I'd like to populate the drop down list with a distinct collection of single genres but I just can't get it to work.

This is what the tutorial suggest as a start point

var GenreLst = new List<string>();

var GenreQry = from d in db.Movies
                   orderby d.Genre
                   select d.Genre;
GenreLst.AddRange(GenreQry.Distinct());

... and this is where I'd got to

var GenreLst = new List<string>();

var GenreQry = (from d in db.Movies
                orderby d.Genre
                select d.Genre ).Select(s=>s.Split(','))
开发者_如何学Go                .Distinct();

GenreLst.AddRange( GenreQry );

Linq2Sql doesn't know s.Split(',') method, so it should throw an exception, you can do this:

var GenreQry = (from d in db.Movies
             orderby d.Genre                 
             select d.Genre ).Distinct().ToList(); 

GenreLst.AddRange( GenreQry.SelectMany(x=>x.Split(',')).Distinct());

about above code: When calling ToList() in the end of query, your data will be fetched and your query in fact is list, in second part, SelectMany flats separated strings as a IEnumberable of strings.

Edit: Also in first part you can call .AsEnumerable() instead of .ToList() for fetching data, it seems better way.

In case you find the SelectMany syntax a bit confusing, consider the following (which compiles into a select many method call under the covers but I find easier to read):

var GenreQry = (from d in db.Movies.AsEnumerable() 
                from s in d.Split(',')
                select s)
               .Distinct()
               .OrderBy(s => s);