I'm using LLBLGen Pro 2.5 final (December 5th build) using SQL Server 2005 and .Net 2.0.

I'm going to code around this so that I don't need the data sorted, but I'd like to know for future reference:

If I use an EntityField object that uses an expression in a sort clause, it emits the name of the field, rather than the expression. This is in a prefetch, so DISTINCT is not emitted, so it shouldn't matter that the expression isn't one of the fields. And if it did, I'd expect an error since there is no field of that name. (Well, I'd expect it from LLBL, I'm getting exactly that from SQL.)

Shortening the names a bit, here's what I'm doing:

IExpression adExp = new DbFunctionCall("COALESCE", new object[] { ATFields.ATDate, ATFields.CreatedDate }); EntityField aDate = new EntityField("ADate", adExp);

...

sort = new SortExpression(); sort.Add(aDate | SortOperator.Descending); Prefetch.Add(AccountEntity.PrefetchPathAT, 0, null, null, sort);

The SQL code emitted will contain

ORDER BY [ADate] DESC

rather than

ORDER BY COALESCE(blah.dbo.[ATDate], blah.dbo.[CreatedDate])

I know this won't work if DISTINCT is specified, but it isn't. In SQL Server at least, this can't ever work, as you can't reference a select list alias in the order by clause, you have to use the expression. (In this case the field isn't even in the select list, but even if it were this would fail.)

Is it simply a limitation that you can't sort on an expression?

I think you can do that in a TypedList/DynamicList. However in a EntityCollection you can't. Please try this tip: make the alias of the EntityField2 be the whole expression _COALESCE(....). _Try and let me know if that made it.

In order to do that I would need to have LLBLGen Pro give me the fully resolved names of the fields I wanted to pass as arguments to COALESCE, with catalog rewriting applied (and in some situations, with aliases resolved to the internal aliases used in the generated query). It seems unlikely that this would be possible since the query is still being built and has not yet been "rendered" into SQL.

I'm a little unclear from the documentation as to whether sorting on an expression should be possible. It refers to sorting against fields, including using SetObjectAlias(), but doesn't mention anything about expressions. The section on expressions mentions using them in 3 specific scenarios, and sorting isn't one of them.

However, there are other situations I've found where an object that seems like it should work in a given situation does, even though the documentation doesn't give a specific example of it. One of the advantages of an object oriented solution is exactly this sort of synergy, where a solution meant for one situation works in others as well, because the pieces of the model fit together in logical ways. On the other hand, sometimes what seems logical doesn't work because of additional behind the scenes concerns, and learning what does and doesn't work is just part of learning to use the tool. And this forum has been great for that.

If the answer is that this is not supported, then that's fine. I just want to know what I can and can't do. This may be considered an obscure enough case that it isn't worth the development effort to support it.

If, on the other hand, this should work, then consider it a bug report.

I'm sorry that I can't conveniently try your work around. I've refactored my code so that I don't need the data sorted in this case. I posted this mostly so I'd know for next time. However, the catalog rewriting problem would be a very real concern in this case, and so that wouldn't have given me an effective solution to my problem, without even more coding to parallel the catalog rewriting that LLBLGen Pro is doing on my behalf in normal situations. However, I do appreciate you considering the problem and trying to find a fix for me.

Would you please check if the following code makes any difference?

EntityField aDate = ATFields.ATDate;

aDate.ExpressionToApply = new DbFunctionCall("COALESCE", new object[] { 
    ATFields.ATDate, ATFields.CreatedDate });

SortExpression sort = new SortExpression(aDate | SortOperator.Descending);
Prefetch.Add(AccountEntity.PrefetchPathAT, 0, null, null, sort);

You can sort on an expression, though I fail to see how a COALESCE call would work in this case, as order by works on columns in full, while COALESCE is then applied to each row, so this contradicts with how order by works. it COULD work if you added a field to the projection with COALESCE from both fields and then ordered on that field.

Also what catalog name overwriting problem are you referring to?

Walaa: the only difference with using that code is that the instead of the made up name used in my code, it emits [ATDate] in the order by expression. Since this is an actual field in the query, the query no longer fails, however it doesn't emit the COALESCE and so would return incorrect results. (It does not, however, prefix the field name with catalog and schema.)

Otis: At least in SQL Server (2005 and 2000) it will indeed sort by the output of the COALESCE function. I've used this many times. Below is a test script that demonstrates the behavior. This could be a SQL Server deviation from the standard, and the documentation is unclear on this, but it does work. Of course, if this is non-standard behavior, that would explain why it isn't supported. If indeed, it is not, I'm still unclear on that.

If you run the following script you will see that it does indeed sort by the return value of the COALESCE:

DECLARE @Test TABLE( ID INT IDentity, Val1 INT, Val2 INT )

INSERT INTO @Test(Val1, Val2) SELECT 40, 2 UNION ALL SELECT NULL, 30 UNION ALL SELECT 20, 1 UNION ALL SELECT NULL, 10

SELECT * FROM @Test SELECT * FROM @Test ORDER BY Val1 SELECT * FROM @Test ORDER BY Val2 SELECT * FROM @Test ORDER BY COALESCE(Val1, Val2)

As to the catalog rewriting problem: that was relative to Daelmo's suggestion of putting the rendered expression in as the alias of the field object. Since I'm using catalog rewriting, I would have to duplicate that logic in producing the text of the alias field. Other than that it might work, since it does appear to be emitting the alias field unchanged.

D-Rider wrote:

Walaa: the only difference with using that code is that the instead of the made up name used in my code, it emits [ATDate] in the order by expression. Since this is an actual field in the query, the query no longer fails, however it doesn't emit the COALESCE and so would return incorrect results. (It does not, however, prefix the field name with catalog and schema.)

The thing is this: when a field in a SortClause has an expression, it is assumed that expression is emitted in the selectlist / projection, and the alias is used to refer to the field to sort on. This is because the query is generated in different systems, so they have no notion of eachother. There's a workaround possible though if you want to change this behaviour: Derive a class from SortExpression and override ToQueryText. In that method, simply call the base class method with 'false' specified for aliasesForExpressionsAggregates. You'll then get the expression in full form in the orderby. This should give you the COALESCE function call as specified in your first post. Of course instead of the default SortExpression class you should use your derived class version

Otis: At least in SQL Server (2005 and 2000) it will indeed sort by the output of the COALESCE function. I've used this many times. Below is a test script that demonstrates the behavior. This could be a SQL Server deviation from the standard, and the documentation is unclear on this, but it does work. Of course, if this is non-standard behavior, that would explain why it isn't supported. If indeed, it is not, I'm still unclear on that.

If you run the following script you will see that it does indeed sort by the return value of the COALESCE:

DECLARE @Test TABLE( ID INT IDentity, Val1 INT, Val2 INT )

INSERT INTO @Test(Val1, Val2) SELECT 40, 2 UNION ALL SELECT NULL, 30 UNION ALL SELECT 20, 1 UNION ALL SELECT NULL, 10

SELECT * FROM @Test SELECT * FROM @Test ORDER BY Val1 SELECT * FROM @Test ORDER BY Val2 SELECT * FROM @Test ORDER BY COALESCE(Val1, Val2)

Interesting!

Definitely not what I would expect at first, but thinking about it, it makes sense: the column to sort on is a different column, secretly added to the resultset (this is also the reason why an order by on a field which isn't in the projection with DISTINCT present won't work) and that column is filled with the results of COALESCE(val1, val2) applied to every row. Similar to any other expression in ORDER BY... Thanks for the heads up

Thank you! In this case I've found a faster solution that doesn't use the sort, but I expect this will come up again, and I'll definitely use the technique of overriding SortExpression when it does. Since it's so simple, a property to set on SortExpression to emit the expression rather than the alias might be a convenient addition in a later version.

Thank you also for your explanation of what this is doing behind the scenes. I'd never really thought about how that works, but it makes perfect sense now, and does explain the rule on DISTINCT, which I never understood.

Once again, thank you for a great product and even better support.

Glad it's sorted out

The property is a good suggestion, I'll add that to the todo list (likely a property on the sortclause so you can tweak it on a per-clause basis.

I ran across this thread while trying to learn how to use an expression on a sort. It looks like the property on SortClause that Frans mentioned was implemented - it's called EmitAliasForExpressionAggregateField, and should be set to false to allow the expression to be used in the sort clause.

wesleysmith wrote:

I ran across this thread while trying to learn how to use an expression on a sort. It looks like the property on SortClause that Frans mentioned was implemented - it's called EmitAliasForExpressionAggregateField, and should be set to false to allow the expression to be used in the sort clause.

That is correct

P.S. Please don't resurrect old threads. We prefer you open new ones (http://llblgen.com/tinyforum/Messages.aspx?ThreadID=7725). Thanks for the understanding