Microsoft Analysis Services MDX Query with Linq

				
					using System.Dynamic;
using Microsoft.AnalysisServices.AdomdClient;
using Tuple = Microsoft.AnalysisServices.AdomdClient.Tuple;

namespace DataAccessLayer.Data;
public interface IDataAccessLayer
{
    public List<ExpandoObject> GetProductCategorySales(int? fiscalYear);
    public List<ExpandoObject> GetFiscalYears();
}
public class DataAccessLayer : IDataAccessLayer
{
    private static void AddProperty(ExpandoObject expando, string propertyName, object propertyValue)
    {
        // ExpandoObject supports IDictionary
        var expandoDict = expando as IDictionary<string, object>;
        if (expandoDict.ContainsKey(propertyName))
            expandoDict[propertyName] = propertyValue;
        else
            expandoDict.Add(propertyName, propertyValue);
    }
    private List<ExpandoObject> GetExpandoList(string mdx)
    {
        List<ExpandoObject> resultList = new();

        System.Text.StringBuilder result = new System.Text.StringBuilder();

        try
        {
            using (AdomdConnection connection = new AdomdConnection())
            {
                connection.ConnectionString = "Data Source=https://bi.syncfusion.com/olap/msmdpump.dll;Catalog=Adventure Works DW 2008 SE;";
                connection.Open();
                using ( AdomdCommand command = new AdomdCommand(mdx, connection))
                {
                    CellSet cs = command.ExecuteCellSet();

                    TupleCollection tuplesOnRows = cs.Axes[1].Set.Tuples;
                    TupleCollection tuplesOnColumns = cs.Axes[0].Set.Tuples;
                    for (int row = 0; row < tuplesOnRows.Count; row++)
                    {

                        dynamic d = new ExpandoObject();
                        for (int col = 0; col < tuplesOnColumns.Count; col++)
                        {
                            AddProperty(d, tuplesOnColumns[col].Members[0].Caption.ToString(), cs.Cells[col, row].Value);
                        }

                        resultList.Add(d);
                    }
                    connection.Close();
                }
            }
            return resultList;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex);
            return new List<ExpandoObject>();
        }
    }
    public List<ExpandoObject> GetProductCategorySales(int? fiscalYear)
    {
        var mdx = @"
            WITH 
                MEMBER Measures.ProductCategoryId AS 
                    Product.Category.CurrentMember.Properties(""Key"") 
                MEMBER Measures.ProductCategoryName AS
                    Product.Category.CurrentMember.Member_Caption
                MEMBER Measures.SalesChannelId AS 
                    [Sales Channel].CurrentMember.Properties(""Key"") 
                MEMBER Measures.SalesChannelName AS
                    [Sales Channel].CurrentMember.Member_Caption
                MEMBER Measures.SalesAmount AS
                    [Measures].[Sales Amount]
                SELECT
                {
                Measures.ProductCategoryName
                ,Measures.SalesChannelName
                ,Measures.ProductCategoryId
                ,Measures.SalesChannelId
                ,Measures.SalesAmount
                }
                ON 0
                ,
                [Product].[Category].CHILDREN * [Sales Channel].CHILDREN ON 1
            FROM[Adventure Works] 
        ";

        if (fiscalYear is not null) 
            mdx += $"WHERE  [Date].[Fiscal Year].&[{fiscalYear.ToString()}] ";

        return GetExpandoList(mdx);

    }
    public List<ExpandoObject> GetFiscalYears()
    {
        var mdx = @"
            WITH 
                MEMBER Measures.CalendarYearId AS 
                    [Date].[Fiscal Year].CurrentMember.Properties(""Key"") 
                MEMBER Measures.CalendarYearName AS
                    [Date].[Fiscal Year].CurrentMember.Member_Caption
            SELECT
                {
                  Measures.CalendarYearId
                 ,Measures.CalendarYearName
                }
                ON 0
                ,
                [Date].[Fiscal Year].CHILDREN ON 1
            FROM[Adventure Works];
        ";

        return GetExpandoList(mdx);
    }




}