Was asked to put together a quick and dirty sample of doing a join in LINQ. . . .
Here are some setup classes that I am going to use:
1: public class MyDto
2: {
3: public int CustomerId { get; set; }
4: public string CustomerName { get; set; }
5: public string ItemId { get; set; }
6: public string ItemName { get; set; }
7: public float Amount { get; set; }
8: }
9:
10: public class SalesPrice
11: {
12: public int CustomerId { get; set; }
13: public string ItemId { get; set; }
14: public float Amount { get; set; }
15: }
16:
17: public class Customer
18: {
19: public int CustomerId { get; set; }
20: public string CustomerName { get; set; }
21: }
22:
23: public class Item
24: {
25: public string ItemId { get; set; }
26: public string ItemName { get; set; }
27: }
First I am going to fill the objects with some dummy data:
1: List<SalesPrice> prices = new List<SalesPrice>()
2: {
3: new SalesPrice() {CustomerId = 1, ItemId = "A", Amount = 5},
4: new SalesPrice() {CustomerId = 2, ItemId = "B", Amount = 10},
5: new SalesPrice() {CustomerId = 3, ItemId = "C", Amount = 15},
6: new SalesPrice() {CustomerId = 4, ItemId = "D", Amount = 20},
7: };
8:
9: List<Customer> customers = new List<Customer>()
10: {
11: new Customer() {CustomerId = 1, CustomerName = "Sam"},
12: new Customer() {CustomerId = 2, CustomerName = "Sally"},
13: new Customer() {CustomerId = 3, CustomerName = "Joe"},
14: new Customer() {CustomerId = 5, CustomerName = "Bill"}
15: };
16:
17: List<Item> items = new List<Item>()
18: {
19: new Item() {ItemId = "A", ItemName = "Fork"},
20: new Item() {ItemId = "B", ItemName = "Knife"},
21: new Item() {ItemId = "C", ItemName = "Teddy"},
22: new Item() {ItemId = "F", ItemName = "Ball"}
23: };
Now lets do a regular join:
1: var results1 = from p in prices
2: join c in customers
3: on p.CustomerId equals c.CustomerId
4: join i in items
5: on p.ItemId equals i.ItemId
6: select new MyDto()
7: {
8: CustomerId = p.CustomerId,
9: ItemId = p.ItemId,
10: Amount = p.Amount,
11: CustomerName = c.CustomerName,
12: ItemName = i.ItemName
13: };
Note that we are missing one of our SalesPrices, depending on your business rules this might be bad, very bad. . . . . how about an outer join
1: var results = from p in prices
2: join c in customers
3: on p.CustomerId equals c.CustomerId into priceCustomer
4: from pc in priceCustomer.DefaultIfEmpty()
5: join i in items
6: on p.ItemId equals i.ItemId into priceItems
7: from pi in priceItems.DefaultIfEmpty()
8: select new MyDto()
9: {
10: CustomerId = p.CustomerId,
11: ItemId = p.ItemId,
12: Amount = p.Amount,
13: CustomerName = pc == null ? string.Empty : pc.CustomerName,
14: ItemName = pi == null ? string.Empty : pi.ItemName
15: };
Great now we got everything! Hope this helps you, perhaps more important it is going to help me when I forget next week. 