Hi, I have to sort my collection just on a date value, but the data is stored in a datetime format. Does anyone have an idea? Preferrable an example how to accomplish this.

I could create a view, but I don't want to generate the DAL again.

I use LLBLGen 2.6.

Thank you

Do you want to sort on the database side or in memor(client side)?

Hi Walaa,

At this moment I use an EntityView so I would say in memory. But I don't care where to sort, as long as I can get it to work.

Thanks

So what's wrong with the In-Memory sorting using a EntityView. This should work smoothly as far as I know.

Wou dyou please explain the erronous behaviour you are facing and supply a simple code snippet.

Thanks.

Hi Wallaa, I think I have to explain a little more. In the database these records are stored in a datetime-value. I mean the time is also set to the moment I store the data. ie Record A has 2009-11-18 15:44:10 as a value and record B 2009-11-18 14:00:00.

The problem I'm facing I want to sort on just the datepart and no check on the time value. Now when I use a descending sort. Record A is the top record, which is correct, but in my sorting clause I have some other fields which should also be used to sort.

Say Record A also has a field Type which is set to 2 and the value for Record B for the Type field is set to 1.

the result I would like to have in this case is record B as first record because the typefield is 1 the record A because the typefield is 2.

When I first sort on the datefield desc then sort on the type-field asc. Record A is the top record. Which makes sense because the timevalue 15:44:10 is higher then the time value for record B 14:00:00.

If I could just sort on the date (2009-11-18 without the time-part) descending then sort on the typefield, everything would be as I want to have it.

Or is there a way to set the timevalue to 12:00:00 before sorting, so every record has the same time in de datetime-part.

I hope this explains a little better what I try to achieve here.

To be more clear, I don't get any errors, just want to know how I could do this.

So, you want the resulted query would be something link this:

SELECT * FROM Orders
ORDER BY CAST(OrderDate AS DATE)

You have to use a DBFuncionCall, create a field with it and pass it to a sortexpression:

DataAccessAdapter adapter = new DataAccessAdapter();

// prepare the function call and the field to sort on
EntityCollection<OrdersEntity> orders = new EntityCollection<OrdersEntity>();
IExpression datePart = new DbFunctionCall("CAST({0} AS DATE)", new object[] { OrdersFields.OrderDate });
IEntityField2 datePartField = OrdersFields.OrderDate.SetExpression(datePart);

// prepare the sorter
ISortClause datePartSortClause = new SortClause(datePartField, null, SortOperator.Descending);
datePartSortClause.EmitAliasForExpressionAggregateField = false;
ISortExpression sorter = new SortExpression(datePartSortClause);

// fetch
adapter.FetchEntityCollection(orders, null, 0, sorter);

The LINQ2LLBL version:

DataAccessAdapter adapter = new DataAccessAdapter();
LinqMetaData metaData = new LinqMetaData(adapter);

var q = from o in metaData.Orders
        orderby o.OrderDate.Date descending
        select o;

List<OrdersEntity> orders = q.ToList();

Hi Dealmo,

thanks man. That did it for me. I had to change the "CAST({0} AS DATE)" to "CAST( FLOOR( CAST( {0} AS FLOAT ) ) AS DATETIME )". But I would never had come up with you solution. I was trying to use the DBFunctionCall, but couldn't get it working.

Thanks a lot.