Note: This is an example how to use SDX implicit joins with lambda LINQ syntax via SelectMany(). In standard regular LINQ this query would result in a Cartesian product, but SDX joins data implicitly using cube metadata.
context.Customers
.SelectMany(c => context.OrderDetails, (customer, order) => new
{
customer,
order
})
.Where
(
r =>
(r.customer.Country == "Italy"
|| r.customer.Country.Contains("US"))
&& r.order.Product.ProductName.ToUpper().StartsWith("P")
&& r.order.Discount != 0
&& r.order.Quantity >= 100
).OrderByDescending(r => r.order.Discount)
.Select
(
r => new
{
r.customer.CustomerID,
r.customer.CompanyName,
r.order.Quantity, //Quantity is aggregated using implicit Sum() aggregation function
r.order.Discount, //Discount is aggregated using implicit Avg() aggregation function
//Sometimes you will specify explicit aggregation functions in MDX style
MaxDiscount =
Mdx.Max(r.order.Discount, "[Customers].[Customer ID].[Customer ID]"
/*granularity dimension(s)*/)
}
)
.Skip(2) //pagination
.Take(3)
.ToArray();