Linq2SQL .Union() throwing 'local sequence cannot be used ...' error

Question

I am currently updating a Windows Phone app written, obviously, in Silverlight For Windows Phone. I am attempting to Union two groups of objects, but I am getting the following error

Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator.

I have read about being unable to join a local array and a table, however I'm simply at a loss of how to implement this in my code... Quite frankly I'm not even sure if that is what is causing my problem.

This is my current code:

    //Creating a 'blank' group of movies objects
    List<String> characters = new List<String> { "#", "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z" };
    List<Group<Movies>> emptyGroups = new List<Group<Movies>>();
    characters.ForEach(x => emptyGroups.Add(new Group<Movies>(x, new List<Movies>())));

    //Querying the database
    var allMovies = from m in dataContext.MoviesTable
                            orderby m.Title
                            select m;

    //Grouping all movie objects into a container of Group(string name, IEnumerable<T> items)
    var groupedMovies = (from t in allMovies
                         group t by t.Title.ToLower()[0].ToString() into grp
                         orderby grp.Key
                         select new Group<Movies>(grp.Key, grp));

    // This is where everything falls over.
    IEnumerable LongList.ItemsSource = (from t in groupedMovies.Union(emptyGroups)
                                              orderby t.Title
                                              select t).ToList();

The List<T> 'emptyGroups' works fine, the IQueryable<T> 'groupedMovies' works fine. But I cannot figure out how to put them together.

Help? Pretty please?

Answer 1

You can AsEnumerable() to force groupedMovies to execute in SQL before performing the union, ie

IEnumerable LongList.ItemsSource = (
    from t in goupedMovies.AsEnumerable().Union(emptyGroups)
    orderby t.Title
    select t).ToList();