I have the following SQL Query I am trying to replicate using LLBLGen 2.5. SelfService. I am new to LLBLGen and so far I am really enjoying the experience.

Select Distinct(year(check)) as Year from EmployeeCheck where EmployeeMasterId = @EmployeeId ORDER BY Year Desc

This will return a List of years for which a given employee has received a check.

So far I have:

 var checkColl = new EmployeeCheckCollection();
       IPredicateExpression filter = new PredicateExpression();
       filter.Add(EmployeeCheckFields.EmployeeMasterId == employeeId);


       checkColl.GetMulti(filter);

Any assistance would be greatly appreciated.

Dan

Don't use an entityCollection, as you only need a resultset with one field (years).

Please use a DynamicList, with one ResultSetFields of just one field, and set it's ExpressionToApply to a DbFunctionCall() which calls the Year() dbFunction.

And for the Distinct part, just set the AllowDuplicates parameter of the typedListDao.GetMultiAsDataTable() to false. Of-course you'll have to pass a predicateExpression and a SortExpression for filtering and sorting.

For more info please check the Docs for the usage of DynamicLists & DBFunctionCalls.

Here is the code I came up with for future reference

        IPredicateExpression filter = new PredicateExpression();
        filter.Add(EmployeeCheckFields.EmployeeMasterId == EmployeeID)

        var fields = new ResultsetFields(1);
        fields.DefineField(EmployeeCheckFields.CheckDate, 0, "Year");
        fields[0].ExpressionToApply = new DbFunctionCall("CAST({0} as bigint)",
                                                         new object[]
                                                            {
                                                                new DbFunctionCall("YEAR",
                                                                                   new object[]
                                                                                       {
                                                                                           EmployeeCheckFields.
                                                                                               CheckDate
                                                                                       })
                                                        });

        ISortExpression sorter = new SortExpression();
        sorter.Add(new SortClause(fields[0], SortOperator.Descending));


        var dynamicList = new DataTable();
        var dao = new TypedListDAO();
        dao.GetMultiAsDataTable(fields, dynamicList, 0, sorter, filter, null, false, null, null, 0, 0);

Thanks for pointing me in the right direction