Problem to use AsNoTracking query in master-detail entities.

Question

User-79977429 posted

Hi

I have 2  tables in master-details scenario as follow :

DefaultVisitProductHeaders -> Master table
DefaultVisitProductDetails -> Detail table

My problem is that when i load details data with AsNoTracking as follow :

List<DefaultVisitProductDetails> packageDetails = _dbContext.DefaultVisitProductDetails.AsNoTracking().Where(d => d.DefaultVisitProductHeaderRowID == gidPackageHeaderRowID).OrderBy(d => d.ProductID).ToList();
this.SetPackageDetailsList(packageDetails);

i'm facing error in SetPackageDetailsList method as follow :

InvalidOperationException: Error generated for warning 'Microsoft.EntityFrameworkCore.Infrastructure.DetachedLazyLoadingWarning': 
An attempt was made to lazy-load navigation property 'DefaultVisitProductHeaderRow' on detached entity of type 'DefaultVisitProductDetailsProxy'. Lazy-loading is not supported for detached entities or entities that are loaded with 'AsNoTracking()'. This exception can be suppressed or logged by passing event ID 'CoreEventId.DetachedLazyLoadingWarning' to the 'ConfigureWarnings' method in 'DbContext.OnConfiguring' or 'AddDbContext'.

Here is SetPackageDetailsList's body :

private void SetPackageDetailsList(List<DefaultVisitProductDetails> lstPackageDetails)
        {
            TempData["_lstPackageDetails"] = JsonConvert.SerializeObject(lstPackageDetails, Formatting.Indented, new JsonSerializerSettings { PreserveReferencesHandling = PreserveReferencesHandling.Objects }); // Cause error!
        }

Where is the problem & how to solve it?

Thanks in advance

Answer 1

User1120430333 posted

How is the dbcontext being used? You run into having to use a No Tracking scenario becuase the dbcontext is not being closed and destroyed on each usage of EF in the program.  This can happen when using an IoC and the dbcontext is being dependency injected into the solution. 

You can control the tracking altogether and not use it so that it's not a problem by creating a new context for each usage of EF.  Using Options that's part of .NET Core and using the Core IoC, I am able to DI the connectionstring object to the Dbcontext and create a new dbcontext each time I use EF.

I got the Options concept from the link. You create a new dbcontext each time you use EF,  tracking is out of the picture and its always a disconnected state for EF.

https://corderoski.wordpress.com/2017/09/18/how-to-read-appsettings-json-from-class-library-in-asp-net-core/

using System.Collections.Generic;
using System.Linq;
using System.Transactions;
using DAL.Models.DB;
using Entities;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Options;

namespace DAL
{
    public class DaoProject :IDaoProject
    {
        private readonly IOptions<ConnectionStrings> _options;
        
        public DaoProject(IOptions<ConnectionStrings> options)
        {
            _options = options;
        }

        public DtoProject GetProjectById(int id)
        {
            var dto = new DtoProject();

            using (var context = new ProjectManagementContext(_options))
            {
                var project = (context.Projects.Where(a => a.ProjectId == id)).SingleOrDefault();

                if (project == null) return dto;
                dto.ProjectId = project.ProjectId;
                dto.ClientName = project.ClientName;
                dto.ProjectName = project.ProjectName;
                dto.Technology = project.Technology;
                dto.ProjectType = project.ProjectType;
                dto.UserId = project.UserId;
                dto.StartDate = project.StartDate;
                dto.EndDate = project.EndDate;
                dto.Cost = project.Cost;
            }

            return dto;
        }
   }
}

// rest of code removed for bravity ..

using Entities;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Options;

namespace DAL.Models.DB
{
    public partial class ProjectManagementContext : DbContext
    {
        private readonly IOptions<ConnectionStrings> _options;
        public ProjectManagementContext(IOptions<ConnectionStrings> options)
        {
            _options = options;
        }

        public ProjectManagementContext(DbContextOptions<ProjectManagementContext> options)
            : base(options)
        {
        }

        public virtual DbSet<Projects> Projects { get; set; }
        public virtual DbSet<Tasks> Tasks { get; set; }
        public virtual DbSet<ProjectTypes> ProjectTypes { get; set; }
        public virtual DbSet<Durations> Durations { get; set; }
        public virtual DbSet<Resources> Resources { get; set; }
        public virtual DbSet<Statuses> Statuses { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer(new AppConfiguration(_options).GetProjectMgmntConn());
            }
        }

using Entities;
using Microsoft.Extensions.Options;

namespace DAL
{
    public class AppConfiguration
    {
        private readonly string _projectManagementConnection = "";
        private readonly IOptions<ConnectionStrings> _options;

        public AppConfiguration(IOptions<ConnectionStrings> options) 
        {
            _options = options;
            _projectManagementConnection = _options.Value.ProjectManagementConnection;
        }

        public string GetProjectMgmntConn() => $"{_projectManagementConnection}";

    }

Answer 2

User-854763662 posted

Hi hamed_1983 ,

 It's a known github issue , as ajcvickers commented:

This is because lazy-loading isn't supported for NoTracking queries (#10042) but we tried to not make it throw if it looked like lazy-loading wasn't needed. In retrospect it might have been better to always throw. Note that the warning can be configured to not throw using ConfigureWarnings in the DbContextOptionsBuilder.

You have two options:

1. Use the Include() method to load your relationships

2. Ignore the warning and simply get null for your relationships

You can configure EF to ignore this error in ConfigureService():

 public void ConfigureServices(IServiceCollection services)
{
            var connection = @"Server=(localdb)\mssqllocaldb;Database=WebAPIDemo3_1;Trusted_Connection=True;ConnectRetryCount=0";
            services.AddDbContext<WebAPIDbContext>(options => 
              options.UseLazyLoadingProxies()
                .ConfigureWarnings(warnings => warnings.Ignore(CoreEventId.DetachedLazyLoadingWarning))
                .UseSqlServer(connection)
                );

            //...
}

Best Regards,

Sherry