SDX LINQ Example Using Lambda (Method) Syntax

  • 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();
    						
    					

    See also: SDX LINQ Example Using Comprehension Syntax