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
Farzad Panahi
shared this idea
-
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
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?
