Performing GroupBy and Union using LINQ to fetch records

Question

Here's the situation.

I am trying to perform a Union between two queries using LINQ. I have applied GroupBy in both queries, using anonymous types. I dont know exactly, but probably the usage of anonymous type is leading me to this issue where I am unable to perform that Union operation. It shows me exception like this :

'System.Linq.IQueryable' does not contain a definition for 'Union' and the best extension method overload 'System.Linq.ParallelEnumerable.Union(System.Linq.ParallelQuery, System.Collections.Generic.IEnumerable)' has some invalid arguments

Here is the query :

var records = (from o in _context.Table1
                       join q in _context.Table2 on o.UserId equals q.UserId
                       group new { o, q }
                       by new { o.Name, o.Date, o.IsDone, o.IsDl, o.DateChanged, o.UserId, q.FirstName, q.LastName } into data
                       select new
                       {
                           NameP = data.Key.Name,
                           Date = data.Key.Date,
                           IsDone = data.Key.IsDone,
                           IsDl = data.Key.IsDl,
                           DateDone = data.Key.DateChanged,
                           Name = data.Key.FirstName +" "+ data.Key.LastName
                       }).Union(
                            from i in _context.Table1
                            where i.UserId == null
                            group i by new {o.Name, o.Date, o.IsDone, o.IsDl, o.DateChanged, o.UserId} into newData
                            select new
                            {
                                NameP = newData.Key.Name,
                                Date= newData.Key.Date,
                                IsDone = newData.Key.IsDone,
                                IsDl = newData.Key.IsDl,
                                DateDone = ' ',
                                Name = ' '
                            }).ToList();

What I am trying to achieve here is to get records of both cases, when UserId is null as well as when its not null using group by since records are repeated.

Any suggestions or guidance here on what I am doing wrong are appreciated.

Make sure that the two anonymous types have the same data type for all proprties.. the only ones I can speculate on are the date ones.. Maybe the first `DateDone` is actually a `DateTime` type and you are setting the sencond one to a `string`... — Michael Coxon, Apr 20 '15 at 12:02
Its because you are using anonymous type. Try creating dummy result class and try. or you can try full outer join. http://stackoverflow.com/questions/5489987/linq-full-outer-join — Girish Sakhare, Apr 20 '15 at 12:06
@MichaelCoxon - I get your point. It should be a date. Let me try it this way. — Dhrumil, Apr 20 '15 at 12:14
@MichaelCoxon - It worked Michael. I just made a couple of changes and it got to work. Many Thanks. — Dhrumil, Apr 20 '15 at 12:47
I have added my comment as an answer. Please accept it so that others can find it. Cheers. — Michael Coxon, Apr 20 '15 at 13:30

Michael Coxon · Accepted Answer · 2015-04-20T13:29:35.297

Make sure that the two anonymous types have the same data type for all proprties.

The only ones I can speculate on are the date ones. Maybe the first DateDone is actually a DateTime type and you are setting the second one to a string...

var records = (from o in _context.Table1
               join q in _context.Table2 on o.UserId equals q.UserId
               group new { o, q }
               by new 
               { 
                   o.Name, 
                   o.Date,  
                   o.IsDone,  
                   o.IsDl,  
                   o.DateChanged,  
                   o.UserId,  
                   q.FirstName,  
                   q.LastName  
               } into data
               select new
               {
                   NameP = data.Key.Name,
                   Date = data.Key.Date,
                   IsDone = data.Key.IsDone,
                   IsDl = data.Key.IsDl,
                   DateDone = data.Key.DateChanged,
                   Name = data.Key.FirstName +" "+ data.Key.LastName
               }).Union(from i in _context.Table1
                        where i.UserId == null
                        group i by new 
                        {
                            o.Name,
                            o.Date, 
                            o.IsDone, 
                            o.IsDl, 
                            o.DateChanged, 
                            o.UserId
                        } into newData
                        select new
                        {
                            NameP = newData.Key.Name,
                            Date = newData.Key.Date,
                            IsDone = newData.Key.IsDone,
                            IsDl = newData.Key.IsDl,
                            DateDone = new DateTime(0),
                            Name = ' '
                        }).ToList();

Performing GroupBy and Union using LINQ to fetch records

1 Answers1