Result set is discarded : New database transaction was started

5 posts, 2 answers
  1. Chris
    Chris avatar
    3 posts
    Member since:
    Nov 2010

    Posted 03 Feb Link to this post

    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)
          }); 

  2. Chris
    Chris avatar
    3 posts
    Member since:
    Nov 2010

    Posted 04 Feb in reply to Chris Link to this post

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

     

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

  3. DevCraft banner
  4. Answer
    Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 08 Feb Link to this post

    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
     
    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
  5. Chris
    Chris avatar
    3 posts
    Member since:
    Nov 2010

    Posted 09 Feb in reply to Viktor Zhivkov Link to this post

    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
         }),
      });

  6. Answer
    Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 12 Feb Link to this post

    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
     
    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
Back to Top
DevCraft banner