Convert linq to lambda with multiple unions

Question

I am trying to convert this linq request to lambda

var query = (from a in context.Table_A join u in context.Table_B on a.scored equals u.userid join u2 in context.Table_B on a.scorer equals u2.userid from cs in a.Table_C //(Table_A is related to Table_C) where (a.date>= startdate && a.date < enddate) select new MSViewModel { scored= u.User.name, scorer= u2.User.name, subject_name = cs.Subject.name, score = cs.score, categoryid = cs.id, }) .AsEnumerable() .GroupBy(t => t.scored) .ToList(); 

So far this is what I have. I lost a little what to do next.

  var tobi = db.Table_A.Join(db.Table_B,a=>a.scored,u=>u.userid, (a,u) => new {scored=u.User.name }); db.Table_A.Join(db.Table_B,a1=>a1.scorer,u2=>u2.userid, (a,u2)=> new {scorer= u2.User.name}); 

Answer 1

First, I would agree with Arran's comment: a query expression will be much easier to deal with. I am absolutely on board using a lambda form, where it makes sense, but combining is usually much simpler in query expressions.

Having said that, you basically need to emulate transparent identifiers . The code below is not verified, but it looks vague to me.

 var query = context.Table_A .Join(context.Table_B, a => a.scored, u => u.userid, (a, u) => new { a, u }) .Join(context.Table_B, p => pascorer, u2 => u2.userid, (p, u2) => new { p, u2 }) .SelectMany(q => qpaTableC, (q, cs) => new { q, cs }) .Where(r => rqpadate >= startdate && rqpadate < enddate) .Select(q => new MSViewModel { scored= rqpuUser.name, scorer= rqu2.User.name, subject_name = r.cs.Subject.name, score = r.cs.score, categoryid = r.cs.id, }) .AsEnumerable() .GroupBy(t => t.scored) .ToList(); 

Basically p and q and r are transparent identifiers here. You have three of them, because you have two connections and the following from clause.

Answer 2

To expand my comment if you have a Resharper:

Reseller showing the ability to convert LINQ to Lambada

After conversion:

Answer 3

Try the following:

  var query = (_context.Table_A.Join(_context.Table_B, a => a.scored, u => u.userid, (a, u) => new {a, u}).Join(_context.Table_B, @t => @tascorer, u2 => u2.userid, (@t, u2) => new {@t, u2}). SelectMany(@t => _context.Table_A, (@t, cs) => new MSViewModel() { scored = @ [e-mail] , scorer= @t.u2.User.name, subject_name = @t.cs.Subject.name, score = @t.cs.score, categoryid = @t.cs.id, })).AsEnumerable() .ToList();