This is a migrated thread and some comments may be shown as answers.

Edit generated sql query.

1 Answer 20 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Jose Mejia
Top achievements
Rank 1
Jose Mejia asked on 08 Oct 2013, 09:50 AM

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.

1 Answer, 1 is accepted

Sort by
0
Accepted
Kristian Nikolov
Telerik team
answered on 11 Oct 2013, 07:39 AM
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
OpenAccess ORM Q3 2013 Beta is available for immediate download in your account. Get it now and play with the latest bits. See what's new >>
Tags
General Discussions
Asked by
Jose Mejia
Top achievements
Rank 1
Answers by
Kristian Nikolov
Telerik team
Share this question
or