How to ensure the query uses a single context instance?

Question

0.00/5 (No votes)

See more:

Hello,

I am currently facing an error when running a method that could be related to 2 connectoin instances running at the same time but I can't find how is it happening exactly.
The error:

InvalidOperationException: Cannot use multiple context instances within a single query execution. Ensure the query uses a single context instance.

Below is part of the code where I suspect is related to this problem:

C#

var linkedTransactionIds = _transactionRepository.Table.AsNoTracking()
                .Include(t => t.RentPeriodRelations)
                .Where(
                    t =>
                        t.Active
                        && t.EntryStatusId != (int) EntryStatusEnum.Rejected
                        && t.RentAgreementRateId == baseRentAgreementRateId && t.RentPeriodRelations
                            .Select(rpr => rpr.PeriodDate)
                            .OrderBy(d => d)
                            .SequenceEqual(basePeriods))
                .Select(x => x.Id)
                .AsEnumerable();

            var linkedSteps = _approvationStepRepository.Table
                .Include(st => st.Approvation.TransactionDataEntry.RentAgreement.Property.TradeCenter.City.Cluster.Region)
                .Include(st => st.Approvation.TransactionDataEntry.RentAgreementRate.RateCurrencyType)
                .Include(st => st.Approvation.TransactionDataEntry.RentAgreementRate)
                .Include(st => st.Approvation.TransactionDataEntry.Vendor)
                .Include(st => st.Approvation.TransactionDataEntry.Store)
                .Include(st => st.Approvation)
                .Include(st => st.MatrixStep)
                //.Where(st => linkedTransactionIds.Any(step => step == st.Approvation.TransactionDataEntryId))
                .Where(st => linkedTransactionIds.Contains(st.Approvation.TransactionDataEntryId))
                .ToList();

What I have tried:

What I tried is writing the where clause in a different way as shown but it didn't help.
The error seems to appear because of this line:

C#

.Where(st => linkedTransactionIds.Contains(st.Approvation.TransactionDataEntryId))

Removing this where clause from the code will run it with no issues or errors, but ofc, this where clause is needed and can't be removed.

Can someone explain what could be causing this error please or if something in the code is written in a wrong way that might be causing it?

Thank you.

Posted 17-Apr-23 5:12am

xTMx9

Updated 18-Apr-23 4:05am

PIEBALDconsult

v2

Add a Solution

Comments

[no name] 17-Apr-23 13:01pm

Maybe because you're calling an enumerable query from a "list" query.

xTMx9 18-Apr-23 9:40am

There seem to appear the following error on the 1st query: "could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'."
I do have it "AsEnumerable" but still getting this message.
I do not see what's wrong with the code to not be translated.

Richard Deeming 18-Apr-23 3:54am

At a guess, you are injecting a DbContext into your repositories, and you're getting a different instance per repository. That would suggest that you've registered it as a "transient" service instead of a "scoped" service, or you've done something else wrong with the service registration.

But since we can't see your repositories, nor how you are creating them, we can't tell you precisely what the problem is.

xTMx9 18-Apr-23 9:57am

This is a project that I newly took over so my knowledge of it is limited. I have checked the program.cs file and all services are registered as "Transient" in it.
What part of the project could I add to the question in order to make it clearer?
I noticed an error in the first query in the question which I added as a comment above this one.
Could they be connected? and what could be causing it?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2023-04-18T04:05:00

Solution 1

Quote:
I have checked the program.cs file and all services are registered as "Transient" in it.

Well, there's your problem. Services registered as "transient" will have a new instance created every time they are resolved.

You have two (or more) repositories which resolve your DbContext service, so each will receive a different instance. When you try to write a query for data from multiple repositories, you're querying multiple DbContext instances, which isn't supported.

Change your service registration code to register the DbContext as "scoped" instead. If you're using EF Core, this is the default lifetime used by the AddDbContext method:
EntityFrameworkServiceCollectionExtensions.AddDbContext Method (Microsoft.Extensions.DependencyInjection) | Microsoft Learn[^]

DbContext Lifetime, Configuration, and Initialization - EF Core | Microsoft Learn[^]

Posted 18-Apr-23 4:05am

Richard Deeming

Comments

xTMx9 19-Apr-23 4:32am

Thanks for the answer, changing it to scoped resolved that particular issue.
The main problem now is with the 1st query, it is throwing the following error in the console:
"System.InvalidOperationException: The LINQ expression 'DbSet<transactiondataentry>()
.Where(t => t.Active && t.EntryStatusId != 5 && t.RentAgreementRateId == __baseRentAgreementRateId_0 && DbSet<rentperiodrelation>()
.Where(r => EF.Property<int?>(t, "Id") != null && object.Equals(
objA: (object)EF.Property<int?>(t, "Id"),
objB: (object)EF.Property<int?>(r, "TransactionDataEntryId")))
.OrderBy(r => r.PeriodDate)
.Select(r => r.PeriodDate)
.SequenceEqual(__basePeriods_1))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'."

I am calling it explicitly with "AsEnumerable but it seems to not get to it.
Do you recommend a way to checking and solving this?

Richard Deeming 19-Apr-23 4:43am

That's one of the problems with Entity Framework - it's a "leaky abstraction". The .SequenceEqual(basePeriods) part of your query can't be translated to SQL, and the error message isn't particularly helpful.

You'll need to load the transactions and evaluate that condition in the client:

var linkedTransactions = _transactionRepository.Table_transactionRepository.Table.AsNoTracking()
    .Include(t => t.RentPeriodRelations)
	.Where(t => t.Active && t.EntryStatusId != (int)EntryStatusEnum.Rejected && t.RentAgreementRateId == baseRentAgreementRateId)
	.ToList();

var linkedTransactionIds = linkedTransactions
    .Where(t => t.RentPeriodRelations.Select(rpr => rpr.PeriodDate).OrderBy(d => d).SequenceEqual(basePeriods))
	.Select(t => t.Id)
	.ToList();

xTMx9 19-Apr-23 5:19am

Thanks for the clarification and solution, appreciated.