Edit generated sql query.

2 posts, 1 answers
  1. Jose Mejia
    Jose  Mejia avatar
    107 posts
    Member since:
    May 2009

    Posted 08 Oct 2013 Link to this post

    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.
  2. Answer
    Kristian Nikolov
    Kristian Nikolov avatar
    206 posts

    Posted 11 Oct 2013 Link to this post

    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.

    Kristian Nikolov
    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 >>
  3. DevCraft banner
Back to Top