Hi,

Is it possible in QuerySpec to sort the query in an OrderBy statement which orders a field depending on specific values.

For example i have been doing the following LINQ sort on a projected result set after the info is fetched. I'd like to know if it is possible to have this in the generated query itself, and if so, an example of the QuerySpec syntax to use would be great.:

var orderedResponse = response.OrderByDescending(x => x.OrderPriority == "Urgent") .ThenBy(x => x.OrderPriority == "FastTrack") .ThenBy(x => x.OrderPriority == "Local") .ThenBy(x => x.OrderPriority == "Normal");

Thanks, Iain

Hi Iain,

The code you posted above, is in-memory linq query, right? IFAIK that kind of sort is not supported, but we will confirm this.

Hi Daelmo,

Yeah that's right i'm sorting on an in-memory projection in the example.

It would be good to know if it's possible or if there is another way to achieve this ordering on the generated SQL.

Reason i ask is because i am now doing database paging on the initial fetch, so my post fetch ordering hack isn't going to work for me any more.

Cheers, Iain

Not sure if this is even possible in the DB. Only workaround is to execute a fetch for each sort value (filter on the sort value). First fetch the "Urgent" ones. Then fetch the "FastTrack"... etc.

You can have this in a view, which performs the fetches and performs Union of the resultsets.

One trick might work, which is a database function, which returns a numeric value (1, 2, 3, 4) based on the input, so "Urgent" == 1, "FastTrack" == 2, "Local"==3, "Normal"==4. Then do:

ORDER BY YourFunction(field) ASC

.ThenBy(x => x.OrderPriority == "FastTrack") .ThenBy(x => x.OrderPriority == "Local") .ThenBy(x => x.OrderPriority == "Normal");

In the database you need to create a table or function that maps OrderPriority to a sort priority

Fasttrack -> sortPriority = 1 Local -> SortPriority = 2 Normal -> SortPriority = 3

join to this table and in you order by order by sortpriority.

This could also be a function but it might not be as efficient, depending on how many rows are being returned.

Edit ==== Just saw fran's post it is the same idea as i posted

Hi,

Thanks for the ideas. I just got round to having a go at this today. so i have added a function to the db as described and i have looked at the docs about how to add a Function to the my code base. However i am having difficulty with the syntax on how to ensure the query.OrderBy(???) can make use of the function.

I have done the following for mapping the function, I'm not 100% sure if it is correct or not:

public class BlahFunctions
    {

        public static int PurchaseOrderPrioritySort(IEntityField2 field)
        {
            return 0;
        }

        /// Class which defines the custom mapping between BlahFunctions.PurchaseOrderPrioritySort and
        /// fnPurchaseOrderPrioritySort, the database function.
        public class BlahFunctionMappings : FunctionMappingStore
        {
            public BlahFunctionMappings()
                : base()
            {
                // define the mapping. SQLServer 2000 needs the schema to be present for the function call,
                // so we specify that as well.
                this.Add(new SD.LLBLGen.Pro.ORMSupportClasses.FunctionMapping(typeof(BlahFunctions), "PurchaseOrderPrioritySort", 1,
                                "fnPurchaseOrderPrioritySort({0})", "BlahDb", "dbo"));
            }
        }

    }

Then to call the function i get as far as this (Note i have already defined a QuerySpec query by this point):

            //set up the custom function mapping store
            var mappedFunctions = new BlahFunctions.BlahFunctionMappings();
            //assign the custom function mapping store to the query
            query.CustomFunctionMappingStore = mappedFunctions;

            //at this point i dont see how to actually specify the function in the OrderBy method of the query as it expects a SortClause.
            query.OrderBy(BlahFunctions.PurchaseOrderPrioritySort(PurchorderFields.OrdOrderStatus));

Any pointers on how it should be written would be greatly appreciated.

Thanks, Iain

query.OrderBy(BlahFunctions.PurchaseOrderPrioritySort(PurchorderFields.OrdOrderStatus).Ascending());

There are extension methods defined for creating sortclauses: Ascending() / Descending(). They create ISortClause instances when called on fields, expressions, function mappings, etc.

If you'd have wanted to sort on field Foo, you also would have used these extension methods.

See example:

[Test]
public void CustomFunctionInOrderByTest()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        var qf = new QueryFactory();
        var q = qf.Order.Where(OrderFields.CustomerId == "CHOPS")
                        .OrderBy(NorthwindFunctionsQS.CalculateOrderTotal(OrderFields.OrderId, true).Ascending())
                        .Select(() => new
                        {
                            OrderId = OrderFields.OrderId.ToValue<int>(),
                            OrderTotal = NorthwindFunctionsQS.CalculateOrderTotal(OrderFields.OrderId, true).ToValue<decimal>()
                        });
        q.CustomFunctionMappingStore = new NorthwindFunctionMappingsQS();
        var results = adapter.FetchQuery(q);

        int count = 0;
        decimal totalOfOrders = 0.0M;
        decimal previous=0.0M, current=0.0M;
        foreach(var v in results)
        {
            previous = current;
            current = v.OrderTotal;
            Assert.IsTrue(previous < current);
            count++;
            totalOfOrders += v.OrderTotal;
        }
        Assert.AreEqual(8, count);
        Assert.AreEqual(21045.0M, totalOfOrders);
    }
}

cool thanks. Got it working.

So the thing i was missing was i needed my .NET method in my functions class to return something other than an 'int' which would allow me to use the extension methods to create the ISortClause.

Pretty cool that this is supported at all.

Just for completeness here's my function class altered to work:

public class BlahFunctions
    {
        public static FunctionMappingExpression PurchaseOrderPrioritySort(IEntityFieldCore field)
        {
            return new FunctionMappingExpression(typeof(BlahFunctions), "PurchaseOrderPrioritySort", 1, new object[] { field });
        }

        /// Class which defines the custom mapping between BlahFunctions.PurchaseOrderPrioritySort and
        /// fnPurchaseOrderPrioritySort, the database function.
        public class BlahFunctionMappings : FunctionMappingStore
        {
            public BlahFunctionMappings()
                : base()
            {
                // define the mapping. SQLServer 2000 needs the schema to be present for the function call, 
                // so we specify that as well.
                Add(new SD.LLBLGen.Pro.ORMSupportClasses.FunctionMapping(typeof(BlahFunctions), "PurchaseOrderPrioritySort", 1,
                                "fnPurchaseOrderPrioritySort({0})", "myDbName", "dbo"));        
            }
        }

    }

Thanks for all the help guys.