How to merge (REMEMBER merge not combine!) these two queries

Question

I have these two linq queries:

var combo1 = from c in db.comments
             join p in db.picture on c.targetpictureid equals p.idpictures
             join u in db.users on c.iduser equals u.iduser
             select new TCommentDTO
             {
                 idcomments=c.idcomments,
                 comment1 = c.comment1,
                 targetpictureid = c.targetpictureid,
                 ctime = c.ctime,
                 iduofpic=p.iduser,
                 iduofcommentor=c.iduser,
                 profilepicofcommentor=u.profilepic,
                 usernameofcommentor=u.username,
                 picFilename=p.picFilename,
                 picTitle=p.picTitle
             };

var combo2 = from f in db.followers
             join u in db.users on f.iduser equals u.iduser
             select new TfollowerDTO
             {
                idfollowers = f.idfollowers,
                iduser = f.iduser,
                targetiduser = f.targetiduser,
                startedfollowing = f.startedfollowing,
                unoffollower = u.username,
                ppoffollower = u.profilepic,
                status = u.status
             };

I am using web API that returns JSON. I want to merge the output of these two queries.

I want to rewrite this code in such a way that comments and follower data should be merged (not combined) with respect to time based on ctime and startedfollowing. If a user has a new comment, the comment should come first and if the follower is first, the follower data should come first.I can not use Union() and Concat() because firstly both classes have different members and secondly i dont want both json object to be combined.

Something like this:

{ //comments data },
{ //follower data},
{ //comments data },
{ //comments data },
{ //comments data },
{ //follower data}

So how to do this task?

Yeah i do know about Union() and Concat(), but cant use them, because firstly both classes have different members and secondly i dont want both json object to be combined. — Obvious, Oct 31 '13 at 13:25
It sounds like you have to [full outer join](http://stackoverflow.com/q/2085422/1043380) your two queries, but I'm not sure. — gunr2171, Oct 31 '13 at 13:26
Other than printing them out and using duct tape, I don't get how the two result sets can relate to each other... Are they bound by one of the many `userid`s ? — Alex, Oct 31 '13 at 13:36

score 1 · Accepted Answer · answered Oct 31 '13 at 13:38

What about:

public class TDtoWrapper
{
    public DateTime SortKey {get;set; }
    public Object Member {get;set;}
}


var result1 = from c in combo1
              select new TDtoWrapper { SortKey = c.ctime, Member = c }

var result2 = from c in combo2
              select new TDtoWraller { SortKey = c.startedfollowing, Member = c }

var result = result1.Concat(result2).Orderby(x => x.SortKey).Select(x => x.Member);

if you can rewrite the origninal queries, you can ommit the Dto class and write

var combo1 = from c in db.comments
             join p in db.picture on c.targetpictureid equals p.idpictures
             join u in db.users on c.iduser equals u.iduser
             select new 
             { 
                SortKey = c.ctime, 
                Member = (object) new TCommentDTO
                {
                  idcomments=c.idcomments,
                  comment1 = c.comment1,
                  targetpictureid = c.targetpictureid,
                  ctime = c.ctime,
                  iduofpic=p.iduser,
                  iduofcommentor=c.iduser,
                  profilepicofcommentor=u.profilepic,
                  usernameofcommentor=u.username,
                  picFilename=p.picFilename,
                  picTitle=p.picTitle
                 }
             };


var combo2 = ...

var result = from c in combo1.Concat(combo2)
             orderby c.SortKey
             select c.Member;

I am getting this error while implementing your solution, Cannot implicitly convert type 'System.Linq.IQueryable — Obvious, Oct 31 '13 at 14:09
solved that, now getting a runtime error :DbUnionAllExpression requires arguments with compatible collection ResultTypes — Obvious, Oct 31 '13 at 14:29
Can't tell where excatly this happens, but you can try `somelinquery.Cast — Jürgen Steinblock, Oct 31 '13 at 14:31

How to merge (REMEMBER merge not combine!) these two queries

1 Answers1