Edit generated sql query.

[Jose Mejia]

Hello again!

Suppose I have simple  OA entity, say field1-- int, field2 -- string, field3 -- string.
It would be ideal for me to issue a little bit customized sql query, say:

select field1,field2, somefunc(field2)
from bla-bla

so field3  should be mapped to somefunc(field2). Is it possible somehow ?  I've sqlite as backend and OA version is 2013.2.702.1.

Thanks in advance.

[Kristian Nikolov]

Hello Jose,

In order to execute custom queries with Telerik OpenAccess ORM, you can use the ExecuteQuery<T> method of the context object.

This method allows you to execute a query and materialize the result as either persistent or non-persistent class. In your case, since you are using a function in the query the result should be materialized in a non-persistent class. This documentation article details the workflow of using the ExecuteQuery method.

Alternatively you can map a method to a scalar SQLite function and use that method in Linq query. To do that you need to define a static method in a class and add the following attribute:

[MappedFunctionAttribute(Name = "SQLiteFunctionName", IsDeterministic = false, Backend = Backend.SQLite)]

The following code snippet illustrates what would look a method mapped to the Hex function:

public class StaticMethods
{
    [MappedFunctionAttribute(Name = "Hex", IsDeterministic = false, Backend = Backend.SQLite)]
    public static string GetHex(string toBeHexed)
    {
        throw new NotImplementedException();
    }
}

Please note that method mapped to a scalar function can only be used in a Linq query and will throw NotImplementedException when used outside of such. An example usage of the GetHex method would be:

var result = context.Products.Select<Product, CommandResultType>(product =>
                    new CommandResultType()
                    {
                        Id = product.ProductId,
                        Description = product.Description,
                        Hexed = StaticMethods.GetHex(product.Description)
                    });

You can read more about using database functions in this documentation article.

In case your scenario demands that you materialize the result of a customized query in a persistent class, you can do so by extending the desired persistent class with a partial class containing the required properties in a separate file.

I hope this information will be useful.

Regards,
Kristian Nikolov
Telerik