igor-tkachev/bltoolkit — Issue #80: Critical: After Upgrade: Dbmanager opening connection for simple SQL... without need.
Author: NetTecture
Created: 2012-03-09 14:53:46+00:00
State: open

...which blows on our end (not allowed to open additional connectiosn by admins). We also reconfigure conenction default schema after opening, which BlToolkit does not - but that is not relevant here, it just makes the issue obvious. The following code - when activating the commented out WHERE clause - blows because it uses a new connection. Breakpoint in OpenConnection is triggered.

We ugpradeed from a 4.0 version to the current 4.1.8 and are currently rolling back for exact this reason - blows our code.

The following sequence:

var c1 = DataManager.Get().First();
System.Diagnostics.Debug.Assert(c1 != null);
var c2 = DataManager.Get().First();
System.Diagnostics.Debug.Assert(c2 != null);

var insNr = trade.InstrumentNumber;
var sideNr = side.ActualSideId.Value;

foreach (var dbleg in DataManager.Get()
.GroupJoin(DataManager.Get (),
x => new { x.InsNum, x.ParamSeqNum, LegId = x.ProfileSeqNum},
y => new { y.InsNum, y.ParamSeqNum, LegId = y.OptionSeqNum },
(x, y) => new { Profile = x, InsOption = y }
)
.SelectMany(
y => y.InsOption.DefaultIfEmpty(),
(x, y) => new { x.Profile, InsOption = x.InsOption.FirstOrDefault() }
)
//.Where(x => x.Profile.InsNum == insNr && x.Profile.ParamSeqNum == sideNr)
.OrderBy(x => x.Profile.ProfileSeqNum)
)
{

When the Where clause is removed, the SQL statement is successfully submited to the database. The DbManager has an always open connection.

Now, this is a simple join with aprameters. NOTHING in there asks for a secondary cnonection, especially not the where clause. In act I dare saying that as the resulting SQL is (with where clause):

This runs the whole statement on one connection only. No in memory merging. As such, the opening of a connection in BlToolkit is a code error. There is no need for an already connected DbManager to establish another connection.

The stack trace for opening the connection is:

>	BLToolkit.4.DLL!BLToolkit.Data.DbManager.OpenConnection() Line 211	C#
[External Code]
BLToolkit.4.DLL!BLToolkit.Data.DbManager.OnInitCommand(System.Data.IDbCommand command) Line 464 + 0x1e bytes	C#
[External Code]
BLToolkit.4.DLL!BLToolkit.Data.DbManager.Parameter(System.Data.ParameterDirection parameterDirection, string parameterName, object value) Line 1553 + 0x1e bytes	C#
BLToolkit.4.DLL!BLToolkit.Data.DbManager.Parameter(string parameterName, object value) Line 1368 + 0x19 bytes	C#
BLToolkit.4.DLL!BLToolkit.Data.DbManager.AddParameter(System.Collections.Generic.ICollection parms, string name, BLToolkit.Data.Sql.SqlParameter parm) Line 160 + 0x27 bytes	C#
BLToolkit.4.DLL!BLToolkit.Data.DbManager.GetParameters(BLToolkit.Data.Linq.IQueryContext query, BLToolkit.Data.DbManager.PreparedQuery pq) Line 146 + 0x1c bytes	C#
BLToolkit.4.DLL!BLToolkit.Data.DbManager.BLToolkit.Data.Linq.IDataContext.SetQuery(BLToolkit.Data.Linq.IQueryContext queryContext) Line 52 + 0x1a bytes	C#
BLToolkit.4.DLL!BLToolkit.Data.Linq.Query.SetCommand(BLToolkit.Data.Linq.IDataContext dataContext, System.Linq.Expressions.Expression expr, object[] parameters, int idx) Line 348 + 0x52 bytes	C#
BLToolkit.4.DLL!BLToolkit.Data.Linq.Query.RunQuery(BLToolkit.Data.Linq.IDataContextInfo dataContextInfo, System.Linq.Expressions.Expression expr, object[] parameters, int queryNumber) Line 327 + 0x5c bytes	C#
BLToolkit.4.DLL!BLToolkit.Data.Linq.Query.Map(System.Collections.Generic.IEnumerable data, BLToolkit.Data.Linq.QueryContext queryContext, BLToolkit.Data.Linq.IDataContextInfo dataContextInfo, System.Linq.Expressions.Expression expr, object[] ps, System.Func mapper) Line 988 + 0x200 bytes	C#
[External Code]
BLToolkit.4.DLL!BLToolkit.Data.Linq.Builder.GroupByBuilder.GroupByContext.Grouping<<>f__AnonymousType49,Rwe.Glori.Adapters.EndurAdapters.Database.InsOption>.GetItems() Line 180 + 0xfc bytes	C#
BLToolkit.4.DLL!BLToolkit.Data.Linq.Builder.GroupByBuilder.GroupByContext.Grouping<<>f__AnonymousType49,Rwe.Glori.Adapters.EndurAdapters.Database.InsOption>.GetEnumerator() Line 191 + 0x1a bytes	C#
[External Code]
BLToolkit.4.DLL!BLToolkit.Data.Linq.Query<<>f__AnonymousType4a>.Map(System.Collections.Generic.IEnumerable data, BLToolkit.Data.Linq.QueryContext queryContext, BLToolkit.Data.Linq.IDataContextInfo dataContextInfo, System.Linq.Expressions.Expression expr, object[] ps, System.Funcf__AnonymousType4a> mapper) Line 989 + 0xbf bytes	C#

The final stack trace of the error is:

BLToolkit.Data.DataException was unhandled by user code
Message=Verbindung ist schon geöffnet (Translation: Connection is arleady open)
Source=BLToolkit.4
StackTrace:
at BLToolkit.Data.DbManager.OnOperationException(OperationType op, DataException ex) in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\DbManager.cs:line 593
at BLToolkit.Data.DbManager.HandleOperationException(OperationType op, Exception ex) in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\DbManager.cs:line 4418
at BLToolkit.Data.DbManager.ExecuteOperation(OperationType operationType, Action operation) in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\DbManager.cs:line 4384
at BLToolkit.Data.DbManager.OpenConnection() in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\DbManager.cs:line 211
at BLToolkit.Data.DbManager.get_Connection() in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\DbManager.cs:line 186
at BLToolkit.Data.DbManager.OnInitCommand(IDbCommand command) in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\DbManager.cs:line 464
at BLToolkit.Data.DbManager.get_SelectCommand() in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\DbManager.cs:line 401
at BLToolkit.Data.DbManager.get_Command() in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\DbManager.cs:line 385
at BLToolkit.Data.DbManager.Parameter(ParameterDirection parameterDirection, String parameterName, Object value) in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\DbManager.cs:line 1553
at BLToolkit.Data.DbManager.Parameter(String parameterName, Object value) in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\DbManager.cs:line 1368
at BLToolkit.Data.DbManager.AddParameter(ICollection`1 parms, String name, SqlParameter parm) in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\DbManager.Linq.cs:line 160
at BLToolkit.Data.DbManager.GetParameters(IQueryContext query, PreparedQuery pq) in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\DbManager.Linq.cs:line 146
at BLToolkit.Data.DbManager.BLToolkit.Data.Linq.IDataContext.SetQuery(IQueryContext queryContext) in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\DbManager.Linq.cs:line 52
at BLToolkit.Data.Linq.Query`1.SetCommand(IDataContext dataContext, Expression expr, Object[] parameters, Int32 idx) in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\Linq\Query.cs:line 348
at BLToolkit.Data.Linq.Query`1.d__11.MoveNext() in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\Linq\Query.cs:line 327
at BLToolkit.Data.Linq.Query`1.d__60.MoveNext() in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\Linq\Query.cs:line 988
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at BLToolkit.Data.Linq.Builder.GroupByBuilder.GroupByContext.Grouping`2.GetItems() in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\Linq\Builder\GroupByBuilder.cs:line 180
at BLToolkit.Data.Linq.Builder.GroupByBuilder.GroupByContext.Grouping`2.GetEnumerator() in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\Linq\Builder\GroupByBuilder.cs:line 191
at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
at lambda_method(Closure , QueryContext , IDataContext , IDataReader , Expression , Object[] )
at BLToolkit.Data.Linq.Query`1.d__60.MoveNext() in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\Linq\Query.cs:line 989
at Rwe.Glori.Adapters.EndurAdapters.TradeAdapter.GetSideLegs(Trade trade, TradeSide side) in D:\Work\GloRi\trunk\vs\Rwe.Glori\Rwe.Glori.Adapters.EndurAdapters\TradeAdapter.cs:line 670
at Rwe.Glori.Adapters.EndurAdapters.TradeAdapter.FillLegs(Trade trade, TradeSide side) in D:\Work\GloRi\trunk\vs\Rwe.Glori\Rwe.Glori.Adapters.EndurAdapters\TradeAdapter.cs:line 382
at Rwe.Glori.Adapters.EndurAdapters.TradeAdapter.FillSides(Trade trade) in D:\Work\GloRi\trunk\vs\Rwe.Glori\Rwe.Glori.Adapters.EndurAdapters\TradeAdapter.cs:line 364
at Rwe.Glori.Adapters.EndurAdapters.TradeAdapter.EnrichTrade(Trade trade, Header header, PwrPhyParam pwrPhyParam, InsOption insOption, PhysHeader physHeader, Instruments instrument, Int32 insClass, Boolean evaluateSidesAndInfos) in D:\Work\GloRi\trunk\vs\Rwe.Glori\Rwe.Glori.Adapters.EndurAdapters\TradeAdapter.cs:line 520
at Rwe.Glori.Adapters.EndurAdapters.TradeAdapter.DoProcess(TradeRequest request) in D:\Work\GloRi\trunk\vs\Rwe.Glori\Rwe.Glori.Adapters.EndurAdapters\TradeAdapter.cs:line 106
at Rwe.Glori.Adapters.Shared.Adapter`1.Process(Request request) in D:\Work\GloRi\trunk\vs\Rwe.Glori\Rwe.Glori.Adapters.Shared\Adapter,T.cs:line 38
at Rwe.Glori.Adapters.EndurAdapters.Tests.EndurTradeAdapterTests.ProcessRequest() in D:\Work\GloRi\trunk\vs\Rwe.Glori\Rwe.Glori.Adapters.EndurAdapters.Tests\TradeAdapterTests.cs:line 80
InnerException: System.InvalidOperationException
Message=Verbindung ist schon geöffnet
Source=Oracle.DataAccess
StackTrace:
at Oracle.DataAccess.Client.OracleConnection.Open()
at BLToolkit.Data.DbManager.ExecuteOperation(OperationType operationType, Action operation) in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\DbManager.cs:line 4379
InnerException:

Even better, when I bring the query into the original form (which started it all):

foreach (var dbleg in DataManager.Get()
.GroupJoin(DataManager.Get (),
x => new { x.InsNum, x.ParamSeqNum, LegId = x.ProfileSeqNum},
y => new { y.InsNum, y.ParamSeqNum, LegId = y.OptionSeqNum },
(x, y) => new { Profile = x, InsOption = y }
)
.SelectMany(
y => y.InsOption.DefaultIfEmpty(),
(x, y) => new { x.Profile, InsOption = x.InsOption.FirstOrDefault() }
)
.Where(x => x.Profile.InsNum == trade.InstrumentNumber && x.Profile.ParamSeqNum == side.ActualSideId.Value)
.OrderBy(x => x.Profile.ProfileSeqNum)
)

(note that trade and side are input parameters defined before), then the connection tried to be used is closed (? this makes no sense) and being opened.

BLToolkit.Data.DataException was unhandled by user code
Message=ORA-00942: table or view does not exist
Source=BLToolkit.4
StackTrace:
at BLToolkit.Data.DbManager.OnOperationException(OperationType op, DataException ex) in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\DbManager.cs:line 593
at BLToolkit.Data.DbManager.HandleOperationException(OperationType op, Exception ex) in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\DbManager.cs:line 4418
at BLToolkit.Data.DbManager.ExecuteOperation[T](OperationType operationType, Func`1 operation) in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\DbManager.cs:line 4404
at BLToolkit.Data.DbManager.ExecuteReaderInternal(CommandBehavior commandBehavior) in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\DbManager.cs:line 607
at BLToolkit.Data.DbManager.ExecuteReader(CommandBehavior commandBehavior) in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\DbManager.cs:line 3600
at BLToolkit.Data.DbManager.ExecuteReaderInternal() in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\DbManager.cs:line 602
at BLToolkit.Data.DbManager.ExecuteReader() in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\DbManager.cs:line 3587
at BLToolkit.Data.DbManager.BLToolkit.Data.Linq.IDataContext.ExecuteReader(Object query) in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\DbManager.Linq.cs:line 255
at BLToolkit.Data.Linq.Query`1.d__11.MoveNext() in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\Linq\Query.cs:line 329
at BLToolkit.Data.Linq.Query`1.d__60.MoveNext() in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\Linq\Query.cs:line 988
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at BLToolkit.Data.Linq.Builder.GroupByBuilder.GroupByContext.Grouping`2.GetItems() in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\Linq\Builder\GroupByBuilder.cs:line 180
at BLToolkit.Data.Linq.Builder.GroupByBuilder.GroupByContext.Grouping`2.GetEnumerator() in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\Linq\Builder\GroupByBuilder.cs:line 191
at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
at lambda_method(Closure , QueryContext , IDataContext , IDataReader , Expression , Object[] )
at BLToolkit.Data.Linq.Query`1.d__60.MoveNext() in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\Linq\Query.cs:line 989
at Rwe.Glori.Adapters.EndurAdapters.TradeAdapter.GetSideLegs(Trade trade, TradeSide side) in D:\Work\GloRi\trunk\vs\Rwe.Glori\Rwe.Glori.Adapters.EndurAdapters\TradeAdapter.cs:line 667
at Rwe.Glori.Adapters.EndurAdapters.TradeAdapter.FillLegs(Trade trade, TradeSide side) in D:\Work\GloRi\trunk\vs\Rwe.Glori\Rwe.Glori.Adapters.EndurAdapters\TradeAdapter.cs:line 382
at Rwe.Glori.Adapters.EndurAdapters.TradeAdapter.FillSides(Trade trade) in D:\Work\GloRi\trunk\vs\Rwe.Glori\Rwe.Glori.Adapters.EndurAdapters\TradeAdapter.cs:line 364
at Rwe.Glori.Adapters.EndurAdapters.TradeAdapter.EnrichTrade(Trade trade, Header header, PwrPhyParam pwrPhyParam, InsOption insOption, PhysHeader physHeader, Instruments instrument, Int32 insClass, Boolean evaluateSidesAndInfos) in D:\Work\GloRi\trunk\vs\Rwe.Glori\Rwe.Glori.Adapters.EndurAdapters\TradeAdapter.cs:line 520
at Rwe.Glori.Adapters.EndurAdapters.TradeAdapter.DoProcess(TradeRequest request) in D:\Work\GloRi\trunk\vs\Rwe.Glori\Rwe.Glori.Adapters.EndurAdapters\TradeAdapter.cs:line 106
at Rwe.Glori.Adapters.Shared.Adapter`1.Process(Request request) in D:\Work\GloRi\trunk\vs\Rwe.Glori\Rwe.Glori.Adapters.Shared\Adapter,T.cs:line 38
at Rwe.Glori.Adapters.EndurAdapters.Tests.EndurTradeAdapterTests.ProcessRequest() in D:\Work\GloRi\trunk\vs\Rwe.Glori\Rwe.Glori.Adapters.EndurAdapters.Tests\TradeAdapterTests.cs:line 80
InnerException: Oracle.DataAccess.Client.OracleException
Message=ORA-00942: table or view does not exist
Source=Oracle Data Provider for .NET
ErrorCode=-2147467259
DataSource=( DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=s989s3210-v.rwe.com)(PORT=1523)) (ADDRESS=(PROTOCOL=TCP)(HOST=s989s3220-v.rwe.com)(PORT=1523)) (ADDRESS=(PROTOCOL=TCP)(HOST=s989s3230-v.rwe.com)(PORT=1523)) (ADDRESS=(PROTOCOL=TCP)(HOST=s989s3240-v.rwe.com)(PORT=1523)) (LOAD_BALANCE=yes)) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=ENDUR1P_TAF.RWE.COm) (FAILOVER_MODE=(TYPE=SELECT) (METHOD=BASIC)(RETRIES=180)(DELAY=5))) )
Number=942
Procedure=""
StackTrace:
at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck)
at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
at Oracle.DataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at BLToolkit.Data.DbManager.<>c__DisplayClass12.b__11() in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\DbManager.cs:line 610
at BLToolkit.Data.DbManager.ExecuteOperation[T](OperationType operationType, Func`1 operation) in D:\Work\GloRi\trunk\vs\BlToolkit\Source\Data\DbManager.cs:line 4396
InnerException:

Plus a call to connection open.

Conclusions:

There is something broken with connection opening / closing here.
* On the first error, BlToolkit tries to open an already open connection wrongly.
* On the second code version, a closed connection is opened. The code makes clear that BlToolkit should ahve no need for it - for some reason the connecton was closed without my intervention and knowledge - which is inefficient and not callede for. Originalyl the Dbmanager was created with an IDbConnection and I do NOT expect it to close it without an error or me asking..

Last test: I wrap everything in begin / commit transaction on bltoolkit level.

The same call to OpenConnection. The connection is closed. This is now not only an error (as inefficient) as in by chance, ti is illegal (as in voiding the transaction contract) because this means the open transaction had been commited or rolled back.