There is already an open DataReader associated with this Command which must be closed first. #5762

mmukkara created 5 years ago

Hi Guys,

This issue may or may not be ANZ issue. Appreciate your help

        var benefitOption = await _benefitOptionRepository.GetAsync(input.Id);

         benefitOption.BenefitOptionPlans = _benefitOptionPlanRepository.GetAll()
            .Where(e => e.BenefitOptionId == input.Id && e.FromDate &lt;= input.AsOfDate)
            .OrderByDescending(e =&gt; e.FromDate)
            .ToList();

        BenefitOptionOutputDto benefitOptionOutputDto = ObjectMapper.Map&lt;BenefitOptionOutputDto&gt;(benefitOption);

        foreach (BenefitOptionPlanOutputDto d in BenefitOptionOutputDto.BenefitOptionPlans)
        {
                var benefitEnrolment = _benefitEnrolmentRepository.GetAll()
                                        .Include(e => e.DependentEnrolmentRules)
                                        .Include(e => e.EmploymentRules)
                                        .Include(e => e.TrappingRules)
                                        .Where(e => e.Id == d.RuleId)
                                        .FirstOrDefault&lt;BenefitEnrolmentRule&gt;();
                d.BenefitEnrolmentRule = ObjectMapper.Map&lt;BenefitEnrolmentRuleOutputDto&gt;(benefitEnrolment);
        }

return benefitOptionOutputDto;

I get "System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first." in the foreach loop.

I googled and determined that this can happen if you execute a query while iterating over the results from another query. Couple of suggestions like

Enabling Multiple Active Result Sets (MARS) - Not sure where I enable this in the ANZ.
Use .ToList() instead of FirstOrDefault(). - I have tried this and all other methods still same issue

I tried different variations to fx this issue with no solution. Realy appreciate your help on this.

Thank you

16 Answer(s)

0

slamj1 created 5 years ago

Hi @mmukkara, you've closed the reader on benefitOption.BenefitOptionPlans by using ToList(), however it looks to me like the reader on benefitOption may still be open on the same command once you reach the foreach loop.

This may be causing the problem, even though you're iterating through BenefitOptionOutputDto.BenefitOptionPlans.
0

mmukkara created 5 years ago

I don't know why it didn't close. Is it because of first line in the code I posted.. var benefitOption = await _benefitOptionRepository.GetAsync(input.Id);

Thanks for the response though.
0
aaron created 5 years ago
Support Team
Show the definitions of:

BenefitOptionOutputDto

BenefitOptionPlanOutputDto

BenefitEnrolmentRuleOutputDto
0

mmukkara created 5 years ago

Hi aaron,

BenefitOptionOutputDto public int TenantId { get; set; } public long Id { get; set; } public string OptionCode { get; set; } public bool IsActive { get; set; } public List<BenefitOptionPlanOutputDto> BenefitOptionPlans { get; set; }

BenefitOptionPlanOutputDto public int TenantId { get; set; } public long Id { get; set; } public long BenefitOptionId { get; set; } public DateTime FromDate { get; set; } public short RuleType { get; set; } public int? RuleId { get; set; } public BenefitEnrolmentRuleOutputDto BenefitEnrolmentRule;

BenefitOptionPlanOutputDto is generic table we have where all the rules are attached. One of the rule is BenefitEnrolmentRuleOutputDto which is identfied by RuleType and RuleId. So there is no FK association defined between RuleId and BenefitEnrolmentRuleOutputDto.

BenefitEnrolmentRuleOutputDto public int TenantId { get; set; } public int Id { get; set; } public string GracePeriod { get; set; } public List<DependentEnrolmentRuleOutputDto> DependentEnrolmentRules { get; set; } public List<EmploymentRuleOutputDto> EmploymentRules { get; set; } public List<TrappingRuleOutputDto> TrappingRules { get; set; }

Hope this helps!

Thanks
0
aaron created 5 years ago
Support Team
Show the definitions of:

DependentEnrolmentRuleOutputDto

EmploymentRuleOutputDto

TrappingRuleOutputDto

mmukkara created 5 years ago

Hi aaron,

Here are the definitions as requested.

DependentEnrolmentRuleOutputDto

        public int TenantId { get; set; }
        public int BenefitEnrolmentRuleId { get; set; }
        public short Dependent { get; set; }
        public string WaitingPeriod { get; set; }
        public short StartDateCalc { get; set; }
        public int TerminationAge { get; set; }
        public short TerminationDateCalc { get; set; }
        public bool isEligible { get; set; }

EmploymentRuleOutputDto

     public int TenantId { get; set; }
        public int BenefitEnrolmentRuleId { get; set; }
        public short EmploymentType { get; set; }
        public decimal MinHours { get; set; }
        public short Frequency { get; set; }

TrappingRuleOutputDto

  public int TenantId { get; set; }
        public int BenefitEnrolmentRuleId { get; set; }
        public short TrappingCondition { get; set; }
        public short CovBeforeDecision { get; set; }

BenefitEnrolmentRuleId is propogated to the above entities from BenefitEnrolmentRuleOutputDto Id.

PS: It works if I changed the signature of the method (removed async Task) from public async Task BenefitOptionOutputDto GetBenefitOption(BenefitOptionGetInputDto input) to public BenefitOptionOutputDto GetBenefitOption(BenefitOptionGetInputDto input) I am not sure that it is the solution. It has to be a better way I guess.

Thanks for quick reply.

0

aaron created 5 years ago
Support Team

Did you await GetBenefitOption properly up the call chain?
0

alper created 5 years ago
Support Team

https://github.com/aspnetboilerplate/aspnetboilerplate/issues/1858
0

mmukkara created 5 years ago

Hi @alper, I read the link before posting this message. I think this issue is not not related to muti-thread.

@aaron, if you look at the first line in the code posted in the orginal message is var benefitOption = await _benefitOptionRepository.GetAsync(input.Id);

Thanks
0

aaron created 5 years ago
Support Team

await GetBenefitOption != await _benefitOptionRepository.GetAsync
0

mmukkara created 5 years ago

Hi @aaron,

Sorry, don't understand your message. Can you please explain a bit

Thanks
0

aaron created 5 years ago
Support Team

How do you call GetBenefitOption?
0

mmukkara created 5 years ago

I am calling this from Angular this._classServiceProxy.getBenefitOption(this.benefitOptionId, moment()).subscribe(result => { this.benefitOption = result;
0

ismcagdas created 5 years ago
Support Team

Hi @mmukkara

Could you share the exact definition of BenefitOptionPlans in BenefitOptionOutputDto ? If it is an entity of yours, please replace it with a DTO class.
0

mmukkara created 5 years ago

Hi @ismcagdas,

It is a Dto class.. public List<BenefitOptionPlanOutputDto> BenefitOptionPlans { get; set; }
0

ismcagdas created 5 years ago
Support Team

Hi @mmukkara

Have you solved the problem ? If not, please share your BenefitOptionPlans class.

Thanks.

Have an answer to this question? Log in and write your answer.