I suggest you ...

Support Calls to .ToString() in SqlExpression Where Clause

This feature is useful when you need to do STRING related operations on NON STRING types like INT.

One use case is when you need to do pattern matching (used in searches) on an INT column. That would be great to be able to just write:

q.Where(x => x.IntColumn.ToString().Contains("123"));

which should generate the following in SQL Server:

WHERE cast(IntColumn as VARCHAR) LIKE '%123%'

Since this feature is not implemented we have to use something like this:

q.Where("cast(IntColumn as VARCHAR) LIKE '%123%'");

Which is not that good because it is not strongly typed. And also for that SqlExpression to work, OrmLiteUtils.IllegalSqlFragmentTokens needs to be modified to exclude dangerous keywords like "cast".

Similar feature in NHibernate: https://nhibernate.jira.com/browse/NH-2563

7 votes
Vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    Farzad Panahi shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    2 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • AdminDemis J Bellot (Developer, ServiceStack) commented  ·   ·  Flag as inappropriate

        I wouldn't accept an implicit cast like this, it's not obvious that there's an implicit transformation and I don't want to encourage using deep C# expressions as only a very limited expression can be translated to SQL and it's not discoverable, so my preferred API would be something like:

        q.Where(x => Sql.Cast<string>(x.IntColumn).Contains("123"))

        But I'd want to add a new API that's pluggable and customizable so others can extend this with their own custom methods, so a composable API might look something like:

        q.Where(x => new[]{ Sql.Cast<string>(x.IntColumn), Sql.Like("123") })

        But I'd have to spend time exploring the different possible API's to work out which is the best approach.

        In the meantime I'd look at different ways you can create a typed API that returns a string, e.g:

        q.Where(x => SqlUtils.Contains(x.IntColumn, "123"))

        Which is just a custom method that returns a Custom SQL like:

        return "CAST({0} as VARCHAR) LIKE '%123%'.Fmt(propName.SqlColumn())

      • Farzad Panahi commented  ·   ·  Flag as inappropriate

        Because of this missing feature, we end up having pretty ugly code :(

        Could you give some hints on implementing this feature? Where do I start?

      Feedback and Knowledge Base