c# - .NET LINQ , List of Orders grouped by CustomerId across three tables? -

c# - .NET LINQ , List of Orders grouped by CustomerId across three tables? -

i have 3 tables in sql-database

orders (id, value, timestamp) customer(id, firstname, lastname) ordercustomer(id, customerid, ordersid)

i want accomplish list of results each object got customerid , related orders of customer.

i have seen illustration @ msdn.com, need, 2 tables:

dim customerlist = cust in customers grouping bring together ord in orders on cust.customerid equals ord.customerid customerorders = group, ordertotal = sum(ord.total) select cust.companyname, cust.customerid, customerorders, ordertotal

my code right gives me list customers , order, not grouped together. like:

customer 1 - order 1 customer 1 - order 5 customer 2 - order 2 customer 2 - order 3

customer 2 - order 36

dim results = customers in db.customers bring together ordercustomer in db.ordercustomer on customers.id equals ordercustomer.customerid grouping bring together orders in db_alt.orders on ordercustomer.orderid equals orders.id customerorders = group, ordertotal = sum(aufträge.order_value) select customers.firstname, customers.firstname, customerorders, ordertotal, customers.id

if foreign keys set up, linq sql or ef should see properties , can query this:

var result = db.customers.include("order") // ef

that should bring customers , each of them have collection of orders

or var result = db.customers.select(c=>new {customer = c, orders = c.orders}

c# sql vb.net linq