score:209

Accepted answer

for joins, i strongly prefer query-syntax for all the details that are happily hidden (not the least of which are the transparent identifiers involved with the intermediate projections along the way that are apparent in the dot-syntax equivalent). however, you asked regarding lambdas which i think you have everything you need - you just need to put it all together.

var categorizedproducts = product
    .join(productcategory, p => p.id, pc => pc.prodid, (p, pc) => new { p, pc })
    .join(category, ppc => ppc.pc.catid, c => c.id, (ppc, c) => new { ppc, c })
    .select(m => new { 
        prodid = m.ppc.p.id, // or m.ppc.pc.prodid
        catid = m.c.catid
        // other assignments
    });

if you need to, you can save the join into a local variable and reuse it later, however lacking other details to the contrary, i see no reason to introduce the local variable.

also, you could throw the select into the last lambda of the second join (again, provided there are no other operations that depend on the join results) which would give:

var categorizedproducts = product
    .join(productcategory, p => p.id, pc => pc.prodid, (p, pc) => new { p, pc })
    .join(category, ppc => ppc.pc.catid, c => c.id, (ppc, c) => new {
        prodid = ppc.p.id, // or ppc.pc.prodid
        catid = c.catid
        // other assignments
    });

...and making a last attempt to sell you on query syntax, this would look like this:

var categorizedproducts =
    from p in product
    join pc in productcategory on p.id equals pc.prodid
    join c in category on pc.catid equals c.id
    select new {
        prodid = p.id, // or pc.prodid
        catid = c.catid
        // other assignments
    };

your hands may be tied on whether query-syntax is available. i know some shops have such mandates - often based on the notion that query-syntax is somewhat more limited than dot-syntax. there are other reasons, like "why should i learn a second syntax if i can do everything and more in dot-syntax?" as this last part shows - there are details that query-syntax hides that can make it well worth embracing with the improvement to readability it brings: all those intermediate projections and identifiers you have to cook-up are happily not front-and-center-stage in the query-syntax version - they are background fluff. off my soap-box now - anyhow, thanks for the question. :)

score:0

var query = from a in d.tbl_usuarios
                    from b in d.tblcomidapreferidas
                    from c in d.tbllugarnacimientoes
                    select new
                    {
                        _nombre = a.nombre,
                        _comida = b.comidapreferida,
                        _lnacimiento = c.ciudad
                    };
        foreach (var i in query)
        {
            console.writeline($"{i._nombre } le gusta {i._comida} y naciĆ³ en {i._lnacimiento}");
        }

score:0

it has been a while but my answer may help someone:

if you already defined the relation properly you can use this:

        var res = query.products.select(m => new
        {
            productid = product.id,
            categoryid = m.productcategory.select(s => s.category.id).tolist(),
        }).tolist();

score:4

 public actionresult index()
    {
        list<customerorder_result> obj = new list<customerorder_result>();

       var  orderlist = (from a in db.ordermasters
                         join b in db.customers on a.customerid equals b.id
                         join c in db.customeraddresses on b.id equals c.customerid
                         where a.status == "pending"
                         select new
                         {
                             customername = b.customername,
                             phone = b.phone,
                             orderid = a.orderid,
                             orderdate = a.orderdate,
                             noofitems = a.noofitems,
                             order_amt = a.order_amt,
                             dis_amt = a.dis_amt,
                             net_amt = a.net_amt,
                             status=a.status,  
                             address = c.address,
                             city = c.city,
                             state = c.state,
                             pin = c.pin

                         }) ;
       foreach (var item in orderlist)
       {

           customerorder_result clr = new customerorder_result();
           clr.customername=item.customername;
           clr.phone = item.phone;
           clr.orderid = item.orderid;
           clr.orderdate = item.orderdate;
           clr.noofitems = item.noofitems;
           clr.order_amt = item.order_amt;
           clr.net_amt = item.net_amt;
           clr.address = item.address;
           clr.city = item.city;
           clr.state = item.state;
           clr.pin = item.pin;
           clr.status = item.status;

           obj.add(clr);



       }

score:7

take look at this sample code from my project

public static ilist<letter> getdepartmentletterslinq(int departmentid)
{
    ienumerable<letter> alldepartmentletters =
        from allletter in letterservice.getallletters()
        join alluser in userservice.getallusers() on allletter.employeeid equals alluser.id into usersgroup
        from user in usersgroup.defaultifempty()// here is the tricky part
        join alldepartment in departmentservice.getalldepartments() on user.departmentid equals alldepartment.id
        where alldepartment.id == departmentid
        select allletter;

    return alldepartmentletters.toarray();
}

in this code i joined 3 tables and i spited join condition from where clause

note: the services classes are just warped(encapsulate) the database operations

score:12

what you've seen is what you get - and it's exactly what you asked for, here:

(ppc, c) => new { productproductcategory = ppc, category = c}

that's a lambda expression returning an anonymous type with those two properties.

in your categorizedproducts, you just need to go via those properties:

categorizedproducts catproducts = query.select(
      m => new { 
             prodid = m.productproductcategory.product.id, 
             catid = m.category.catid, 
             // other assignments 
           });

Related Query

More Query from same tag