Result set is discarded : New database transaction was started

[Chris]

I am getting the 'Result set is discarded : New database transaction was started' error in code. I am retrieving data from DataAccess using the below LINQ query and then in code I am just filtering and looping through the data in a MVC view. I am NOT doing any updates to the data at all. The page was working properly before but now is not working. Updates were made to the DataAccess rlinq file this morning but none that affected the table in question.

Please help resolve the issue!

 

return (from f in dbContext.Fundraisers
    where f.Status == 'A'
    orderby f.Name
    select new ViewModels.Site.FundraiserSummary
    {
         FundraiserID = f.FundraiserID,
         CustomURL = f.CustomURL,
         EventType = f.EventID.HasValue ? f.Event.EventType.TypeName : "",
         FundraiserTypeID = f.FundraiserTypeID,
         Name = f.Name,
         IsFeatured = f.IsFeatured,
         Picture = f.Picture,
         TotalRaised = dbContext.GetTotalRaisedForFundraiser(f.FundraiserID)
     });

[Chris]

I should note that the error occurs on the first function in the view:

 

@if (Model.Any(m => m.IsFeatured == true))

[Viktor Zhivkov]

Hi Chris,

I am guessing here, but if dbContext.GetTotalRaisedForFundraiser(...) is a call to a stored procedure or database function, then it may be causing the trouble.
You can easily check by commenting out the last line of your LINQ query projection and running the steps to reproduce the issue. 

If I am correct another question is how you have mapped GetTotalRaisedForFundraiser function if it is mapped as Domain Method, you will be facing some performance issues as it will be executed for each returned record (aka N+1 problem). You can fix that by mapping the same function again as Database Function. This will enable our LINQ translation to inline the call in the generated SQL and give you better performance.

As secondary way to solve the issue is to disable Read Without Transaction option by setting it to false.
My personal preference will be to use the first approach as it will be more efficient.

If you need any further assistance do not hesitate to contact us.

Regards,
Viktor Zhivkov
Telerik

 

[Chris]

Thanks Viktor. I went ahead with the suggested approach and that worked get for that issue. My issue now is if there is a subquery that uses a scalar database function. For the below query I get a NotImplementedException since the child query is not loaded initially. Is there a way to have this work?

Thanks

 

return (from f in dbContext.Fundraisers
 let totalRaised = EntitiesModel.CFKCDB.GetTotalRaisedForFundraiser(f.FundraiserID)
 where f.Status == 'A'
 orderby f.Name
 select new ViewModels.Site.FundraiserSummary
 {
      FundraiserID = f.FundraiserID,
      CustomURL = f.CustomURL,
      EventType = f.EventID.HasValue ? f.Event.EventType.TypeName : "",
      FundraiserTypeID = f.FundraiserTypeID,
      Name = f.Name,
      IsFeatured = f.IsFeatured,
      TotalRaised = totalRaised,
     Fundraisers = (from c in f.Fundraisers
    let childTotalRaised = EntitiesModel.CFKCDB.GetTotalRaisedForFundraiser(c.FundraiserID)
     where c.Status == 'A'
     orderby c.Name
     select new ViewModels.Site.FundraiserDetailChildSummary
     {
        FundraiserID = c.FundraiserID,
         EventType = c.EventID.HasValue ? c.Event.EventType.TypeName : "",
         FundraiserTypeID = c.FundraiserTypeID,
         Name = c.Name,
         CustomURL = c.CustomURL,
         AllowChildren = c.AllowChildren,
         TotalRaised = childTotalRaised
     }),
  });

[Viktor Zhivkov]

Hi Chris,

Any sub-query that you are doing in the Select clause will be evaluated and executed in memory. In best case scenario you will get N+1 database queries, in worst the exception that you are seeing.
One way that might be applicable in your scenario is to get a flat list of all Fundraisers in single LINQ (and database query) and then post-process the list into a tree. I am afraid that I am lacking some domain knowledge about your model and requirements so I am unable to create such query for you.
Another way is to move the complete query to a stored procedure and use a new type for the result tree.
Third option can be to load first level in one query and then do another one for all ChildSummaries or even load the child summaries on demand if you expect to have more than 200-300 top level Fundraiser instances.

Regards,
Viktor Zhivkov
Telerik