CODEWITHSUNDEEP
asp.netasp.net-mvcazureazure-sql-database
Spionred
Spionred

Reputation: 807

open DataReader error on Azure SQL but not SQL Server 2014

I have found a few posts on this but can't seem to find one that relates to my code.

The following line is highlighted with the error:

Line 74:         <td>
Line 75:             @Html.DisplayFor(modelItem => item.Equipment.ModelName)
Line 76:         </td>

The part of the view rendering the data looks like this:

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.Location)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Position)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Description)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.SerialNo)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.InstallDate)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.ServiceFrequency) Months
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.NextServiceDate)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Equipment.ModelName)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Owner.OwnerName)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.ServiceCompany.CompanyName)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Site.SiteName)
        </td>

        <td>
            @Html.ActionLink("Edit", "Edit", new { id=item.AssetID })
            @Html.ActionLink("Details", "Details", new { id=item.AssetID })
            @Html.ActionLink("Delete", "Delete", new { id=item.AssetID })
        </td>
    </tr>

The controller look like this:

public ActionResult Index()
{
    var userId = User.Identity.GetUserId();
    var asset = from a in db.Assets
                join o in db.Owners on a.OwnerID equals o.OwnerID into oa
                from asst in oa.DefaultIfEmpty()
                join s in db.ServiceCompanies on a.ServiceCompanyID equals s.ServiceCompanyID into sa
                from service in sa.DefaultIfEmpty()
                where asst.RegUserID == userId || service.RegUserID == userId
                select a;

    return View(asset);
}

My understanding I have tried to connect to the database twice however I can't see where and also I don't get this error on my laptop. I feeling is it may be something that's not available in Azure SQL Databases that is fine in SQL Server 2014.

Any thoughts?

UPDATE::::

I have stopped the issue by adding MultipleActiveResultSets=true to the connection string however I am not sure if this is masking an issue that needs to be resolved or maybe introducing other issues.

Upvotes: 2

Views: 59

Answers (1)

flytzen
flytzen

Reputation: 7448

Adding MultipleActiveResultSets=true is fine.

I always set it to true by default - it's generally safer, especially if you use any 3rd party libraries.

However, what is causing this in this particular situation, is that you have a SELECT N+1 problem in your code (where you go back to SQL for each record to retrieve another bit of data).

For example, you render item.Equipment.ModelName which causes the another roundtrip to the server to get that (assuming they are virtual properties). So, you already have an open connection so you can iterate over the result set and you need to run another query on the same connection to get the Equipment entity. This can very easily happen with any ORM and virtual properties when you are iterating over a result set. You can usually resolve it by telling the ORM to pre fetch the properties you need or, better, by doing a projection in your query so SQL gives you all the data you need and nothing more. Better for performance. In your case, instead of select a, do something like 

select new {
   a.Location,
   a.Position
   ....
   EquipmentName = a.Equipment.ModelName
   ....
 }

Now, you can pass that to your view as a dynamic (I think - not sure) but you will probably want to instead create a concrete class with the relevant properties (R# will do that for you) to make it easier to send it to the view.

Easiest way to detect this is to use SQL Server Profiler on a local instance of SQL Server to see exactly which query / queries are sent to SQL Server.

Upvotes: 1

Related Questions