Related to this thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=12394

I am using v2.6 (June 6th, 200 , Sql Server 2005, .net 2.0, Self Servicing

I have attempted to derive from SortExpression as suggested by Otis in the above thread, however it causes a stack overflow exception when the GetMulti call executes.

I was curious if their is a more official way to do this, Otis mentioned adding this feature to handle this situation to the todo list.

My end goal is to sort a table with the null values appearing at the bottom of the list.

public class DerivedSortExpression : SortExpression
    {
        public DerivedSortExpression(ISortClause sortClauseToAdd) : base(sortClauseToAdd) { }

        public override string ToQueryText(ref int uniqueMarker, bool aliasesForExpressionsAggregates)
        {
            return base.ToQueryText(ref uniqueMarker, false);
        }
    }

EntityField field = SomeFieldValue.Field;
field.ExpressionToApply = new DbFunctionCall("ISNULL", new object[] { SomeFieldValue.Field, "z" });
SortExpression sorter = new DerivedSortExpression(field | SortOperator.Ascending);

Now you can use _EmitAliasForExpressionAggregateField _ property:

OrdersCollection orders = new OrdersCollection();

IEntityField isnullField = OrdersFields.CustomerId.SetExpression(
    new DbFunctionCall("ISNULL", new object[] { OrdersFields.CustomerId, "z" }));

SortClause isnullSortClause = new SortClause(isnullField, SortOperator.Ascending);
isnullSortClause.EmitAliasForExpressionAggregateField = false;

SortExpression sorter = new SortExpression(isnullSortClause);

orders.GetMulti(null, 0, sorter);

I have the same problem, stack overflow.

An unhandled exception of type 'System.StackOverflowException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll

I've removed the filters and relations from the GetMulti call. It is now just a simple fetch all records with the above sort.

Is there a way to provide you with more information regarding the error?

Is there a way to provide you with more information regarding the error?

Yes, please post the exact exception message and stack trace. Also post the code snippet where you get the error.

"An unhandled exception of type 'System.StackOverflowException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll" is everything from the detail of the exception, there is no more information about what is happening. I'm running from the code-behind of an asp.net 2.0 web application in debug mode.

In preparation for this post I've realized that the problem is not necessarily related to the the ISNULL function but that I am trying to determine the field to sort on based on a string. I have done this in the past with success however when I try to apply to the ISNULL code to the field I am receiving the stack overflow message.

Here is a working code snippet. ApplicationStatus is the column I'm sorting on, it is possible that some students do not have a record in the StudentStatus table.

StudentCollection students = new StudentCollection();
RelationCollection relations = new RelationCollection(StudentEntity.Relations.StudentStatusEntityUsingStudentId, JoinHint.Left);

IEntityField isNullField = StudentStatusFields.ApplicationStatus.SetExpression(
    new DbFunctionCall("ISNULL", new object[] { StudentStatusFields.ApplicationStatus, "zzz" }));
SortClause isNullSortClause = new SortClause(isNullField, SortOperator.Ascending);
isNullSortClause.EmitAliasForExpressionAggregateField = false;
SortExpression sorter = new SortExpression(isNullSortClause);

students.GetMulti(null, 0, sorter, relations);

Here is the code snippet I'm trying to get working. The field to sort on is passed in as a string (hard-coded to "ApplicationStatus" for now).

StudentCollection students = new StudentCollection();
RelationCollection relations = new RelationCollection(StudentEntity.Relations.StudentStatusEntityUsingStudentId, JoinHint.Left);

IEntityField field = EntityFieldFactory.Create((StudentStatusFieldIndex)Enum.Parse(typeof(StudentStatusFieldIndex), "ApplicationStatus"));
IEntityField isNullField = field.SetExpression(
    new DbFunctionCall("ISNULL", new object[] { field, "zzz" }));
SortClause isNullSortClause = new SortClause(isNullField, SortOperator.Ascending);
isNullSortClause.EmitAliasForExpressionAggregateField = false;
SortExpression sorter = new SortExpression(isNullSortClause);

students.GetMulti(null, 0, sorter, relations);

msickler wrote:

Here is the code snippet I'm trying to get working. The field to sort on is passed in as a string (hard-coded to "ApplicationStatus" for now).

StudentCollection students = new StudentCollection();
RelationCollection relations = new RelationCollection(StudentEntity.Relations.StudentStatusEntityUsingStudentId, JoinHint.Left);

IEntityField field = EntityFieldFactory.Create((StudentStatusFieldIndex)Enum.Parse(typeof(StudentStatusFieldIndex), "ApplicationStatus"));
IEntityField isNullField = field.SetExpression(
    new DbFunctionCall("ISNULL", new object[] { field, "zzz" }));
SortClause isNullSortClause = new SortClause(isNullField, SortOperator.Ascending);
isNullSortClause.EmitAliasForExpressionAggregateField = false;
SortExpression sorter = new SortExpression(isNullSortClause);

students.GetMulti(null, 0, sorter, relations);

Indeed, an exception is thrown in that case. Do you need, for some reason, to pass the "ApplicationStatus" name hard-coded?

(About the error) this is the cause of the stackoverflow:

IEntityField isNullField = field.SetExpression(
    new DbFunctionCall("ISNULL", new object[] { field, "zzz" }));

You pass 'field' as parameter to the db function call, but it's also the field containing the expression, so the parameter gets evaluated, it has an expression, that gets evaluated, it has a parameter, that gets evaluated etc. etc. boom

Use a different field object to contain the expression.

That was it. I was so focused on the "ISNULL" portion of code I overlooked that as a possible cause. Thanks for the help!