Hi all,

I'm selecting the TOP 1 record in a table using a custom sort criteria. Is it possible to do an ORDER BY IsNull(Somefield, 1) ASC for sorting purposes?

If not - what is the best way to do this? Is there a way to append a calculated column to my result set?

I'm filling an EntityCollection, then telling the Adapter to only return 1 result.

Thanks!

The sort clause object accepts an EntityField, to which you can set the ExpressionToApply to a DBFunctionCall of IsNULL.

Walaa wrote:

The sort clause object accepts an EntityField, to which you can set the ExpressionToApply to a DBFunctionCall of IsNULL.

Hi,

I'm just trying to do the same thing with LLBLGen 2.5 Adapter. Could you please give the example above in a few lines of code to help me understand it?

I like to have this SQL in the end:

SELECT VistorId, FirstName, LastName FROM Visitors ORDER BY ISNULL(VisitorName,LastName)

Thanks a lot, Patrick

EntityField myField = CustomerFields.VisitorName;
myField.ExpressionToApply = new DbFunctionCall("ISNULL", new object[] { VisitorFields.VisitorName, VisitorFields.LastName})

SortExpression sorter = new SortExpression(myField | SortOperator.Ascending)

Thank you Waala.

For some reason this code:

EntityField2 myField = VisitorFields.VisitorName;
myField.ExpressionToApply = new DbFunctionCall("ISNULL", new object[] { VisitorFields.VisitorName, VisitorFields.VisitorId });

SortExpression sorter = new SortExpression(myField | SortOperator.Ascending);
EntityCollection<VisitorEntity> col = new EntityCollection<VisitorEntity>();
da.FetchEntityCollection(col, null, 0, sorter, prefetch);

returns this query:

SELECT [Test01].[dbo].[Visitor].[VisitorId], [Test01].[dbo].[Visitor].[VisitorName] FROM [Test01].[dbo].[Visitor]  ORDER BY [VisitorName] ASC

Any ideas?

Thanks, Patrick

The Walaa's advice is for TypedLists/DynamicLists. Here is why for EntityCollections is tricky, however also a workaround is provided: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=12394