Calling a custom database function in WHERE part of your entity

I have encountered a need to call a custom database function in WHERE part of the entity using entityspaces(2008.x.x) which does not support Raw SQL Injection Everywhere.

I have searched everywhere and posted inquiries in the friendly ES forum but theres no luck.

This task can be done easily using stored procedure but I wanted to use ES for I have other filters, join and grouping, all of this are already in place except for the part where I need to call the function.

Now LastQuery to the rescue, this property contains nothing until you make the query, so the idea is to call the query making sure that only one or zero record will be return then use the value of LastQuery to construct a new SQL Query/Statement with the datase function in it to be use in our entity custom load. I am not sure if this is a good practice but I works for me. See the code below.

' Make sure we only have one record for this load

Dim esqUsers As New UserCollectionQuery("uv")

esqUsers.es.Top = 1

esqUsers.es.PageSize = 10

esqUsers.es.PageNumber = _PageNumber



' this part will give us an entry point for insertion of call to our custom database function it will generate this string "uv.[UserID] IN ('-2')"

esqUsers.Where(esqUsers.UserID.OP(esWhereOperand.In, "-2".Split("!"c)))



' more filters, join and grouping here.



' Make the call so we can generate the LastQuery

esUsers = New UserCollection

esUsers.Load(esqUsers)



' Get last query

Dim strLastQuery As String = esqUsers.es.LastQuery

' Insert the call to the function

strLastQuery = strLastQuery.Replace("uv.[UserID] IN ('-2')", "database function & params")



' Make sure we have the data

strLastQuery = strLastQuery.Replace("TOP 1", "")



' You also need to update some parameters if needed.



' Now we call our custom load 

esUsers.CustomLoad(strLastQuery)

The custom load function is very simple, see below:

Public Function CustomLoad(ByVal sSQL As String) As UserCollection

            MyBase.Load(esQueryType.Text, sSQL)

            Return Me

        End Function

HTH