Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> QuerySpec OrderBy ThenBy With Values
 

Pages: 1
LLBLGen Pro Runtime Framework
QuerySpec OrderBy ThenBy With Values
Page:1/1 

  Print all messages in this thread  
Poster Message
TopDog74
User



Location:
UK
Joined on:
27-Apr-2012 13:04:36
Posted:
40 posts
# Posted on: 11-Mar-2016 13:48:03.  
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
  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8137 posts
# Posted on: 12-Mar-2016 15:53:01.  
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.


David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
TopDog74
User



Location:
UK
Joined on:
27-Apr-2012 13:04:36
Posted:
40 posts
# Posted on: 15-Mar-2016 16:20:30.  
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
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14626 posts
# Posted on: 15-Mar-2016 22:40:20.  
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.


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38047 posts
# Posted on: 16-Mar-2016 16:23:45.  
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

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
arschr
User



Location:
Atlanta, Georgia; USA
Joined on:
14-Dec-2003 16:57:29
Posted:
887 posts
# Posted on: 16-Mar-2016 16:27:02.  
>> .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


- Al  Top
TopDog74
User



Location:
UK
Joined on:
27-Apr-2012 13:04:36
Posted:
40 posts
# Posted on: 18-Mar-2016 00:00:42.  
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:

Code:

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):
Code:

            //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
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38047 posts
# Posted on: 18-Mar-2016 10:37:17.  
Code:
query.OrderBy(BlahFunctions.PurchaseOrderPrioritySort(PurchorderFields.OrdOrderStatus).Ascending());


Regular Smiley 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:

Code:


[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);
    }
}


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
TopDog74
User



Location:
UK
Joined on:
27-Apr-2012 13:04:36
Posted:
40 posts
# Posted on: 18-Mar-2016 12:00:16.  
cool thanks. Got it working. Tongue

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:
Code:

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.
  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.