score:209
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
});
Source: stackoverflow.com
Related Query
- How to perform Join between multiple tables in LINQ lambda
- Entity Framework: How to perform left join with EF and LINQ among multiple tables
- How to left join multiple tables with LINQ
- How to reuse a linq expression for 'Where' when using multiple source tables
- How to perform left join in linq having multiple table joins
- Lambda / LINQ find relations between multiple many-to-many relationship SQL tables
- How to use Linq or Lambda to join 1-to-many tables and project flattened results into an anonymous type
- How do I write Joins between multiple tables with Linq and ASP.Net Forms
- How to join multiple tables to one table with lambda expression?
- LINQ - How to join multiple tables and order by dynamically?
- How to join in LINQ by multiple fields (2 TABLES 1 PK to 2 FK ) c# linq
- Linq Lambda multiple tables ( 4 tables ) LEFT JOIN
- How can I make a JOIN query with LINQ between two tables (entities) with many-to-many relationship, while there is no joining table in EF?
- how to join multiple tables in linq using flag field
- How to differentiate between two similar fields in Linq Join tables
- How to join three tables and put it in a ViewModel using LINQ Lambda in ASP.Net MVC?
- how to equals between multiple table columns in join using Linq
- How can I use this Linq Query using it with a multiple parameter by Join tables
- How can I join multiple tables including a join-table (auto-generated by Entity Framework) using LINQ
- How to make use of join in linq having multiple tables and use orderby?
- How do you perform a left outer join with a where clause using linq lambda extension methods
- How to use Left join linq for multiple tables
- How to get data in Linq to EF join between four tables using count?
- How To Join Many Tables In Linq Lambda Expressions?
- How do you perform a left outer join using linq extension methods
- How to do joins in LINQ on multiple fields in single join
- How do you perform a CROSS JOIN with LINQ to SQL?
- LINQ to SQL multiple tables left outer join
- linq to sql join on multiple columns using lambda
- lambda expression join multiple tables with select and where clause
More Query from same tag
- Exception handling in Linq to SQL for customers without orders
- return message in linq
- LINQ: How to Append List of Elements Into Another List
- Unable to set datasource of datagridview from List
- Is this overusing extension methods?
- LINQ - group/sum multiple columns
- LINQ C# Sum and changing value to time (hh:mm:ss) in datatable
- How to remove a value from a specific property in a JSON string by matching value?
- I need to search strings ignoring case of the strings using Expression Trees
- Linq Select Statement slow when getting COUNT
- Why would you quote a LambdaExpression?
- reading a empty cell, gives object reference error
- MVC5 LINQ display only if latest record is a certain condition
- How to load all levels of object nested within a class, using Entity Framework 7 linq queries?
- Different results in Entity Framework than LINQ to SQL
- Dynamically build an Expression tree for sorting
- Encapsulating LINQ select statement
- How to Linq-ify this query?
- Linq How to Group By
- Find all objects in one list that have a property that matches a property in another list of objects
- Linq outer join on non-nullable field
- Linq to fill nested objects with pivot
- File Upload read to memory and use as text file - is there a better way?
- Lambda expression to create AND filter
- Selecting distinct attrbutes where count isn't same
- Linq: group and select from collection
- Linq - SelectMany Confusion
- Using LINQ on XML - join is very slow
- Linq to FileHelpers Class
- My SQL statement vs LinqPad SQL statement. Where is difference?