OpenAccess and CONTAINS operator

2 posts, 0 answers
  1. Samuel
    Samuel avatar
    13 posts
    Member since:
    Aug 2013

    Posted 30 Aug 2013 Link to this post

    Giving OpenAccess a go to see if I could use it in combination with WebAPI and ODATA

    So far, reading data seemed to work OK (skip, top, inlineCount, select, they all work). But CONTAINS (which translate as substringOf('') in ODATA, raises an exception, no matter what field I query on (of type 'string' of course....) 

    Am I doing anything wrong here  ?

    Here's the ODATA URL:

    http://localhost:52446/odata/Mandates?$filter=substringof('Marc'%2CAccountOwnerName)%20eq%20true&$inlinecount=allpages

    The WebAPi controller method:

      [Queryable(AllowedQueryOptions = AllowedQueryOptions.All)]
      public override IQueryable<Mandate> Get()
      {
          DirectDebitModel.DirectDebitModel dbContext = new DirectDebitModel.DirectDebitModel();
          return dbContext.Mandates;
    }

    Finally, the generated SQL:

    SELECT COUNT(1) AS  EXPR1                  FROM [Mandates] a WHERE (( ( (CASE WHEN (a.[AccountOwner_Name] IS NULL OR @p0 = 1) THEN NULL ELSE  (CASE WHEN a.[AccountOwner_Name] LIKE '%' + @p1 + '%' ESCAPE '\' THEN 1 ELSE 0 END)  END) ) IS NULL) AND ( (@p2) IS NULL) OR ( (CASE WHEN (a.[AccountOwner_Name] IS NULL OR @p3 = 1) THEN NULL ELSE  (CASE WHEN a.[AccountOwner_Name] LIKE '%' + @p4 + '%' ESCAPE '\' THEN 1 ELSE 0 END)  END)  AND @p5 = 1 OR not ( (CASE WHEN (a.[AccountOwner_Name] IS NULL OR @p6 = 1) THEN NULL ELSE  (CASE WHEN a.[AccountOwner_Name] LIKE '%' + @p7 + '%' ESCAPE '\' THEN 1 ELSE 0 END)  END) ) AND not (@p8 = 1)))

    which raises the exception:

    "An expression of non-boolean type specified in a context where a condition is expected, near 'AND'. Statement(s) could not be prepared."

    stacktrace:

    at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery() at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeQuery() at OpenAccessRuntime.Relational.fetch.FetchResultImp.Execute()"
  2. Alexander
    Admin
    Alexander avatar
    727 posts

    Posted 03 Sep 2013 Link to this post

    Hi Samuel,

    Such error was reported some time ago by another customer. The solution was to set the HandleNullPropagation option to False in the Queryable attribute:
    [Queryable(AllowedQueryOptions = AllowedQueryOptions.All, HandleNullPropagation = HandleNullPropagationOption.False)]
     public override IQueryable<Mandate> Get()
    ...

    This way the problematic null checks which confuse OpenAccess are excluded from the query expression and the generated SQL statement is correct. Hope that helps.

    Regards,
    Alexander
    Telerik
    OpenAccess ORM Q2 2013 brings you a more powerful code generation and a unique Bulk Operations support with LINQ syntax. Check out the list of new functionality and improvementsshipped with this release.

  3. DevCraft banner
Back to Top