How to write a composable query in LINQ/SDX

Jun 18, 2013 at 9:27 PM
Hi,
I've downloaded your product for evaluation as a proof of concept for a project my company is building. If it works we'll move forward and purchase.

So far I really like your product and the goal of using LINQ to access our cubes is exactly what I want. However my understanding is that you cannot use explicit Joins nor can you use EF Navigation properties in LINQ/SDX, you need to use Implicit Joins only. From the examples and my limited testing, I can only figure out how to get implicit joins working using Query Syntax with multiple From clauses. I believe Multiple From clause in Query syntax would be equivalent to a SelectMany in extension method syntax, but I cant figure out how to get this working without explicitly stating the join.

One of the benefits of LINQ queries is that they are composable which allows you to build up a query at run time based on user input / conditional logic. Is there any way to compose up a query using extension method syntax in LINQ / SDX? It appears to me that I would have to know both the dimensions in the From clauses and the shape of the query results in the Select clause at Design time using Query syntax.

Any pointers or advice on how to solve this would be appreciated as one of the main requirements of our project is allowing the user to choose the dimensionality of the query via a GUI, not just the filter criteria.

Thanks in advance
Dan
Jun 21, 2013 at 1:39 AM
Edited Jun 29, 2013 at 5:33 AM
Hi Dan,

Please see -inline-:

dcarlson wrote:
Hi,
I've downloaded your product for evaluation as a proof of concept for a project my company is building. If it works we'll move forward and purchase.

So far I really like your product and the goal of using LINQ to access our cubes is exactly what I want. However my understanding is that you cannot use explicit Joins
-Correct.
nor can you use EF Navigation properties in LINQ/SDX, you need to use Implicit Joins only.
-Good news is that navigation properties (associations) are supported, so you can use both EF navigation properties and implicit joins. Line 5 in our SDX example shows a usage of a navigation property order.Product:
"&& order.Product.ProductName.ToUpper().StartsWith( "P" )"
From the examples and my limited testing, I can only figure out how to get implicit joins working using Query Syntax with multiple From clauses. I believe Multiple >From clause in Query syntax would be equivalent to a SelectMany in extension method syntax, but I cant figure out how to get this working without explicitly stating the join.
-We have updated our example to include a LINQ query in lambda (method) syntax, see: http://agiledesignllc.com/Products/SsasEFProvider/SDX-example-lambda
One of the benefits of LINQ queries is that they are composable which allows you to build up a query at run time based on user input / conditional logic. Is there any way to compose up a query using extension method syntax in LINQ / SDX? It appears to me that I would have to know both the dimensions in the From clauses and the shape of the query results in the Select clause at Design time using Query syntax.
-There is no difference in composability between comprehension (query) and lambda (method) syntax. LINQ comprehension syntax is just a syntactic sugar on top of lambda syntax. Both LINQ queries in comprehension syntax and lambda syntax are translated by a compiler into the same IL code based on extension methods.
You can compose and mix and match LINQ (sub)queries in both comprehension syntax and lambda syntax by putting query parts into parenthesis or/and assigning them into variables and parameters and composing those parts at run time together with predicates and other LINQ expressions. Just make sure those query parts return either Expression<T> or IQueryable rather than IEnumerable, if you would like to execute them on server side. (Server side for SDX means inside SSAS after LINQ being converted into MDX by SSAS Entity Framework Provider).
Any pointers or advice on how to solve this would be appreciated as one of the main requirements of our project is allowing the user to choose the dimensionality of the query via a GUI, not just the filter criteria.
-You should even be able to convert strings into LINQ by building ESQL strings or using Linq Dynamic Query Library, if needed.
Thanks in advance
Dan
Please let us know if you have any additional questions.
Jun 24, 2013 at 6:07 PM
Thank you very much for replying and updating your sample code. I'll work with this and let you know if I have any follow up questions.

Dan