Linq Tips
This a sample for some linq tips like => inner join, left join, projection one to many , Aggregation using grouping, ... .
we have some classes for simulating this.
public class Product
{
public int ProductID { get; set; }
public string Name { get; set; }
public string Color { get; set; }
public decimal StandardCost { get; set; }
public decimal ListPrice { get; set; }
public string Size { get; set; }
// Calculated Properties
public int? NameLength { get; set; }
public decimal? TotalSales { get; set; }
#region ToString Override
public override string ToString()
{
StringBuilder sb = new StringBuilder(1024);
sb.Append(Name);
sb.AppendLine($" ID: {ProductID}");
sb.Append($" Color: {Color}");
sb.AppendLine($" Size: {(Size ?? "n/a")}");
sb.Append($" Cost: {StandardCost:c}");
sb.Append($" Price: {ListPrice:c}");
if (NameLength.HasValue)
{
sb.AppendLine($" Name Length: {NameLength}");
}
if (TotalSales.HasValue)
{
sb.AppendLine($" Total Sales: {TotalSales:c}");
}
return sb.ToString();
}
#endregion
}
////////////
public class SalesOrderDetail
{
public int SalesOrderID { get; set; }
public short OrderQty { get; set; }
public int ProductID { get; set; }
public decimal UnitPrice { get; set; }
public decimal LineTotal { get; set; }
#region ToString Override
public override string ToString()
{
StringBuilder sb = new StringBuilder(1024);
sb.AppendLine($"Order ID: {SalesOrderID}");
sb.Append($" Product ID: {ProductID}");
sb.AppendLine($" Qty: {OrderQty}");
sb.Append($" Unit Price: {UnitPrice:c}");
sb.AppendLine($" Total: {LineTotal:c}");
return sb.ToString();
}
#endregion
}
/////////
public class ProductSales
{
public Product Product { get; set; }
public IEnumerable<SalesOrderDetail> Sales { get; set; }
}
////////
public class ProductStats
{
public ProductStats()
{
Max = Decimal.MinValue;
Min = Decimal.MaxValue;
TotalProducts = 0;
Total = 0;
}
public int TotalProducts { get; set; }
public decimal Max { get; set; }
public decimal Min { get; set; }
public decimal Total { get; set; }
public decimal Average { get; set; }
public ProductStats Accumulate(Product prod)
{
// Increment total number of products
TotalProducts += 1;
// Add to total list price
Total += prod.ListPrice;
// Calculate Max and Min
Max = Math.Max(Max, prod.ListPrice);
Min = Math.Min(Min, prod.ListPrice);
return this;
}
public ProductStats ComputeAverage()
{
Average = Total / TotalProducts;
return this;
}
}
////////
public class SaleProducts
{
public int SalesOrderID { get; set; }
public List<Product> Products { get; set; }
}
this class for view Model
public partial class SalesOrderDetailRepository
{
#region GetAll Method
public static List<SalesOrderDetail> GetAll()
{
return new List<SalesOrderDetail>
{
new SalesOrderDetail
{
SalesOrderID = 71774,
OrderQty = 1,
ProductID = 680,
UnitPrice = 356.90M,
LineTotal = 356.90M
},
new SalesOrderDetail
{
SalesOrderID = 71774,
OrderQty = 1,
ProductID = 712,
UnitPrice = 356.90M,
LineTotal = 356.90M
},
new SalesOrderDetail
{
SalesOrderID = 71776,
OrderQty = 1,
ProductID = 680,
UnitPrice = 356.90M,
LineTotal = 356.90M
},
new SalesOrderDetail
{
SalesOrderID = 71780,
OrderQty = 4,
ProductID = 707,
UnitPrice = 218.45M,
LineTotal = 873.80M
},
new SalesOrderDetail
{
SalesOrderID = 71780,
OrderQty = 2,
ProductID = 708,
UnitPrice = 461.69M,
LineTotal = 923.38M
},
new SalesOrderDetail
{
SalesOrderID = 71780,
OrderQty = 6,
ProductID = 709,
UnitPrice = 113.00M,
LineTotal = 678.00M
},
new SalesOrderDetail
{
SalesOrderID = 71781,
OrderQty = 2,
ProductID = 709,
UnitPrice = 818.70M,
LineTotal = 1637.40M
},
new SalesOrderDetail
{
SalesOrderID = 71781,
OrderQty = 1,
ProductID = 710,
UnitPrice = 323.99M,
LineTotal = 323.99M
},
new SalesOrderDetail
{
SalesOrderID = 71781,
OrderQty = 1,
ProductID = 711,
UnitPrice = 149.87M,
LineTotal = 149.87M
},
new SalesOrderDetail
{
SalesOrderID = 71782,
OrderQty = 1,
ProductID = 711,
UnitPrice = 809.76M,
LineTotal = 809.76M
},
new SalesOrderDetail
{
SalesOrderID = 71782,
OrderQty = 4,
ProductID = 712,
UnitPrice = 1376.99M,
LineTotal = 5507.96M
},
new SalesOrderDetail
{
SalesOrderID = 71783,
OrderQty = 2,
ProductID = 713,
UnitPrice = 158.43M,
LineTotal = 316.86M,
},
new SalesOrderDetail
{
SalesOrderID = 71784,
OrderQty = 4,
ProductID = 714,
UnitPrice = 1391.99M,
LineTotal = 5567.96M
},
new SalesOrderDetail
{
SalesOrderID = 71784,
OrderQty = 1,
ProductID = 715,
UnitPrice = 48.59M,
LineTotal = 48.59M,
},
new SalesOrderDetail
{
SalesOrderID = 71784,
OrderQty = 6,
ProductID = 716,
UnitPrice = 41.99M,
LineTotal = 251.94M
},
new SalesOrderDetail
{
SalesOrderID = 71784,
OrderQty = 1,
ProductID = 717,
UnitPrice = 113.00M,
LineTotal = 113.00M
},
new SalesOrderDetail
{
SalesOrderID = 71785,
OrderQty = 2,
ProductID = 718,
UnitPrice = 323.99M,
LineTotal = 647.98M
},
new SalesOrderDetail
{
SalesOrderID = 71785,
OrderQty = 3,
ProductID = 719,
UnitPrice = 323.99M,
LineTotal = 971.97M
},
new SalesOrderDetail
{
SalesOrderID = 71786,
OrderQty = 1,
ProductID = 720,
UnitPrice = 323.99M,
LineTotal = 323.99M
},
new SalesOrderDetail
{
SalesOrderID = 71786,
OrderQty = 2,
ProductID = 721,
UnitPrice = 323.99M,
LineTotal = 647.98M
},
new SalesOrderDetail
{
SalesOrderID = 71786,
OrderQty = 2,
ProductID = 722,
UnitPrice = 113.00M,
LineTotal = 226.00M
},
new SalesOrderDetail
{
SalesOrderID = 71787,
OrderQty = 3,
ProductID = 723,
UnitPrice = 113.00M,
LineTotal = 339.00M,
},
new SalesOrderDetail
{
SalesOrderID = 71788,
OrderQty = 3,
ProductID = 724,
UnitPrice = 113.00M,
LineTotal = 339.00M
},
new SalesOrderDetail
{
SalesOrderID = 71788,
OrderQty = 2,
ProductID = 725,
UnitPrice = 461.69M,
LineTotal = 923.38M,
},
new SalesOrderDetail
{
SalesOrderID = 71788,
OrderQty = 3,
ProductID = 726,
UnitPrice = 461.69M,
LineTotal = 1385.082000M
},
new SalesOrderDetail
{
SalesOrderID = 99988,
OrderQty = 3,
ProductID = 9999,
UnitPrice = 461.69M,
LineTotal = 1385.07M
}
};
}
#endregion
}
///////////
public partial class ProductRepository
{
#region GetAll Method
public static List<Product> GetAll()
{
return new List<Product>
{
new Product {
Name = "Sport-100 Helmet",
Color = "Red"
},
new Product {
Name = "Road Frame",
Color = "Black",
},
new Product {
Name = "Long Sleeve Logo Jersey",
Color = "Red"
},
new Product {
Name = "Mountain Frame",
Color = "Silver"
}
};
}
#endregion
}
public partial class ProductRepository
{
#region GetAll Method
public static List<Product> GetAll()
{
return new List<Product>
{
new Product {
Name = "Sport-100 Helmet",
Color = "Red"
},
new Product {
Name = "Road Frame",
Color = "Black",
},
new Product {
Name = "Long Sleeve Logo Jersey",
Color = "Red"
},
new Product {
Name = "Mountain Frame",
Color = "Silver"
}
};
}
#endregion
}
public class ViewModelClass{
#region Properties
public bool UseQuerySyntax { get; set; } = true;
public List<Product> Products { get; set; }
public List<SalesOrderDetail> Sales { get; set; }
public string ResultText { get; set; }
#endregion
#region Constructor
public ViewModelClass()
{
// Load all Product Data, simulation to get all data
Products = ProductRepository.GetAll();
// Load all Sales Data
Sales = SalesOrderDetailRepository.GetAll();
}
#endregion
}
inner join
#region InnerJoin
/// <summary>
/// Join a Sales Order Detail collection with Products into anonymous class
/// NOTE: This is an equijoin or an inner join
/// </summary>
public void InnerJoin()
{
StringBuilder sb = new StringBuilder(2048);
int count = 0;
if (UseQuerySyntax)
{
// Query syntax
var query = (from prod in Products
join sale in Sales
on prod.ProductID equals sale.ProductID
select new
{
prod.ProductID,
prod.Name,
prod.Color,
prod.StandardCost,
prod.ListPrice,
prod.Size,
sale.SalesOrderID,
sale.OrderQty,
sale.UnitPrice,
sale.LineTotal
});
foreach (var item in query)
{
count++;
sb.AppendLine($"Sales Order: {item.SalesOrderID}");
sb.AppendLine($" Product ID: {item.ProductID}");
sb.AppendLine($" Product Name: {item.Name}");
sb.AppendLine($" Size: {item.Size}");
sb.AppendLine($" Order Qty: {item.OrderQty}");
sb.AppendLine($" Total: {item.LineTotal:c}");
}
}
else
{
// Method syntax
var query = Products.Join(Sales, prod => prod.ProductID,
sale => sale.ProductID,
(prod, sale) => new
{
prod.ProductID,
prod.Name,
prod.Color,
prod.StandardCost,
prod.ListPrice,
prod.Size,
sale.SalesOrderID,
sale.OrderQty,
sale.UnitPrice,
sale.LineTotal
});
foreach (var item in query)
{
count++;
sb.AppendLine($"Sales Order: {item.SalesOrderID}");
sb.AppendLine($" Product ID: {item.ProductID}");
sb.AppendLine($" Product Name: {item.Name}");
sb.AppendLine($" Size: {item.Size}");
sb.AppendLine($" Order Qty: {item.OrderQty}");
sb.AppendLine($" Total: {item.LineTotal:c}");
}
}
ResultText = sb.ToString() + Environment.NewLine + "Total Sales: " + count.ToString();
}
#endregion
Iner join with 2 fields
#region InnerJoinTwoFields
/// <summary>
/// Join a Sales Order Detail collection with Products using two fields
/// </summary>
public void InnerJoinTwoFields()
{
short qty = 6;
int count = 0;
StringBuilder sb = new StringBuilder(2048);
if (UseQuerySyntax)
{
// Query syntax
var query = (from prod in Products
join sale in Sales on
new { prod.ProductID, Qty = qty }
equals
new { sale.ProductID, Qty = sale.OrderQty }
select new
{
prod.ProductID,
prod.Name,
prod.Color,
prod.StandardCost,
prod.ListPrice,
prod.Size,
sale.SalesOrderID,
sale.OrderQty,
sale.UnitPrice,
sale.LineTotal
});
foreach (var item in query)
{
count++;
sb.AppendLine($"Sales Order: {item.SalesOrderID}");
sb.AppendLine($" Product ID: {item.ProductID}");
sb.AppendLine($" Product Name: {item.Name}");
sb.AppendLine($" Size: {item.Size}");
sb.AppendLine($" Order Qty: {item.OrderQty}");
sb.AppendLine($" Total: {item.LineTotal:c}");
}
}
else
{
// Method syntax
var query = Products.Join(Sales,
prod => new { prod.ProductID, Qty = qty },
sale => new { sale.ProductID, Qty = sale.OrderQty },
(prod, sale) => new
{
prod.ProductID,
prod.Name,
prod.Color,
prod.StandardCost,
prod.ListPrice,
prod.Size,
sale.SalesOrderID,
sale.OrderQty,
sale.UnitPrice,
sale.LineTotal
});
foreach (var item in query)
{
count++;
sb.AppendLine($"Sales Order: {item.SalesOrderID}");
sb.AppendLine($" Product ID: {item.ProductID}");
sb.AppendLine($" Product Name: {item.Name}");
sb.AppendLine($" Size: {item.Size}");
sb.AppendLine($" Order Qty: {item.OrderQty}");
sb.AppendLine($" Total: {item.LineTotal:c}");
}
}
ResultText = sb.ToString() + Environment.NewLine + "Total Sales: " + count.ToString();
}
#endregion
Select one to many
#region GroupJoin
/// <summary>
/// Use GroupJoin to create a new object with a Sales collection for each Product
/// This is like a combination of an inner join and left outer join
/// The 'into' keyword allows you to put the sales into a 'sales' variable
/// that can be used to retrieve all sales for a specific product
/// </summary>
public void GroupJoin()
{
StringBuilder sb = new StringBuilder(2048);
IEnumerable<ProductSales> grouped;
if (UseQuerySyntax)
{
// Query syntax is simply a 'join...into'
grouped = (from prod in Products
join sale in Sales
on prod.ProductID equals sale.ProductID
into sales
select new ProductSales
{
Product = prod,
Sales = sales
});
}
else
{
// Method syntax uses 'GroupJoin()'
grouped =
Products.GroupJoin(Sales,
prod => prod.ProductID,
sale => sale.ProductID,
(prod, sales) => new ProductSales
{
Product = prod,
Sales = sales.ToList()
});
}
// Loop through each product
foreach (var ps in grouped)
{
sb.AppendLine($"Product: {ps.Product}");
// Loop through the sales for this product
if (ps.Sales.Count() > 0)
{
sb.AppendLine(" ** Sales **");
foreach (var sale in ps.Sales)
{
sb.Append($" SalesOrderID: {sale.SalesOrderID}");
sb.Append($" Qty: {sale.OrderQty}");
sb.AppendLine($" Total: {sale.LineTotal}");
}
}
else
{
sb.AppendLine(" ** NO Sales for Product **");
}
sb.AppendLine("");
}
ResultText = sb.ToString();
}
#endregion
Left Outer Join
#region LeftOuterJoin
/// <summary>
/// Perform a left join between Products and Sales using DefaultIfEmpty() and SelectMany()
/// </summary>
public void LeftOuterJoin()
{
StringBuilder sb = new StringBuilder(2048);
int count = 0;
if (UseQuerySyntax)
{
// Query syntax
var query = (from prod in Products
join sale in Sales
on prod.ProductID equals sale.ProductID
into sales
from sale in sales.DefaultIfEmpty()
select new
{
prod.ProductID,
prod.Name,
prod.Color,
prod.StandardCost,
prod.ListPrice,
prod.Size,
sale?.SalesOrderID,
sale?.OrderQty,
sale?.UnitPrice,
sale?.LineTotal
}).OrderBy(ps => ps.Name);
foreach (var item in query)
{
count++;
sb.AppendLine($"Product Name: {item.Name} ({item.ProductID})");
sb.AppendLine($" Order ID: {item.SalesOrderID}");
sb.AppendLine($" Size: {item.Size}");
sb.AppendLine($" Order Qty: {item.OrderQty}");
sb.AppendLine($" Total: {item.LineTotal:c}");
}
}
else
{
// Method syntax
var query =
Products.SelectMany(
sale =>
Sales.Where(s => sale.ProductID == s.ProductID).
DefaultIfEmpty(),
(prod, sale) => new
{
prod.ProductID,
prod.Name,
prod.Color,
prod.StandardCost,
prod.ListPrice,
prod.Size,
sale?.SalesOrderID,
sale?.OrderQty,
sale?.UnitPrice,
sale?.LineTotal
}).OrderBy(ps => ps.Name);
foreach (var item in query)
{
count++;
sb.AppendLine($"Product Name: {item.Name} ({item.ProductID})");
sb.AppendLine($" Order ID: {item.SalesOrderID}");
sb.AppendLine($" Size: {item.Size}");
sb.AppendLine($" Order Qty: {item.OrderQty}");
sb.AppendLine($" Total: {item.LineTotal:c}");
}
}
ResultText = sb.ToString() + Environment.NewLine + "Total Sales: " + count.ToString();
}
#endregion
Aggregation using Grouping
#region AggregateUsingGroupingMoreEfficient
/// <summary>
/// Group products by Size property and calculate min/max/average prices.
/// Using an accumulator class is more efficient because we don't loop
/// through once each for Min, Max, Average as in the previous sample.
/// </summary>
public void AggregateUsingGroupingMoreEfficient()
{
StringBuilder sb = new StringBuilder(2048);
// Method syntax only
var stats =
Products.GroupBy(sale => sale.Size)
.Where(sizeGroup => sizeGroup.Count() > 0)
.Select(sizeGroup =>
{
var results = sizeGroup.Aggregate(new ProductStats(),
(acc, prod) => acc.Accumulate(prod),
acc => acc.ComputeAverage());
return new
{
Size = sizeGroup.Key,
results.TotalProducts,
results.Min,
results.Max,
results.Average
};
})
.OrderBy(result => result.Size)
.Select(result => result);
// Loop through each product statistic
foreach (var stat in stats) {
sb.AppendLine($"Size: {stat.Size} Count: {stat.TotalProducts}");
sb.AppendLine($" Min: {stat.Min:c}");
sb.AppendLine($" Max: {stat.Max:c}");
sb.AppendLine($" Average: {stat.Average:c}");
}
ResultText = sb.ToString();
}
#endregion
Grouped sub-query
#region GroupedSubquery
/// <summary>
/// Group Sales by SalesOrderID, add Products into new Sales Order object using a subquery
/// </summary>
public void GroupedSubquery()
{
StringBuilder sb = new StringBuilder(2048);
IEnumerable<SaleProducts> salesGroup;
// Get all products for a sales order id
if (UseQuerySyntax) {
// Query syntax
salesGroup = (from sale in Sales
group sale by sale.SalesOrderID into sales
select new SaleProducts
{
SalesOrderID = sales.Key,
Products = (from prod in Products
join sale in Sales on prod.ProductID equals sale.ProductID
where sale.SalesOrderID == sales.Key
select prod).ToList()
});
}
else {
// Method syntax
salesGroup =
Sales.GroupBy(sale => sale.SalesOrderID)
.Select(sales => new SaleProducts
{
SalesOrderID = sales.Key,
Products = Products.Join(sales,
prod => prod.ProductID,
sale => sale.ProductID,
(prod, sale) => prod).ToList()
});
}
// Loop through each sales order
foreach (var sale in salesGroup) {
sb.AppendLine($"Sales ID: {sale.SalesOrderID}");
if (sale.Products.Count > 0) {
// Loop through the products in each sale
foreach (var prod in sale.Products) {
sb.Append($" ProductID: {prod.ProductID}");
sb.Append($" Name: {prod.Name}");
sb.AppendLine($" Color: {prod.Color}");
}
}
else {
sb.AppendLine(" Product ID not found for this sale.");
}
}
ResultText = sb.ToString();
}
#endregion
Reference: Linq
for more: moreLinq