OpenAccess and CONTAINS operator

[Samuel]

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()"

[Alexander]

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