My C# application hangs on closing main form at components.dispose().

I have written an application for a client.  This is my first time using Linq to SQL.  The application runs and closes normally unless I do a very large query to a datagridview.  If I do a query that brings ~500 records into the datagridview everything works normally.  If the query returns ~2000 records it takes a couple of seconds to close the application.  If I query ~8000 records it takes 10-15 seconds to close.  The full query of 24000 records takes several minutes to close the form.  It always hangs at components.Dispose() in the form Dispose method.

The main Linq to SQL query is wrapped in a "using" statement and is used as the datasource for a bindingsource linked to a datagridview.  It appears that the data context and the bindingsource are both already disposed before the code where the application hangs.  I tried disposing the datagridview that held all the records manually before the call to components.dispose() and it disposes easily.  I can't figure out what component it is that is hanging the dispose method, and when I try to step through it in debug in VS2008 it steps over the actual dispose code so I can't see which component hangs it.

Anything that points me in a new direction would be appreciated.

PS - I just remembered.  Even if I run a small query subsequent to a large query so that the datagridview only has a few records in it at the time it is closed, it still takes just as long to close.  It does not matter how many records are currently bound to the control.  Once a large query is performed, it will take a long time to close that session.
njwtechAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PockyMasterCommented:
Could you provide parts of your code (e.g. your Dispose implementation as well as your Linq Query?)

The linq context should have been disposed as soon as scope leaves the using-scope.
0
njwtechAuthor Commented:
Here is the Linq to SQL query.  I added the Dispose line in an attempt to solve this problem.
using (jobsDataContext dbo = new jobsDataContext(Properties.Settings.Default.myConnString))
{
    var predicate = PredicateBuilder.True<load>();
    if (currentyearCheckBox.Checked)
        predicate = predicate.And(s => s.jobdate.Value.Year == Convert.ToInt32(currentyearComboBox.SelectedItem.ToString()));
    if (!inactiveCheckBox.Checked)
        predicate = predicate.And(s => s.active == true);
    if (dayofweek < 8)
        predicate = predicate.And(s => Convert.ToInt32(Convert.ToDateTime(s.jobdate).DayOfWeek) == dayofweek);
    if (!completedCheckBox.Checked)
        predicate = predicate.And(s => s.complete == false);
    if (!canceledCheckBox.Checked)
        predicate = predicate.And(s => s.canceled == false);
    if (!futureCheckBox.Checked)
        predicate = predicate.And(s => s.jobdate < DateTime.Now.AddDays(6));
    dbo.Connection.Open();
    var loadsource =
        (from lds in dbo.loads
        select lds).Where(predicate).OrderBy(l => l.loadid).ThenBy(j => j.jobdate);
    loadBindingSource.DataSource = loadsource;
    loadBindingSource.ResetBindings(false);
    dbo.Connection.Close();
    dbo.Dispose();
    jobsDataGridView.Focus();
}

Open in new window

0
njwtechAuthor Commented:
Adding a dispose call manually for either the loadBindingSource or the datacontext in the FormClosing event throws an exception because it says it is already disposed.  Adding a manual dispose for the datagridview in the same place disposes it easily and cleanly, and then the subsequent Component.Dispose() still hangs on something for a long time before releasing it.  Thanks for your help.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

PockyMasterCommented:
You don't need to do
  dbo.Connection.Close();
    dbo.Dispose();

This is done by the using block. Every object created using a using-block will be disposed automatically.
You also don't need to open the connection yourself, that's already taken care of.
(don't need dbo.Connection.Open();)

But... the objects that are bound were created in a disposed context and that could get you into trouble when used later.
And I would normally enumerate the datasource before binding it (e.g.  loadBindingSource.DataSource = loadsource.ToArray();)

0
CodeCruiserCommented:
Try changing
loadBindingSource.DataSource = loadsource;

to

loadBindingSource.DataSource = loadsource.ToList();

Also remove the unnecessary code as mentioned by PockyMaster.
0
njwtechAuthor Commented:
Thank you.  I should mention that I didn't start out with this configuration.  When I noticed the slow dispose I wrapped the code with the using context.  I also added the Connection.OPen(), Connection.Close() and the dbo.Dispose().  I will try the ToArray() though and report back.  Thanks for the help.
0
CodeCruiserCommented:
The reason I suggested to use ToList or ToArray is that LINQ uses Lazy Loading which means it only tries to load stuff when required. If the context is disposed by that time, you will have problems.
0
njwtechAuthor Commented:
Thanks for the advice.  I tried it - unfortunately it was not successful.  I will however take the advice about ToList and ToArray for future.  But I did find something interesting.  This wasn't happening before some code that I wrote Friday.  I just have to compare the versions of the code and figure out where I went wrong.  I will report the results of that as well.  Thanks again.  
0
PockyMasterCommented:
Ok, let me know when you discovered your "interesting" part.

If you get into trouble with the disposed context, you can move it to your form scope (as a private member variable e.g.) Then you are going to have to dispose it yourself on form close.
0
njwtechAuthor Commented:
I was wrong.  I had not selected "All" records in the older code.  When I select all the records so that it has 24000 records, it too hangs on the components disposal.  I have made the attached changes to the selection code to basically take it back where I started.  The code is used to fill a datagridview so that the user can choose a job from the list and get details about that job in various panels on the form.  The datagridview has editing turned off and is read-only so there are never any changes coming from the list that could go back in the database.  There are no changes waiting for the datacontext to store.  And the hang takes place regardless of whether a record has actually been selected from the list.  It is only necessary to fill the list with a large number of records in order to make it hang.  I tried disposing all the controls on the form using a foreach loop in the FormClosing event and all the controls dispose quickly, but when it finishes that and continues on to the components.Dispose() code it hangs.  I have not found a way to enumerate the components and dispose of them individually to be able to isolate the component that is hanging.  The timing is like this:

Query finds 2000 records - shutdown takes 1.8 seconds.
Query finds 4300 records - shutdown takes ~20 seconds.
Query finds ~8500 records - shutdown takes ~27 seconds.
Query finds ~24000 records - shutdown takes ~4 minutes, 38 seconds.

Thank you very much for your assistance and advice to date.
private void load_Grid()
{
    var predicate = PredicateBuilder.True<load>();
    if (currentyearCheckBox.Checked)
        predicate = predicate.And(s => s.jobdate.Value.Year == Convert.ToInt32(currentyearComboBox.SelectedItem.ToString()));
    if (!inactiveCheckBox.Checked)
        predicate = predicate.And(s => s.active == true);
    if (dayofweek < 8)
        predicate = predicate.And(s => Convert.ToInt32(Convert.ToDateTime(s.jobdate).DayOfWeek) == dayofweek);
    if (!completedCheckBox.Checked)
        predicate = predicate.And(s => s.complete == false);
    if (!canceledCheckBox.Checked)
        predicate = predicate.And(s => s.canceled == false);
    if (!futureCheckBox.Checked)
        predicate = predicate.And(s => s.jobdate < DateTime.Now.AddDays(6));
    if (dispatchComboBox.SelectedIndex > 0)
    {
        ComboItem obj = (ComboItem)dispatchComboBox.SelectedItem;
        predicate = predicate.And(t => t.dispatchid == obj.Value);
    }
    var loadsource =
        (from lds in jobContext.loads
        select lds).Where(predicate).OrderBy(l => l.loadid).ThenBy(j => j.jobdate);
    loadBindingSource.DataSource = loadsource.ToArray();
    jobsDataGridView.Focus();
}

Open in new window

0
CodeCruiserCommented:
I learned the lession in a hard way. I had a similar approach to loading data when using LINQ. The program became very slow when dealing with 00s of rows let alone 24000 rows. When I switched to stored procedures, I reduced the load time of some forms from 4-5 seconds to 0.05 - 0.10 seconds. So my suggestion to you is to try and use stored procedures. You may think that building a complex query would be difficult in SP but it does payoff!
0
njwtechAuthor Commented:
When it loads the records it takes 6.1 seconds to return the datagridview full of 24000 records - not fast but not a problem from the user perspective.  The old software used to take minutes to generate the same list in a web based application.

I can see where a stored procedure might help to make that load even faster, and I will certainly try anything that might solve my issue.  Please don't misunderstand my question, but before I go down that road do you think that the stored procedure approach will help me with the disposal problem as well as loading the datagridview faster?
0
CodeCruiserCommented:
I dont fully understand your application but I think your problem is related to LINQ. I can comfortably say that it would be solved by SP. As a test, make a backup of the form, change code to use a simple stored procedure (without any filter), remove the LINQ code and see how it goes.
0
njwtechAuthor Commented:
Finally!!  I tried using the stored procedure with a datatable, but it made no difference.  But I found the cause of the dispose() issue.  I had used two datagridviewComboBox columns which used ID numbers from the load data to call up joined records.  When I took out the combobox columns and bound the datagridview to a completely flat table in the datacontext using only textbox columns in the datagridview it started disposing immediately.  In the end it must have been all those joins that were holding a bunch of connections open for possible changes to be submitted, and it just took a really long time to check each one and dispose of it.  I even tried changing the application to all datatables and stored procedures instead of Linq for all the tables, but in the end they all worked pretty much the same (the Linq queries were slightly faster than datatable.Fill()).  Thanks for your suggestions and good advice.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CodeCruiserCommented:
Glad that your problem is sorted :-)
0
PockyMasterCommented:
@ slow linq performance when querying thousands of records:

I have programs querying ten thousands of records within seconds using Linq. You just have got to make sure you use the proper dataloadoptions.
0
CodeCruiserCommented:
>You just have got to make sure you use the proper dataloadoptions.
True. But when loading just lists, I use stored procedures now anyway because Dynamic SQL is still not as good as a compiled stored procedure.
0
PockyMasterCommented:
There's no real gain on using a SP. SP's are not pre-compiled. If you're lucky it is still in cache on the SQL server the next time you run it. In Linq you can create a compiled query though, making it less dynamic. More of a religious debate though :D

I use SP's only when there's a lot of DB crunching only and one-hit execution going on. If I need to do simple CRUD, I'd rather use LinqToSQL or when speed requires, compile my LinqToSQL query.
0
CodeCruiserCommented:
>compile my LinqToSQL query
If you mind explaining how its done, this would help me as well.
0
PockyMasterCommented:
e.g.
http://blogs.msdn.com/wriju/archive/2009/05/05/linq-to-sql-understanding-compiled-query.aspx

What is done, is keeping a compiled version of your query in memory and wrapping that in a Func.
The CompiledQuery.Compile method will do the translation from Linq to SQL for you once, and you will be able to call the resulting Func multiple times with different parameters.

Google CompiledQuery.Compile for more examples

0
CodeCruiserCommented:
Hmm. That is compiled query from .NET perspective but its still a dynamic query from SQL Server perspective. I was never a fan of stored procedures myself but I saw the difference in performance (let alone security etc) in a recent project and embraced stored procedures. I use LINQ for retrieving single entities etc but when it comes to retrieving lists of entities, I use stored procedures.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.