Installation

Best way is to use https://www.nuget.org/packages/LinqToDax/

Using the project template

Download and install in VS 2013 the project template .zip from the downloads page.
The template includes a Context.csdl file. Change the contents of the file for your needs. You can generate the csdl of a tabular databse with the following XMLA command replacing catalog name with your database name:
<Discover xmlns='urn:schemas-microsoft-com:xml-analysis'>
	<RequestType>DISCOVER_CSDL_METADATA</RequestType>
	<Restrictions>
		<RestrictionList>
			<CATALOG_NAME>AdventureWorks Tabular Model SQL 2012</CATALOG_NAME>
		</RestrictionList>
	</Restrictions>
	<Properties>
		<PropertyList>
			<FORMAT>Tabular</FORMAT>
		</PropertyList>
	</Properties>
</Discover>

You may want to customize the T4 template Contex.tt itself to better suite your needs.

You can also download the example adventureworks context and binary dlls from the Downloads page.

Simple Queries

The simplest query

        [Test]
        public void SimpleQuery()
        {

            var titles =
                    from customer in _db.CustomerSet
                    select customer.Title;
            var res = titles.ToList();
            res.Should().Contain("Mrs.");

        }

Translates to DAX as SUMMARIZE:
EVALUATE
SUMMARIZE ( 'Customer', 'Customer'[Title] )
and in turn it is optimized to:
EVALUATE
VALUES('Customer'[Title])


If you add projection to select it will collect the measures and columns referred and translate it still to a simple SUMMARIZE query with ADDCOLUMNS that adds the measures, and then create the object:


        [Test]
        public void SimpleQuery_withProjection()
        {

            var titles =
                from customer in _db.CustomerSet
                where customer.TotalCarsOwned > 0 && customer.Gender == "F"
                select new
                    {
                        Orders = customer.Internet_Distinct_Count_Sales_Order(),
                        Name = customer.FirstName + " " + customer.LastName,
                        Cars = customer.TotalCarsOwned,
                        Sales = customer.Internet_Total_Sales()
                    };

            var res = titles.ToList();
            res.Select(x => x.Cars).Should().Contain(3);
        }

This translates to

EVALUATE
CALCULATETABLE (
    ADDCOLUMNS (
        SUMMARIZE (
            'Customer',
            'Customer'[First Name],
            'Customer'[Last Name],
            'Customer'[Total Cars Owned]
        ),
        "Internet Distinct Count Sales Order", CALCULATE ( [Internet Distinct Count Sales Order] ),
        "Internet Total Sales", CALCULATE ( [Internet Total Sales] )
    ),
    'Customer'[Total Cars Owned] > 0,
    'Customer'[Gender] = "F"
)


Where is translated to CALCULATETABLE and FILTER depending on the conditions given, for simple column value comparisions it translates to CALCULATETABLE, whenever there is a measure involved it changes to FILTER:


        [Test]
        public void SimpleQuery_WithWhere()
        {

            var titles =
                from customer in _db.CustomerSet
                where customer.Gender == "M" && customer.Title != ""
                select customer.Title;
            var res = titles.ToList();
            res.Should().NotContain("Mrs.");
        }

This translates to:
         EVALUATE
         CALCULATETABLE (
            VALUES( 'Customer'[Title] ),
              'Customer'[Gender] = "M",
             'Customer'[Title] <> ""
         )
While this query:



        [Test]
        public void SimpleQuery_FilterOnMeasure()
        {

            var query =
                from customer in _db.CustomerSet
                from sales in _db.InternetSalesSet
                where sales.Internet_Total_Sales() > 0
                select new
                {
                    Profit = customer.Total_Gross_Profit(),
                    Name = customer.LastName
                };
            var result = query.Take(10).ToList().Select(x => x.Name);
            result.Should().Contain("Xu");
        }


Would translate to:


EVALUATE
TOPN (
    10,
    FILTER (
        ADDCOLUMNS (
            VALUES ( 'Customer'[Last Name] ),
            "Total Gross Profit", CALCULATE ( [Total Gross Profit] )
        ),
        [Internet Total Sales] > 0
    ),
    [Total Gross Profit],
    0
)

You can have multiple Where() calls in a query and the decision on which function to use is made independently on each. for Example the following query will have FILTER and CALCULATETABLE as well for the two Where() calls:


     [Test]
        public void FilterAndCalculateTable()
        {
            var q =
                (from sales in _db.InternetSalesSet
                 where sales.RelatedCustomer.OwnsHouse
                 select new
                 {
                     Name = sales.RelatedCustomer.LastName,
                     sales.RelatedCustomer.RelatedGeography.City,
                     Sales = (
                         from x in _db.InternetSalesSet
                         where x.RelatedCustomer.RelatedGeography.City == "London"
                         select
                         new
                         {
                             x.RelatedCustomer.CustomerId,
                             v = x.SalesAmount.Sum()
                         }).Take(10).Sumx(x => x.v)
                 }
                    ).Where(x => x.Sales != null);
            var result = q.ToList();
            result.Should().NotBeNull();
        }


And it is translated to DAX as:
  DEFINE
    MEASURE 'Internet Sales'[Sum2] =
        CALCULATE (
            SUMX (
                TOPN (
                    10,
                    CALCULATETABLE (
                        ADDCOLUMNS (
                            VALUES ( 'Customer'[Customer Id] ),
                            "SumOfSalesAmount2", CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ) )
                        ),
                        'Geography'[City] = "London"
                    ),
                    [SumOfSalesAmount2],
                    0
                ),
                [SumOfSalesAmount2]
            )
        )
EVALUATE
FILTER (
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE (
                'Internet Sales',
                'Customer'[Last Name],
                'Geography'[City]
            ),
            "Sum2", 'Internet Sales'[Sum2]
        ),
        'Customer'[Owns House]
    ),
    [Sum2] <> BLANK ()
)

In the above example you can also see that measures not defined in the schema, and not mapped via TabularMeasureMapping attribute, that is those that are defined within the query are lifted to the DEFINE section.

The query translation tries to break the where condition so you can write one where condition that breaks down to separate FILTER and CALCULATETABLE functions in DAX:

The following query is an example:

   [Test]
        public void FilterSeparationTest()
        {
            var query =
                from sales in _db.InternetSalesSet
                where 
                (sales.RelatedCurrency.CurrencyCode == "USD" || sales.RelatedCustomer.LastName == "Xu") &&
                (sales.RelatedCustomer.LastName == "Yang" ||  "Xu" == sales.RelatedCustomer.LastName ) && 
                sales.InternetTotalSales() > 0
                select new
                {
                    sales.RelatedCurrency.CurrencyCode,
                    sales.RelatedCustomer.LastName
                };
            var result = query.ToList();
            result.Should().Contain(new { CurrencyCode = "USD", LastName = "Yang" });
        }


The or condition that refers to the same columns can go to CALCULATETABLE, but the other with different columns or the measure reference should go to FILTER function so it is translated as:
EVALUATE
FILTER (
    CALCULATETABLE (
        SUMMARIZE (
            'Internet Sales',
            'Currency'[Currency Code],
            'Customer'[Last Name]
        ),
        'Customer'[Last Name] = "Yang"
            || "Xu" = 'Customer'[Last Name]
    ),
    'Currency'[Currency Code] = "USD"
        || 'Customer'[Last Name] = "Xu"
        && [Internet Total Sales] > 0
)

In DAX you may use several tables without explicitly using join, whenever there is a relationship in the model. So We chose that SelectMany would just discard all from clauses but the last, this enables us to have references to tables. For example the following

       [Test]
        public void SimpleQuery_OverMoreTables()
        {

            const int custno = 20929;
            var q =
                from geo in _db.GeographySet         // these tables are used 
                from customer in _db.CustomerSet     // for referencing attributes
                from sales in _db.InternetSalesSet // the last table listed gets into the summarize
                where sales.RelatedCustomer.CustomerKey == custno
                select new
                {
                    Name = customer.LastName + " " + customer.FirstName,

                    City = new
                    {
                        CityName = geo.City,
                        Country = geo.CountryRegionName,
                    },
                    Discount = sales.DiscountAmount,
                    Date = sales.OrderDate,
                    Sales = sales.Internet_Total_Sales()
                };
            var res = q.ToList();

            res.Select(x => x.City.CityName).Should().Contain("York");
        }


translates to:
EVALUATE
CALCULATETABLE (
    ADDCOLUMNS (
        SUMMARIZE (
            'Internet Sales',
            'Customer'[Last Name],
            'Customer'[First Name],
            'Geography'[City],
            'Geography'[Country Region Name],
            'Internet Sales'[Discount Amount],
            'Internet Sales'[Order Date]
        ),
        "Internet Total Sales", CALCULATE ( [Internet Total Sales] )
    ),
    'Customer'[CustomerKey] = 20929
)


Take translates to Topn with the following logic, if there is a measure in the query the first measure is taken as the top ordering column, if there are no measures than the first column is chosen. We plan to have a Topn() extension so we can support more complex TOPN functions.


        [Test]
        public void SimpleQuery_WithTopnImplicitOrdering()
        {

            var query =
                (

                    from sales in _db.InternetSalesSet
                    where sales.RelatedCustomer.RelatedGeography.CountryRegionCode == "GB"
                    select new
                    {
                        Person = new
                            {
                                Full = sales.RelatedCustomer.FirstName + " " + sales.RelatedCustomer.LastName,
                                First = sales.RelatedCustomer.FirstName,
                                Last = sales.RelatedCustomer.LastName,
                                sales.RelatedCustomer.RelatedGeography.City
                            },
                        Data = new
                            {
                                Sales = sales.Internet_Total_Sales(),
                                Orders = sales.Internet_Distinct_Count_Sales_Order()
                            }
                    }
                ).Take(10);

            var res = query.ToList();
            res.Should().HaveCount(10);
        }


EVALUATE
TOPN (
    10,
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE (
                'Internet Sales',
                'Customer'[First Name],
                'Customer'[Last Name],
                'Geography'[City]
            ),
            "Internet Total Sales", CALCULATE ( [Internet Total Sales] ),
            "Internet Distinct Count Sales Order", CALCULATE ( [Internet Distinct Count Sales Order] )
        ),
        'Geography'[Country Region Code] = "GB"
    ),
    [Internet Total Sales],
    0
)

TabularTable operations, Queries with table parameters

We implemented an extension method for CALCULATETABLE when the filter is another table, its usage is illustrated below:

     [Test]
        public void CalculateTable()
        {

            const string xu = "Xu";
            var q1 =
                from customer in _db.CustomerSet
                where customer.LastName == xu && customer.FirstName == "Tony"
                select customer.CustomerId;

            var q2 =
                from x in _db.InternetSalesSet
                select new
                        {
                            x.RelatedCustomer.CustomerId,
                            Sales = x.InternetTotalSales(),
                            Orders = x.InternetOrderLinesCount()
                        };

            var result = q2.CalculateTable(q1).ToList().Select(r => Math.Round(r.Sales, 0));

            result.Should().Contain(2722);

        }

and it translates to:
EVALUATE
CALCULATETABLE (
    ADDCOLUMNS (
        VALUES ( 'Customer'[Customer Id] ),
        "Internet Total Sales", CALCULATE ( [Internet Total Sales] ),
        "Internet Order Lines Count", CALCULATE ( [Internet Order Lines Count] )
    ),
    CALCULATETABLE (
        VALUES ( 'Customer'[Customer Id] ),
        'Customer'[Last Name] = "Xu",
        'Customer'[First Name] = "Tony"
    )
)

We also have a simple GENERATE(table1, table2) function implementation:


   [Test]
        public void GenerateTest()
        {
            var q =
                from c in _db.SalesTerritorySet
                select new { c.SalesTerritoryGroup };

            var q2 =
                from sales in _db.ProductCategorySet
                select new
                {
                    Cat = sales.ProductCategoryName,
                    sum = (from s in _db.ResellerSalesSet select new { s.SalesAmount }).Sumx(x => x.SalesAmount)
                };
            var result = q.Generate(q2, (x, y) => new { x.SalesTerritoryGroup, y.Cat, y.sum }).Take(3);
            result.ToList().Should().NotBeNull();

        }

That translates to :
DEFINE
    MEASURE 'Reseller Sales'[Sum1] =
        CALCULATE (
            SUMX (
                VALUES ( 'Reseller Sales'[Sales Amount] ),
                'Reseller Sales'[Sales Amount]
            )
        )
EVALUATE
TOPN (
    3,
    GENERATE (
        VALUES ( 'Sales Territory'[Sales Territory Group] ),
        ADDCOLUMNS (
            SUMMARIZE (
                'Product Category',
                'Product Category'[Product Category Name]
            ),
            "Sum1", 'Reseller Sales'[Sum1]
        )
    ),
    [Sum1],
    0
)

Aggregations

Simple SUM, MAX, MIN and AVERAGE functions have they corresponding extension methods. They come in two version, one without parameter and one that expects a Boolean expression, Boolean value as parameter, that is put into a CALCULATE function. For Example


      [Test]
        public void SumTest()
        {
            string xu = "Xu";

            var q =
                from sales in _db.InternetSalesSet
                //this ternary operator is eliminated the test can be evaluated before the query
                //this form can be used to add conditional filters to a query
                where ((xu == null) ? true : sales.RelatedCustomer.LastName == xu)
                && sales.InternetTotalSales() > 0
                select new
                {
                    Name = sales.RelatedCustomer.FirstName + " " + sales.RelatedCustomer.LastName,
                    sales.RelatedCustomer.RelatedGeography.City,
                    Sum = sales.SalesAmount.Sum()
                };
            var result = q.Take(10).ToList();
            Assert.IsNotEmpty(result);
            result.Should().HaveCount(10);

        }

That translates to:
DEFINE
    MEASURE 'Internet Sales'[SumOfSalesAmount1] =
        CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ) )
EVALUATE
TOPN (
    10,
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE (
                'Internet Sales',
                'Customer'[First Name],
                'Customer'[Last Name],
                'Geography'[City]
            ),
            "SumOfSalesAmount1", 'Internet Sales'[SumOfSalesAmount1]
        ),
        'Customer'[Last Name] = "Xu"
            && [Internet Total Sales] > 0
    ),
    [SumOfSalesAmount1],
    0
)

And with a filter inside:

        [Test]
        public void SumFiltered()
        {

            var tony = "Tony";
            var q =
                (from c in _db.CustomerSet
                 from sales in _db.InternetSalesSet
                 select
                     new
                     {
                         constant = "Tony",
                         Sum = sales.SalesAmount.Sum(c.FirstName == tony)
                     }
                ).ToList();
            const decimal sumOfTonys = (decimal)49070.2846;
            Assert.AreEqual(q.First().Sum, sumOfTonys);

        }


Translates to:
DEFINE
    MEASURE 'Internet Sales'[SumOfSalesAmount1] =
        CALCULATE (
            SUM ( 'Internet Sales'[Sales Amount] ),
            'Customer'[First Name] = "Tony"
        )
EVALUATE
ROW ( "SumOfSalesAmount1", 'Internet Sales'[SumOfSalesAmount1] )
Here the single measure projection generates ROW expression.


You can use ForAll and ForAllSelected extension methods inside conditions, that translate to ALL() and ALLSELECTED functions in DAX. These seemingly return a boolean to be integrated to conditions within Where() calls easily:

        [Test]
        public void SumAll()
        {

            var q =
                (
                 from sales in _db.InternetSalesSet
                 from customer in _db.CustomerSet
                 where sales.RelatedCustomer.Gender == "M"
                 select
                     new
                     {
                         constant = "Total",
                         Email = GetLength(customer.EmailAddress),
                         Name = customer.FirstName + " " + customer.LastName,
                         Sum = sales.SalesAmount.Sum(customer.ForAll() && customer.RelatedGeography.ForAll())
                     }
                ).ToList();
            const decimal sumOfTonys = (decimal)49070.2846;
            q.First().Sum.Should().BeGreaterThan(sumOfTonys);
        }



Translate to:
DEFINE
    MEASURE 'Internet Sales'[SumOfSalesAmount1] =
        CALCULATE (
            SUM ( 'Internet Sales'[Sales Amount] ),
            ALL ( 'Customer' ),
            ALL ( 'Geography' )
        )
EVALUATE
CALCULATETABLE (
    ADDCOLUMNS (
        SUMMARIZE (
            'Customer',
            'Customer'[Email Address],
            'Customer'[First Name],
            'Customer'[Last Name]
        ),
        "SumOfSalesAmount1", 'Internet Sales'[SumOfSalesAmount1]
    ),
    'Customer'[Gender] = "M"
)  
And

      [Test]
        public void SumAllSelected()
        {

            var q =
                (from c in _db.CustomerSet
                 from sales in _db.InternetSalesSet
                 where c.FirstName == "Tony"
                 select
                     new
                     {
                         constant = "SubTotal",
                         Sum = sales.SalesAmount.Sum(c.FirstName.ForAllSelected()),
                         TotalNumberOfCustomerIds = c.CustomerId.DistinctCount(c.ForAll()),
                         Something = c.Internet_Current_Quarter_Sales(c.ForAll())
                     }
                ).ToList();
            const decimal sumOfTonys = (decimal)49070.2846;
            Assert.AreEqual(q.First().Sum, sumOfTonys);
            q.First().TotalNumberOfCustomerIds.Should().Be(18484);
        }

Translates to:
DEFINE
    MEASURE 'Internet Sales'[SumOfSalesAmount1] =
        CALCULATE (
            SUM ( 'Internet Sales'[Sales Amount] ),
            ALLSELECTED ( 'Customer'[First Name] )
        )
    MEASURE 'Customer'[DistinctCountOfCustomerId2] =
        CALCULATE (
            DISTINCTCOUNT ( 'Customer'[Customer Id] ),
            ALL ( 'Customer' )
        )
EVALUATE
CALCULATETABLE (
    ADDCOLUMNS (
        VALUES ( 'Customer'[First Name] ),
        "SumOfSalesAmount1", 'Internet Sales'[SumOfSalesAmount1],
        "DistinctCountOfCustomerId2", 'Customer'[DistinctCountOfCustomerId2],
        "Internet Current Quarter Sales", CALCULATE (
            [Internet Current Quarter Sales],
            ALL ( 'Customer' )
        )
    ),
    'Customer'[First Name] = "Tony"
)



We also have implemented the LOOKUPVALUE function :

       [Test]
        public void LookupValue()
        {

            var q =
               (from geo in _db.GeographySet
                from customer in _db.CustomerSet
                select new
                {
                    Key = customer.GeographyKey,
                    E = customer.TotalCarsOwned.Max(customer.ForAll()),
                    Value = geo.City.LookupValue(geo.GeographyKey, customer.GeographyKey),

                }).Where(x => x.Value == "London");
            q.ToList().First().Value.Should().Be("London");
        }
   

That translates to:
DEFINE
    MEASURE 'Customer'[MaxOfTotalCarsOwned1] =
        CALCULATE (
            MAX ( 'Customer'[Total Cars Owned] ),
            ALL ( 'Customer' )
        )
EVALUATE
FILTER (
    ADDCOLUMNS (
        VALUES ( 'Customer'[GeographyKey] ),
        "MaxOfTotalCarsOwned1", 'Customer'[MaxOfTotalCarsOwned1],
        "City", LOOKUPVALUE (
            'Geography'[City],
            'Geography'[GeographyKey], 'Customer'[GeographyKey]
        )
    ),
    [City] = "London"
)


You can use SUMX and other X functions from LINQ as the following example shows

     [Test]
        public void SumxTest()
        {
            var q =
                from dates in _db.DateSet
                select new
                {
                    dates.CalendarYear,
                    sum = (
                            from x in _db.InternetSalesSet
                            where x.RelatedCustomer.RelatedGeography.City == "London"
                            select
                            new
                            {
                                x.RelatedCustomer.CustomerId,
                                v = x.SalesAmount.Sum()
                            }).Take(10).Sumx(x => x.v)
                };
            var result = q.ToList();
            result.Should().NotBeNull();
        }


Translates to:
DEFINE
    MEASURE 'Internet Sales'[Sum2] =
        CALCULATE (
            SUMX (
                TOPN (
                    10,
                    CALCULATETABLE (
                        ADDCOLUMNS (
                            VALUES ( 'Customer'[Customer Id] ),
                            "SumOfSalesAmount2", CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ) )
                        ),
                        'Geography'[City] = "London"
                    ),
                    [SumOfSalesAmount2],
                    0
                ),
                [SumOfSalesAmount2]
            )
        )
EVALUATE
ADDCOLUMNS (
    VALUES ( 'Date'[Calendar Year] ),
    "Sum2", 'Internet Sales'[Sum2]
)

Or RANKX


      [Test]
        public void RankTest()
        {
            var q =
               (from sales in _db.InternetSalesSet
                from geo in _db.GeographySet
                select new
                {
                    geo.City,
                    Sales = sales.InternetTotalSales(),
                    SalesRank = (
                            from geo1 in _db.GeographySet
                            where geo1.ForAll()
                            select
                            new
                            {
                                geo1.City,
                                Sales = sales.InternetTotalSales()
                            }).Rankx(x => x.Sales)
                }).Take(10);
            var result = q.ToList().OrderBy(x => x.SalesRank);
            result.First().SalesRank.Should().Be(1);
        }

That translates to:
DEFINE
    MEASURE 'Geography'[Rank1] =
        CALCULATE (
            RANKX (
                CALCULATETABLE (
                    ADDCOLUMNS (
                        VALUES ( 'Geography'[City] ),
                        "Internet Total Sales", CALCULATE ( [Internet Total Sales] )
                    ),
                    ALL ( 'Geography' )
                ),
                [Internet Total Sales]
            )
        )
EVALUATE
TOPN (
    10,
    ADDCOLUMNS (
        VALUES ( 'Geography'[City] ),
        "Internet Total Sales", CALCULATE ( [Internet Total Sales] ),
        "Rank1", 'Geography'[Rank1]
    ),
    [Internet Total Sales],
    0
)

Last edited Jul 25, 2014 at 10:02 AM by LinqToDax, version 4