Click here to Skip to main content
15,849,907 members
Please Sign up or sign in to vote.
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
Updated 18-Apr-23 5:05am
v2
Comments
Gerry Schmitz 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

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[^]
 
Share this answer
 
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.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900