Connection not reused when working with MSSQL and latest build of Telerik.Data

[Norbert]

Hi, I'm using Open Access for some years now and today I upgraded from 2012.Q2 to 2015.Q1 and now I have some major problems.

I retrieve some data from Sql Server 2008R2 and fill up an Excel sheet. No problems over 3 years.
This is the method I've been using all the time (some modifications now, because I wantetd to test)

01.public IEnumerable<TSCalc> LoadByCustomer(int calcversionId, int customerId, int typeId)
02.{
03.            string sql = "Select a.TSCalcId As TSCalcId\n" +
04.                         "From dbo.tblTSCalc a\n" +
05.                         "Inner Join dbo.tblTSCalcHead b\n" +
06.                         " On b.TSCalcHeadId = a.TSCalcHeadId_FK\n" +
07.                         "Inner Join dbo.tblCustomerTSCalc c\n" +
08.                         " On c.TSCalcHeadId_FK = a.TSCalcHeadId_FK\n" +
09.                         "Where a.CalcversionId_FK = @CalcversionId\n" +
10.                         " And c.CustomerId_FK = @CustomerId\n" +
11.                         "  And b.TSCalcTypeId_FK = @TypeId";
12.            var calcversion = new OAParameter
13.            {
14.                ParameterName = "@CalcversionId",
15.                Value = calcversionId
16.            };
17.
18.            var customer = new OAParameter
19.            {
20.                ParameterName = "@CustomerId",
21.                Value = customerId
22.            };
23.            var typ = new OAParameter
24.            {
25.                ParameterName = "@TypeId",
26.                Value = typeId
27.            };
28.
29.            IEnumerable<TSCalc> timeseriesCalcList;
30.            using (AbsDbContextNew ctx = new AbsDbContextNew())
31.            {
32.                var f = new FetchStrategy();
33.                f.LoadWith<TSCalc>(t => t.Data);
34.                f.LoadWith<TSCalc>(t => t.Head);
35.                ctx.FetchStrategy = f;
36.                //timeseriesCalcList = ctx.ExecuteQuery<TSCalc>(sql, CommandType.Text, calcversion, customer, typ);
37.                // 2. Retrieve the OAConnection instance.
38.                using ( IDbConnection oaConnection = ctx.Connection )
39.                {
40.                    // 4. Create a new instance of the OACommand class.
41.                    using ( IDbCommand oaCommand = oaConnection.CreateCommand() )
42.                    {
43.                        // 5. Initialize OAParameters
44.                        //IDbDataParameter idParam = oaCommand.CreateParameter();
45.                        //idParam.ParameterName = @"Id";
46.                        //idParam.DbType = System.Data.DbType.Int32;
47.                        //idParam.Value = 1;
48.                        // 6. Set the CommandText property.
49.                        oaCommand.CommandText = sql;
50.                        // 7. Add parameters to the Parameters collection of the command.
51.                        oaCommand.Parameters.Add(calcversion);
52.                        oaCommand.Parameters.Add(customer);
53.                        oaCommand.Parameters.Add(typ);
54.                        // 8. Execute the command.
55.                        using ( IDataReader reader = oaCommand.ExecuteReader() )
56.                        {
57.                            return ctx.Translate<TSCalc>(reader as DbDataReader).ToList();
58.                        }
59.                    }
60.                }
61.
62.
63.            }
64.
65.            //using ( AbsDbContextNew ctx = new AbsDbContextNew() )
66.            //{
67.            //    var f = new FetchStrategy();
68.            //    f.LoadWith<TSCalc>(t => t.Data);
69.            //    f.LoadWith<TSCalc>(t => t.Head);
70.            //    return timeseriesCalcList = ctx.TSCalcs.Where(t => t.VersionId == calcversionId && t.Head.CustomerTSCalcs.);
71.            //}
72.        }

Originally I was using this statement

1.var f = new FetchStrategy();
2.f.LoadWith<TSCalc>(t => t.Data);
3.f.LoadWith<TSCalc>(t => t.Head);
4.f.FetchStrategy = f;
5.timeseriesCalcList = ctx.ExecuteQuery<TSCalc>(sql, CommandType.Text, calcversion, customer, typ);

and returned the timeseriesCalcList object.

 

So the problem is now that the ActiveConnection is set to 10 (was always enough) and after a few requets I can't get any connections anymore.
The code is 100 % identicle with the old release, so please give me an advice how to solve it.

 

I can't do any exports anymore :-(

 

 

This are the settings from the working branch.

 

 

Hwere you can see the profiler views ... look there are all connections used but I have wrapped the context within using statement.

 

 

 Thank you very much,

Norbert

 

[Ralph Waldenmaier]

Hello Norbert,
Thank you for contacting us.
Based on the provided scripts I was able to reproduce the reported behavior using the latest NuGet packages.
In between these versions, there was a change in the default value for 'backendConfiguration.Runtime.AllowReadAfterDispose'. The default is now true. This means in your situation where you are just querying for the id in the sql statement, the user of the resulting IEnumerable is most probably using different properties than the id. This leads to point fetches against the database since those values were not provided in your sql statement. 

Obtaining the non loaded values is only possible in case the AllowReadAfterDispose setting is set to true, since you were already disposing the context/connection with the using block.
If you set this to false, you will get an exception when trying to access properties different than the id while iterating the IEnumerable, since the properties were not populated and the context that was bound to the respective object is already disposed.

The solution for you would be to provide the needed values already within your sql statement and setting the AllowReadAfterDispose to false. This will speedup your application, since no point fetches happen anymore, and you will not have the problem with non available connections.

Please have in mind, that setting the AllowReadAfterDispose could cause other places in your code to show exceptions, where you were using a similar pattern. Those must be fixed as well. Nevertheless, I think that it is the right thing to fix them in order to avoid the unwanted point fetches.

Please accept my apologies for the inconvenience this might cause to you.

I hope this information is helpful for you.
Do come back in case you need further assistance.


Regards,
Ralph Waldenmaier
Telerik

 

 

[Norbert]

Sounds reasonable but why I don't get any exception in my old code? (with 2012)
If you have just changed the boolean value I would get an exception? Instead it works ...

Secondly, like you mentioned, the given fetch strategy should do that ... it should load up all properties from TSCalc with my custom sql. As a short term solution I created a detached copy ... this worked but is slow. My old code exported in 3-5 minutes and the current approach needs 12 minutes.

Of course I could write plain ADO commands and translate it manually ... but I intend to use Telerik.Data as much as I can.

Another point is, that I use the context per Thread within a WinFomrs app ... so when I change to transitive I get the same problems I guess? So I can't do that ?!

 

Thanks for the prompt reply and qualitive answer,
Norbert

[Ralph Waldenmaier]

Hi Norbert,
The fetch strategy will not load the needed properties when calling the low level ado api. The fetch strategy will work on persistent objects when using the context api. Please see this link for more details. 

Attached you can find a sample application implementing your example, but avoiding point fetches and freeing up the connections against the database. While testing this scenario again, I saw that you need to set the AllowReadAfterDispose to true, which is also the default. Alternatively, you can return unbound Product instances that would allow you to set this setting to false. This setup is also included in my example.
Please adjust the connection string in the 'ConnectionTestUsingAdoApiContext' to point to your database.

So my advice would be, when you want to use the low level ado api, then please specify all the columns that you later need within the persistent object as show in my example. Additionally, returning transient objects, that are not bound to a context, would provide a clear separation in case you don't need to further operate on the returned object. 
This should also bring your report back to speed.

Hope this helps.
Feel free to ask in case you have any other question.


Regards,
Ralph Waldenmaier
Telerik