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

Dharmesh Sharma 1

I am getting error for multiple dbcontext like

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

But I am using repository pattern with Autofac with one database when trying to join its showing like i am using two dbcontext.
code is

private readonly IRepository<AdminFlightInventoryMaster> _adminFlightIventoryMasterRepository;
 private readonly IRepository<AdminFlightInventoryDetails> _adminInventoryDetailsRepository;
 public AdminFlightInventoryMasterService(IRepository<AdminFlightInventoryMaster> adminFlightIventoryMasterRepository, IRepository<AdminFlightInventoryDetails> adminInventoryDetailsRepository)
        {
            _adminFlightIventoryMasterRepository = adminFlightIventoryMasterRepository;
            _adminInventoryDetailsRepository = adminInventoryDetailsRepository;
        }

public virtual IPagedList<AdminFlightInventoryMaster> SearchInventory(string source, string destination, DateTime travelDate)
        {
            var query = _adminFlightIventoryMasterRepository.Table;
            query = query.Where(x => x.SourceCode == source && x.DestinationCode == destination && x.IsDeleted == false && x.IsActive == true);
            query = from x in query
                    join
                     d in _adminInventoryDetailsRepository.Table
                     on x.ID equals d.AdminInventoryMasterId
                    where d.StartDate.Date == travelDate.Date
                    select x;
            query = query.OrderBy(x => x.ID);
            var result = new PagedList<AdminFlightInventoryMaster>(query, 0, 100);
            return result;

        }

when I return then getting error

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

//data layer
builder.Register(context => new GutObjectContext(context.Resolve<DbContextOptions<GutObjectContext>>()))
                .As<IDbContext>().InstancePerLifetimeScope();
//repositories
        builder.RegisterGeneric(typeof(EfRepository<>)).As(typeof(IRepository<>)).InstancePerLifetimeScope();

why I am getting this error

These all code working in .net core 2.1

AgaveJoe 28,371 Reputation points

2022-01-03T19:09:13.887+00:00

Your post does not include the repository design but the error indicates the a new context is created for each service. To fix this design bug either create a new repository that has the features you want or execute one query at a time in the SearchInventory method.

Honestly, this is a very common problem when wrapping Entity Framework, which is a unit of work pattern, inside a repository pattern. It is often called an anti-pattern and unfortunately the bug is not realized until working with table relationships (navigation properties) and basic business logic. For this use case I would create service/api pattern that populates the navigation properties rather that dealing with the two table separately.

Also, you might consider contacting Autofac if you feel the design is not handling the DbContext life time as expected.

Zhi Lv - MSFT 32,336 Microsoft Vendor

Hi @Dharmesh Sharma ,

Try to use .ToList() method to load the data in your program first, then filter the data. Code like this:

         var query = _adminFlightIventoryMasterRepository.Table.ToList()  
         query = query.Where(x => x.SourceCode == source && x.DestinationCode == destination && x.IsDeleted == false && x.IsActive == true);  
         query = from x in query  
                 join  
                  d in _adminInventoryDetailsRepository.Table.ToList()  
                  on x.ID equals d.AdminInventoryMasterId  
                 where d.StartDate.Date == travelDate.Date  
                 select x;

         var masterlist = _adminFlightIventoryMasterRepository.Table.ToList();  
         var detaillist = _adminInventoryDetailsRepository.Table.ToList();  
         var query = masterlist.Where(x => x.SourceCode == source && x.DestinationCode == destination && x.IsDeleted == false && x.IsActive == true);  
         query = from x in query  
                 join  
                  d in detaillist   
                  on x.ID equals d.AdminInventoryMasterId  
                 where d.StartDate.Date == travelDate.Date  
                 select x;

Best regards,
Dillion

Share via

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

Your answer