Subsonic SimpleRepository NullReferenceException in Contains with cast

Question

Hi, I have a problem with this subsonic3.0.0.4 find statement:

rep = new SimpleRepository(" ... ");
rep.Find<MyObject>( x => x.Code.ToString("00000").Contains("023") );

The Code field is Long value and the sql query that I need is:

*SELECT * FROM ... WHERE convert(varchar, Code) LIKE '%023%'*

When I execute it, NullReferenceException. Then problem is the Cast to string for the LIKE filter. But i don't kwnon how to resolve it.

The stack trace: at SubSonic.SqlGeneration.ANSISqlGenerator.GenerateConstraints() at SubSonic.SqlGeneration.ANSISqlGenerator.BuildSelectStatement() at SubSonic.Query.SqlQuery.BuildSqlStatement() at SubSonic.Query.SqlQuery.GetCommand() at SubSonic.Query.SqlQuery.ExecuteTypedListT at SubSonic.Repository.SimpleRepository.Find[T](Expression`1 expression) at agf.FormMain.BindGrid() in C:\dev\localhost\AGF\trunk\AGF\FormMain.cs:line 351

Thank you!!

Answer 1

This is a "bug" - SubSonic tries to convert your expression in the where clause to an SQL statement. SubSonic gives it's best to convert the x.Code statement to a string but is not aware - and cannot guarantee for multiple database types - that your toString("00000") will be executed successfully.

The exception could be a bit more clear.

Try to convert your value in the "Contains" statement to something like "00023" and omit the format expression in your ToString - that should be functional equivalent and will work.