I'm using version 1.0.2005 of llbl, and would like to sort on a sql server function, rather than a field entity. The idea is that I want to return a random set of rows from a fairly small table. To accomplish that, my ideal sql would look like this..

Select TOP 20 * from MyTable
Order by newid()

I cant see how to create a sort expression on something other than a field entity.

Can this be done?

I cant see how to create a sort expression on something other than a field entity. Can this be done?

Sorting is the ability to order data in one or more fields ascending (A -> Z) or descending (Z -< A). You do this by constructing a SortExpression with one or more SortClauses. SortClauses are simple definitions which contain information about **which field to sort **and in which direction (ascending/descending).

As I see, there is no way, even in 2.0 version (you have an older one).

However,

Select TOP 20 * from MyTable
Order by newid()

Did you test that? I did and have no order effect. Only with constants (1,2,3, etc.)

I recommend you to use two DB calls. One for a DbFunctionCall and another to fetch collection.

David

Actually, with a trick you can Here's my unittest which shows how to fetch random rows from sqlserver: (note: this is 1.0.2005.1 specific code. )

[Test]
public void RandomFetchTest()
{
    DataAccessAdapter adapter = new DataAccessAdapter();

    try
    {
        IEntityField2 newIDField = EntityFieldFactory.Create(CustomerFieldIndex.Country);
        newIDField.Name = "NEWID";
        newIDField.Alias = "NEWID()";
        newIDField.ExpressionToApply = new Expression();

        ISortExpression sorter = new SortExpression(new SortClause(newIDField, null, SortOperator.Ascending));
        EntityCollection customers = new EntityCollection(new CustomerEntityFactory());
        adapter.FetchEntityCollection(customers, null, 0, sorter);
        ResultsetViewer viewer = new ResultsetViewer();
        viewer.BindCollection(customers); 
        viewer.SetTestPurpose("RandomFetchTest: fetches random customers");
        viewer.ShowDialog(null);
    }
    finally
    {
        adapter.Dispose();
    }
}

For v2.0, you need this:

[Test]
public void RandomFetchTest()
{
    DataAccessAdapter adapter = new DataAccessAdapter();

    try
    {
        IEntityField2 newIDField = EntityFieldFactory.Create(CustomerFieldIndex.Country);
        newIDField.Alias = "NEWID()";
        newIDField.ExpressionToApply = new Expression();

        ISortExpression sorter = new SortExpression(new SortClause(newIDField, null, SortOperator.Ascending));
        EntityCollection<CustomerEntity> customers = new EntityCollection<CustomerEntity>(new CustomerEntityFactory());
        adapter.FetchEntityCollection(customers, null, 0, sorter);

        ResultsetViewer viewer = new ResultsetViewer();
        viewer.BindCollection( customers );
        viewer.SetTestPurpose( "RandomFetchTest: fetches random customers" );
        viewer.ShowDialog( null );
    }
    finally
    {
        adapter.Dispose();
    }
}

Worked like a charm! Thanks Frans!