search

markrendle / Simple.Data

A light-weight, dynamic data access component for C# 4.0
MIT License
1.33k stars 303 forks source link

Incorrect "In" clause implementation. #378

Open ramankarman opened 8 years ago

ramankarman commented 8 years ago

I found that if subquery for select parameters for In clause returns empty set, Simple.Data throws ArgumentOutOfRangeException with message "startIndex cannot be larger than length of string. Parameter name: startIndex" and stack trace " at System.String.Substring(Int32 startIndex, Int32 length) at Simple.Data.Ado.CommandBuilder.d__10.MoveNext() at Simple.Data.Ado.CommandBuilder.SetParameters(IDbParameterFactory parameterFactory, IDbCommand command, IEnumerable1 parameters) at Simple.Data.Ado.CommandBuilder.CreateCommand(IDbParameterFactory parameterFactory, ICommandBuilder[] commandBuilders, IDbConnection connection, AdoOptions options) at Simple.Data.Ado.AdoAdapterQueryRunner.RunQuery(SimpleQuery query, IEnumerable1& unhandledClauses) at Simple.Data.Ado.AdoAdapter.RunQuery(SimpleQuery query, IEnumerable1& unhandledClauses) at Simple.Data.DatabaseRunner.RunQuery(SimpleQuery query, IEnumerable1& unhandledClauses) at Simple.Data.SimpleQuery.Run() at Simple.Data.SimpleQuery.Cast[T]() at Simple.Data.SimpleQuery.ToList[T]() at System.Dynamic.UpdateDelegates.UpdateAndExecute1[T0,TRet ](CallSite site, T0 arg0) at ....". Is it correct implementations of the In clause?

For example, there are two tables tblA(a_id, a_name) and tblB(b_id, b_name, b_a_id) where b_a_id is FK to the tblA(a_id field) and I want to select all a_name values for tblB objects which have b_name containig "a" sign. The sql query is "SELECT a_name FROM tblA WHERE a_id IN(SELECT b_a_id FROM tblB WHERE b_name LIKE '%a%')". Using Simple.Data, query is DB.tblA.FindAll(DB.tblA.a_id == DB.tblB.FindAll(DB.tblB.b_name.Like("%a%").ToScalarList()).ToScalarList(). If I execute "native" query, sql server returns empty query result, if I execute simple.data query, Simple.Data throws exception instead of returning empty list.

Ie problem is the query result inside "in" clause. If result of the query inside "In" clause is empty set, Simple.Data query throws exception at runtime. This exception is thrown even if I pass empty array to "In" clause to Simple.Data query: DB.tblA.FindAll(DB.tblA.a_id == new int[]{}).ToScalarList()

BenMagyar commented 8 years ago

Simple.Data does not do nested queries, you would be better off just joining the two tables.