Is accessing rows with varbinary data via LINQ causing a timeout? How to fix?

Question

I have a number of tables in SQL. One is Controls (a typical CRUD sort of object) and one is Attachments. Attachments references Controls via a FK (there can be many attachments). Attachments also includes, amongst other things, a name and a varbinary column with file data.

Through linq, Control has an Attachments property.

I have a Controls view (MVC) that displays a lot of information, including a listing of the existing attachments. That listing is done via a helper method:

public static string FileBox(this HtmlHelper helper, string name, IEnumerable<Models.Attachment> files, bool writable)
    { ... }

This function loops through the attachments and writes out a unordered list with the attachment names.

Infrequently, I get a timeout error, and here is a snippet of that error:

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

...

at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader`2.MoveNext()
   at System.Data.Linq.EntitySet`1.Load()
   at System.Data.Linq.EntitySet`1.GetEnumerator()
   at System.Linq.Enumerable.WhereEnumerableIterator`1.MoveNext()
   at IRSoxCompliance.Helpers.Html.FileBox(HtmlHelper helper, String name, IEnumerable`1 files, Boolean writable) in C:\Documents and Settings\Administrator\My Documents\Visual Studio 2008\Projects\IRSoxCompliance\IRSoxCompliance\IRSoxCompliance\Helpers\Html.cs:line 228
   at ASP.views_edit_control_edit_test_aspx.__Render__control1(HtmlTextWriter __w, Control parameterContainer) in c:\inetpub\wwwroot\Views\Edit\Control_Edit_Test.aspx:line 109

...

So...

1. Am I correct to assume that these timeouts are due to the fact that, while the control has already been loaded, the Attachments rows are lazy-loaded, and only being loaded from the helper? And that this is mostly due to the fact that I'm grabbing maybe 50 mb of data that I don't need?

2. How do I prevent this? a) I'd like to avoid splitting the table. b) Can I create a AttachmentsNoBinary partial property on the Control that returns a new class that has everything but the binary? c) It appears I can turn on 'delay loaded' on just the binary column. Will this work? If so -- I've made it a point of not changing anything in the DBML, because I have a habit of clearing a table and then reloading. So I'd lose this setting. Is there any way to make sure I don't lose it? Can I set it from my partial? Or maybe a unit test that can assert that it's turned on?

SOLUTION: Based on the answer, I realized that instead of:

foreach (Attachment file in controls.Attachments) {
  response.write(file.name);
}

I can instead do:

foreach (string filename in controls.Attachments.Select(a => a.name)) {
  response.write(filename);
}

Though I ended up defer-loading the varbinary column, hoping that I don't forget to set that option again should I reset my dbml file.

Thanks, James

Answer 1

Hi James,

1. You are right that Linq is lazy loading your attachments. Whether or not it is dragging the varbinary data down from the database depends on the query. Can you post it?

2. You don't need to change the structure of your database to sort out the problem. Make sure that your query isn't touching the varbinary field - you'll need an appropriate select/projection to ensure this. Also if you know that you are always going to get all the attachments related to a controller you could use LoadWith to instruct Linq to get it all at once rather than lazy loading on demand. This will cut down the number of database round trips required to get the job done.

LoadWith info on MSDN

Also worth checking that your indexes are ok on the tables.

To get some real insight into what is going on point SQL Query Analyser at your database and then run the code. You'll see exactly what SQL is hitting the database and can grab and run it in Management Studio to see exactly what data is getting dragged back.

Hope this helps a bit.