CODEWITHSUNDEEP
c#linqlambda
deidara song
deidara song

Reputation: 209

convert linq to lambda with multiple joins

I'm trying to convert this linq query 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'm kinda lost 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});

Upvotes: 11

Views: 17826

Answers (3)

Jon Skeet
Jon Skeet

Reputation: 1499760

Firstly, I'd agree with Arran's comment: the query expression is going to be much simpler to deal with. I'm absolutely on board with using the lambda form where it makes sense, but joins are generally much simpler in query expressions.

Having said that, you basically need to imitate transparent identifiers. The code below is untested, but looks vaguely plausible 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 => p.a.scorer, u2 => u2.userid,
                         (p, u2) => new { p, u2 })
                   .SelectMany(q => q.p.a.TableC, (q, cs) => new { q, cs })
                   .Where(r => r.q.p.a.date >= startdate && r.q.p.a.date < enddate)
                   .Select(q => new MSViewModel {
                                  scored= r.q.p.u.User.name,
                                  scorer= r.q.u2.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 the transparent identifiers here. You've got three of them because you've got two joins and a subsequent from clause.

Upvotes: 20

Pal R
Pal R

Reputation: 534

To expand on my comment, If you have Resharper:

Resharper showing option to convert LINQ to Lambada enter image description here

After the conversion: enter image description here

Upvotes: 4

usman mehmood
usman mehmood

Reputation: 112

Try this one:

    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 => @t.a.scorer,
    u2 => u2.userid,
    (@t, u2) => new {@t, u2}).
    SelectMany(@t => _context.Table_A, (@t, cs) => new MSViewModel()
    {
    scored = @[email protected],
    scorer= @t.u2.User.name,
    subject_name = @t.cs.Subject.name,
    score = @t.cs.score,
    categoryid = @t.cs.id,
    })).AsEnumerable()
    .ToList();

Upvotes: 2

Related Questions