SqlTransaction error when navigation property not included

[scottctr]

Receiving the following exception:

System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable.
   at System.Data.SqlClient.SqlTransaction.ZombieCheck()
   at System.Data.SqlClient.SqlTransaction.Commit()
   at Microsoft.EntityFrameworkCore.Storage.RelationalTransaction.Commit()
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(DbContext _, ValueTuple`2 parameters)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementation[TState,TResult](Func`3 operation, Func`3 verifySucceeded, TState state)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IReadOnlyList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at EFCoreTransactionTester.InstitutionSubscriber.OnInstitutionReceived(Object sender, MessageResponse message) in C:\EFCoreTransactionTester\EFCoreTransactionTester\Program.cs:line 93

While creating a scaled down test app, I realized it only happens when a navigation property was missed:

public List<ProviderMasterXrefMdm> Get(int providerInstitutionMdmId)
{
    return dbContext.ProviderMasterXrefMdm
        .Include(pmx => pmx.ProviderMaster)
        //*** THIS WAS MISSING *** -> .Include(pmx => pmx.ProviderMaster.ProviderSearchIndex)
        .Where(pmx => pmx.MdmId == providerInstitutionMdmId)
        .ToList();
}

...and when the missing navigation property is accessed.

private void UpdateProviderSearch(ProviderMaster mpdInstitution, string value)
{
    //*** EXCEPTION DOES NOT OCCUR IF THE MISSED NAVIGATION NOT ACCESSED ***
    var providerSearch = mpdInstitution.ProviderSearchIndex.Where(ps => ps.SearchIndexTypeId == 3 && ps.SearchValue.ToLower() == value.ToLower());

    if (!providerSearch.Any())
    { mpdInstitution.ProviderSearchIndex.Add(new ProviderSearchIndex { NhProviderId = mpdInstitution.NhProviderId, SearchIndexTypeId = 3, SearchValue = value }); }
}

I'm not sure this scenario should throw an exception. If the exception can't be avoided, I'm hoping for an improved message to help identify what's happening (and help others avoid the numerous hours I spent on this ).

EF Core version: 2.1.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer 2.1.1
Operating system: Microsoft Windows 10 Enterprise Version 10.0.16299 Build 16299
IDE: Visual Studio 2017 Professional 15.7.0

Repro:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Newtonsoft.Json;

namespace EFCoreTransactionTester
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Hello World!");

            new MasterDataConsumerService().Start();

            Console.ReadLine();
        }
    }

    public class MasterDataConsumerService
    {
        private readonly InstitutionSubscriber institutionSubscriber;
        private readonly string connectionString;
        private readonly DbContextOptions<MPDContext> dbOptions;

        public MasterDataConsumerService()
        {
            connectionString = @"Server=NHLA04384;Database=MPD;Trusted_Connection=True;MultipleActiveResultSets=True";
            this.dbOptions = GetDbOptions();
            this.institutionSubscriber = institutionSubscriber ?? new InstitutionSubscriber(this.dbOptions);
        }

        public void Start()
        {
            institutionSubscriber.Start();

            logger.Trace($"{nameof(MasterDataConsumerService)} started");
        }

        internal DbContextOptions<MPDContext> GetDbOptions()
        {
            var optionsBuilder = new DbContextOptionsBuilder<MPDContext>();
            optionsBuilder
                .UseSqlServer(connectionString, options => options.EnableRetryOnFailure(5))
                .EnableSensitiveDataLogging();

            return optionsBuilder.Options;
        }
    }

    public class InstitutionSubscriber
    {
        private readonly DbContextOptions<MPDContext> dbOptions;
        private readonly MasterInstitutionToMpdConverter masterInstitutionToMpdConverter;

        public InstitutionSubscriber(DbContextOptions<MPDContext> dbOptions)
        {
            this.dbOptions = dbOptions ?? throw new ArgumentNullException(nameof(dbOptions));
            masterInstitutionToMpdConverter = new MasterInstitutionToMpdConverter(GetMpdProviderTypeLookup());
        }

        private Dictionary<string, List<ProviderTypeXrefIms>> GetMpdProviderTypeLookup()
        {
            using (var dbContext = new MPDContext(dbOptions))
            { return new ProviderTypeXrefImsRepository(dbContext).GetAll(); }
        }

        public void Start()
        {
            var messages = new[]
            {
                new MessageResponse {Offset = 1, PublishedTime = DateTime.Now, Content = "{\"ancillaryProperties\":{\"TotalLicensedBeds\":{\"JsonValue\":\"6311\"},\"WebsiteUrl\":{\"JsonValue\":\"\\\"www.hospitalA.org\\\"\"}},\"InstitutionTypes\":[{\"MdmId\":985,\"Classification\":{\"MdmId\":25,\"Name\":\"Corporate Parent\"},\"ClassificationMdmId\":25,\"FacilityType\":{\"MdmId\":14,\"Name\":\"Integrated Delivery Network (IDN)\"},\"FacilityTypeMdmId\":14,\"Specialty\":{\"Name\":\"Unknown\"}}],\"Addresses\":[{\"MdmId\":2472,\"City\":\"CityA\",\"Line1\":\"123 A RDG\",\"State\":\"AK\",\"Type\":{\"MdmId\":1,\"Name\":\"Mailing\"},\"TypeMdmId\":1,\"Zip\":\"11111\",\"Zip4\":\"1111\"},{\"MdmId\":2495,\"City\":\"CityA\",\"County\":\"County A\",\"Latitude\":\"12.345678\",\"Longitude\":\"-098.76543\",\"Line1\":\"919 A RDG\",\"State\":\"AK\",\"Type\":{\"MdmId\":2,\"Name\":\"Physical\"},\"TypeMdmId\":2,\"Zip\":\"11111\",\"Zip4\":\"1111\"}],\"MdmId\":1485,\"DataLoad\":{\"MdmId\":1343,\"DataSource\":{\"MdmId\":1,\"Name\":\"DS1\"},\"DataSourceMdmId\":1,\"Name\":\"TestSeed\"},\"DataSourceInstitutionId\":\"SLC00000128\",\"InstitutionStatus\":{\"MdmId\":1,\"Name\":\"Active\"},\"InstitutiionStatusMdmId\":1,\"Name\":\"Alfred Health\",\"Identifiers\":[{\"MdmId\":275,\"Identifier\":\"1111111111\",\"Type\":{\"MdmId\":15,\"Name\":\"NPI\"},\"TypeMdmId\":15}],\"PhoneNumbers\":[]}"},
                new MessageResponse {Offset = 2, PublishedTime = DateTime.Now, Content = "{\"ancillaryProperties\":{\"TotalLicensedBeds\":{\"JsonValue\":\"718\"},\"WebsiteUrl\":{\"JsonValue\":\"\\\"www.hospitalB.org\\\"\"}},\"InstitutionTypes\":[{\"MdmId\":988,\"Classification\":{\"MdmId\":25,\"Name\":\"Corporate Parent\"},\"ClassificationMdmId\":25,\"FacilityType\":{\"MdmId\":14,\"Name\":\"Integrated Delivery Network (IDN)\"},\"FacilityTypeMdmId\":14,\"Specialty\":{\"Name\":\"Unknown\"}}],\"Addresses\":[{\"MdmId\":2478,\"City\":\"CityB\",\"Line1\":\"345 W B RD\",\"State\":\"AR\",\"Type\":{\"MdmId\":1,\"Name\":\"Mailing\"},\"TypeMdmId\":1,\"Zip\":\"22222\",\"Zip4\":\"2222\"},{\"MdmId\":2499,\"City\":\"CityB\",\"County\":\"County B\",\"Latitude\":\"23.567890\",\"Longitude\":\"-087.65432\",\"Line1\":\"210 W B RD\",\"State\":\"AR\",\"Type\":{\"MdmId\":2,\"Name\":\"Physical\"},\"TypeMdmId\":2,\"Zip\":\"22222\",\"Zip4\":\"2222\"}],\"MdmId\":1489,\"DataLoad\":{\"MdmId\":1348,\"DataSource\":{\"MdmId\":1,\"Name\":\"DS1\"},\"DataSourceMdmId\":1,\"Name\":\"TestSeed\"},\"DataSourceInstitutionId\":\"SLC00000130\",\"InstitutionStatus\":{\"MdmId\":1,\"Name\":\"Active\"},\"InstitutiionStatusMdmId\":1,\"Name\":\"Bob Health Partners\",\"Identifiers\":[{\"MdmId\":278,\"Identifier\":\"2222222222\",\"Type\":{\"MdmId\":15,\"Name\":\"ID\"},\"TypeMdmId\":15}],\"PhoneNumbers\":[]}"},
                new MessageResponse {Offset = 3, PublishedTime = DateTime.Now, Content = "{\"ancillaryProperties\":{\"TotalLicensedBeds\":{\"JsonValue\":\"1184\"},\"WebsiteUrl\":{\"JsonValue\":\"\\\"www.hospitalC.org\\\"\"}},\"InstitutionTypes\":[{\"MdmId\":989,\"Classification\":{\"MdmId\":25,\"Name\":\"Corporate Parent\"},\"ClassificationMdmId\":25,\"FacilityType\":{\"MdmId\":14,\"Name\":\"Integrated Delivery Network (IDN)\"},\"FacilityTypeMdmId\":14,\"Specialty\":{\"Name\":\"Unknown\"}}],\"Addresses\":[{\"MdmId\":2477,\"City\":\"CityC\",\"Line1\":\"678 W C ST\",\"State\":\"CA\",\"Type\":{\"MdmId\":1,\"Name\":\"Mailing\"},\"TypeMdmId\":1,\"Zip\":\"33333\",\"Zip4\":\"3333\"},{\"MdmId\":2498,\"City\":\"CityC\",\"County\":\"County C\",\"Latitude\":\"45.678901\",\"Longitude\":\"-006.54321\",\"Line1\":\"501 W C ST\",\"State\":\"CA\",\"Type\":{\"MdmId\":2,\"Name\":\"Physical\"},\"TypeMdmId\":2,\"Zip\":\"33333\",\"Zip4\":\"3333\"}],\"MdmId\":1488,\"DataLoad\":{\"MdmId\":1347,\"DataSource\":{\"MdmId\":1,\"Name\":\"DS1\"},\"DataSourceMdmId\":1,\"Name\":\"TestSeed\"},\"DataSourceInstitutionId\":\"SLC00000129\",\"InstitutionStatus\":{\"MdmId\":1,\"Name\":\"Active\"},\"InstitutiionStatusMdmId\":1,\"Name\":\"Chris Health System\",\"Identifiers\":[{\"MdmId\":277,\"Identifier\":\"3333333333\",\"Type\":{\"MdmId\":15,\"Name\":\"ID\"},\"TypeMdmId\":15}],\"PhoneNumbers\":[]}"},
            };

            foreach (var messageResponse in messages)
            { Task.Run(() => OnInstitutionReceived(null, messageResponse)); }
        }

        internal void OnInstitutionReceived(object sender, MessageResponse message)
        {
            try
            {
                logger.Trace($"Received offset {message.Offset} {message.Content}");

                using (var dbContext = new MPDContext(dbOptions))
                {
                    OnInstitutionReceived(message, new ProviderMasterXrefMdmRepository(dbContext));

                    dbContext.SaveChanges();
                }
            }
            catch (Exception e)
            {
                logger.Error(e);
            }
        }

        internal void OnInstitutionReceived(MessageResponse message, ProviderMasterXrefMdmRepository providerMasterXrefMdmRepository)
        {
            var masterInstitution = JsonConvert.DeserializeObject<ProviderInstitution>((string)message.Content);

            logger.Trace($"Received MdmId {masterInstitution.MdmId}");

            masterInstitutionToMpdConverter.Convert(masterInstitution, GetMpdInstitutions(providerMasterXrefMdmRepository, masterInstitution));
        }

        private static List<ProviderMaster> GetMpdInstitutions(ProviderMasterXrefMdmRepository providerMasterXrefMdmRepository
            , ProviderInstitution masterInstitution)
        {
            return providerMasterXrefMdmRepository.Get(masterInstitution.MdmId.Value).Select(x => x.ProviderMaster).ToList();
        }
    }
    public class MasterInstitutionToMpdConverter
    {
        private readonly Dictionary<string, List<ProviderTypeXrefIms>> mpdProviderTypeLookup;

        public MasterInstitutionToMpdConverter(Dictionary<string, List<ProviderTypeXrefIms>> mpdProviderTypeLookup)
        {
            this.mpdProviderTypeLookup = mpdProviderTypeLookup ?? throw new ArgumentNullException(nameof(mpdProviderTypeLookup));
        }

        public void Convert(ProviderInstitution masterInstitution, List<ProviderMaster> mpdInstitutions)
        {
            foreach (var masterInstitutionType in masterInstitution.InstitutionTypes)
            {
                try
                {
                    var mpdType = mpdProviderTypeLookup[BuildMpdInstitutionTypeXrefKey(masterInstitutionType)].First();
                    var mpdInstitution = mpdInstitutions.SingleOrDefault(m => m.ProviderTypeId == mpdType.ProviderTypeId);
                    UpdateMpdIdentifiers(masterInstitution, mpdInstitution);
                }
                catch (Exception e)
                {
                    logger.Error(e);
                }
            }
        }

        private string BuildMpdInstitutionTypeXrefKey(InstitutionType masterInstitutionType)
        {
            return (string.IsNullOrWhiteSpace(masterInstitutionType.Classification?.Name) ? "Unknown" : masterInstitutionType.Classification.Name)
                   + (string.IsNullOrWhiteSpace(masterInstitutionType.FacilityType?.Name) ? "Unknown" : masterInstitutionType.FacilityType.Name)
                   + (string.IsNullOrWhiteSpace(masterInstitutionType.Specialty?.Name) ? "Unknown" : masterInstitutionType.Specialty.Name);
        }

        private void UpdateMpdIdentifiers(ProviderInstitution masterInstitution, ProviderMaster mpdInstitution)
        {
            var npiIdentifier = masterInstitution.Identifiers.SingleOrDefault(i => i.TypeMdmId == 15);
            if (!string.IsNullOrWhiteSpace(npiIdentifier?.Identifier))
            { UpdateProviderSearch(mpdInstitution, npiIdentifier.Identifier); }
        }

        private void UpdateProviderSearch(ProviderMaster mpdInstitution, string value)
        {
            //*** EXCEPTION DOES NOT OCCUR IF THIS METHOD NOT CALLED ***
            var providerSearch = mpdInstitution.ProviderSearchIndex.Where(ps => ps.SearchIndexTypeId == 3 && ps.SearchValue.ToLower() == value.ToLower());

            if (!providerSearch.Any())
            { mpdInstitution.ProviderSearchIndex.Add(new ProviderSearchIndex { NhProviderId = mpdInstitution.NhProviderId, SearchIndexTypeId = 3, SearchValue = value }); }
        }
    }

    public class FacilityType
    {
        public string Name { get; set; }
    }

    public class IdentifierAssignment
    {
        public string Identifier { get; set; }
        public int? TypeMdmId { get; set; }
    }

    public class InstitutionClassification
    {
        public string Name { get; set; }
    }

    public class InstitutionType
    {
        public InstitutionClassification Classification { get; set; }
        public FacilityType FacilityType { get; set; }
        public MedicalSpecialty Specialty { get; set; }
    }

    public enum LogLevel
    {
        Debug,
        Error,
        Trace,
        Warning
    }

    public static class logger
    {
        public static void Error(Exception exception)
        {
            Log(LogLevel.Error, BuildExceptionMessage(exception));
        }

        public static void Trace(string message)
        {
            Log(LogLevel.Trace, message);
        }

        private static string BuildExceptionMessage(Exception exception)
        {
            string result = string.Empty;

            var currentException = exception;
            while (currentException != null)
            {
                if (result != string.Empty)
                { result += $"{Environment.NewLine}---- INNER EXCEPTION ----{Environment.NewLine}"; }

                result += exception.ToString();
                currentException = currentException.InnerException;
            }

            return result;
        }

        private static void Log(LogLevel logLevel, string message)
        {
            Console.WriteLine($"[{DateTime.Now:h:mm:ss.fff}-ThreadId: {Environment.CurrentManagedThreadId}--{logLevel}] {message}");
        }
    }
    public class MedicalSpecialty
    {
        public string Name { get; set; }
    }
    internal class MessageResponse
    {
        public int Offset { get; set; }
        public DateTime PublishedTime { get; set; }
        public string Content { get; set; }
    }
    public partial class MPDContext : DbContext
    {
        public MPDContext(DbContextOptions<MPDContext> options)
            : base(options)
        { }

        public virtual DbSet<ProviderMaster> ProviderMaster { get; set; }
        public virtual DbSet<ProviderMasterXrefMdm> ProviderMasterXrefMdm { get; set; }
        public virtual DbSet<ProviderTypeXrefIms> ProviderTypeXrefIms { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {

            modelBuilder.Entity<ProviderMaster>(entity =>
            {
                entity.HasKey(e => e.NhProviderId);

                entity.ToTable("ProviderMaster", "dbo");

                entity.HasIndex(e => e.ProviderId)
                    .HasName("ix_ProviderMaster_ProviderID");

                entity.HasIndex(e => e.ProviderTypeId)
                    .HasName("ix_ProviderMaster_ProviderTypeID");

                entity.HasIndex(e => new { e.ProviderTypeId, NhproviderName = e.NhProviderName })
                    .HasName("NCIX_ProviderMaster_ProviderTypeID_NHProviderName");

                entity.HasIndex(e => new { e.NhProviderId, e.ProviderId, e.ProviderGroupId, e.IsActive, NhproviderName = e.NhProviderName, ReferBackNhproviderId = e.ReferBackNhProviderId, e.ProviderTypeId })
                    .HasName("NCIX_ProviderMaster_ProviderTypeID_NHProviderID_ProviderID_ProviderGroupID_IsActive_NHProviderName_ReferBackNHProviderID");

                entity.HasIndex(e => new { e.NhProviderId, e.ProviderId, e.ProviderTypeId, e.NhProviderRuleRefId, NhproviderName = e.NhProviderName, e.Comments, e.ProviderGroupId, e.IsActive })
                    .HasName("NCIX_ProviderMaster_ProviderGroupID_IsActive_NHProviderID_ProviderID_ProviderTypeID");

                entity.Property(e => e.NhProviderId)
                    .HasColumnName("NHProviderID")
                    .ValueGeneratedNever();

                entity.Property(e => e.Comments)
                    .HasMaxLength(500)
                    .IsUnicode(false);

                   entity.Property(e => e.NhProviderName)
                    .HasColumnName("NHProviderName")
                    .HasMaxLength(300)
                    .IsUnicode(false);

                entity.Property(e => e.NhProviderRuleRefId).HasColumnName("NHProviderRuleRefID");

                entity.Property(e => e.ProviderGroupId).HasColumnName("ProviderGroupID");

                entity.Property(e => e.ProviderId)
                    .HasColumnName("ProviderID")
                    .HasMaxLength(19)
                    .IsUnicode(false);

                entity.Property(e => e.ProviderTypeId).HasColumnName("ProviderTypeID");

                entity.Property(e => e.ReferBackNhProviderId).HasColumnName("ReferBackNHProviderID");

                   entity.HasMany(d => d.ProviderSearchIndex)
                    .WithOne()
                    .HasForeignKey(d => d.NhProviderId);
     });

            modelBuilder.Entity<ProviderMasterXrefMdm>(entity =>
            {
                entity.HasKey(e => e.XrefMdmId)
                    .HasName("PK_ProviderMasterXref_Mdm");

                entity.ToTable("ProviderMasterXref_Mdm", "dbo");

                entity.HasIndex(e => e.XrefMdmId)
                    .HasName("IX_ProviderMasterXref_MdmId");

                entity.HasIndex(e => e.NhProviderId)
                    .HasName("IX_ProviderMasterXRef_Mdm_NHProviderID");

                entity.Property(e => e.XrefMdmId)
                    .HasColumnName("XrefMdmId")
                    .UseSqlServerIdentityColumn();

                entity.Property(e => e.MdmId)
                    .HasColumnName("MdmId");

                entity.Property(e => e.NhProviderId)
                    .HasColumnName("NHProviderID");

                entity.HasOne(e => e.ProviderMaster)
                    .WithOne()
                    .HasConstraintName("FK_ProviderMasterXref_Mdm_ProviderMaster");
            });

            modelBuilder.Entity<ProviderSearchIndex>(entity =>
            {
                entity.ToTable("ProviderSearchIndex", "dbo");

                entity.HasIndex(e => e.NhProviderId)
                    .HasName("ix_ProviderSearch_NHProviderID");

                entity.HasIndex(e => e.SearchIndexTypeId)
                    .HasName("ix_ProviderSearchIndex_SearchIndexType");

                entity.HasIndex(e => e.SearchValue)
                    .HasName("ix_ProviderSearchIndex_SearchIndexValue");

                entity.HasIndex(e => new { e.NhProviderId, e.SearchIndexTypeId })
                    .HasName("NCIX_ProviderSearchIndex_SearchIndexTypeID_NHProviderID");

                entity.HasIndex(e => new { e.SearchValue, e.NhProviderId, e.SearchIndexTypeId })
                    .HasName("NCIX_ProviderSearchIndex_SearchIndexTypeID_SearchValue_NHProviderID");

                entity.Property(e => e.ProviderSearchIndexId)
                    .HasColumnName("ProviderSearchIndexID")
                    .UseSqlServerIdentityColumn();

                entity.Property(e => e.NhProviderId).HasColumnName("NHProviderID");

                entity.Property(e => e.SearchIndexTypeId).HasColumnName("SearchIndexTypeID");

                entity.Property(e => e.SearchValue)
                    .HasMaxLength(300)
                    .IsUnicode(false);

                entity.HasOne(d => d.NHProvider)
                    .WithMany(p => p.ProviderSearchIndex)
                    .HasForeignKey(d => d.NhProviderId);
            });

            modelBuilder.Entity<ProviderTypeXrefIms>(entity =>
            {
                entity.HasKey(e => e.RefId);

                entity.ToTable("ProviderTypeXref_IMS", "dbo");

                entity.Property(e => e.RefId)
                    .HasColumnName("RefID")
                    .UseSqlServerIdentityColumn();

                entity.Property(e => e.ImsProviderClassification)
                    .IsRequired()
                    .HasColumnName("IMSProviderClassification")
                    .HasMaxLength(50)
                    .IsUnicode(false);

                entity.Property(e => e.ImsProviderFacilityType)
                    .IsRequired()
                    .HasColumnName("IMSProviderFacilityType")
                    .HasMaxLength(50)
                    .IsUnicode(false);

                entity.Property(e => e.ImsProviderSpecialty)
                    .HasColumnName("IMSProviderSpecialty")
                    .HasMaxLength(50)
                    .IsUnicode(false);

                entity.Property(e => e.ProviderTypeId).HasColumnName("ProviderTypeID");
            });
        }
    }

    public class ProviderInstitution
    {
        public int? MdmId { get; set; }
        public List<InstitutionType> InstitutionTypes = new List<InstitutionType>();
        public List<IdentifierAssignment> Identifiers { get; set; } = new List<IdentifierAssignment>();
    }
    public partial class ProviderMaster
    {
        public ProviderMaster()
        {
            ProviderSearchIndex = new HashSet<ProviderSearchIndex>();
        }

        public int NhProviderId { get; set; }
        public string ProviderId { get; set; }
        public byte ProviderGroupId { get; set; }
        public byte ProviderTypeId { get; set; }
        public bool? IsActive { get; set; }
        public int? NhProviderRuleRefId { get; set; }
        public string NhProviderName { get; set; }
        public string Comments { get; set; }
        public int? ReferBackNhProviderId { get; set; }
        public ICollection<ProviderSearchIndex> ProviderSearchIndex { get; set; }
    }
 
   public class ProviderMasterXrefMdm
    {
        public int XrefMdmId { get; set; }

        public int MdmId { get; set; }

        public ProviderMaster ProviderMaster { get; set; }

        public int NhProviderId { get; set; }
    }

    public class ProviderMasterXrefMdmRepository
    {
        private readonly MPDContext dbContext;

        public ProviderMasterXrefMdmRepository(MPDContext dbContext)
        {
            this.dbContext = dbContext ?? throw new ArgumentNullException(nameof(dbContext));
        }

        public List<ProviderMasterXrefMdm> Get(int providerInstitutionMdmId)
        {
            return dbContext.ProviderMasterXrefMdm
                .Include(pmx => pmx.ProviderMaster)
                //*** THIS WAS MISSING *** -> .Include(pmx => pmx.ProviderMaster.ProviderSearchIndex)
                .Where(pmx => pmx.MdmId == providerInstitutionMdmId)
                .ToList();
        }
    }
 
   public partial class ProviderSearchIndex
    {
        public int ProviderSearchIndexId { get; set; }
        public int SearchIndexTypeId { get; set; }
        public string SearchValue { get; set; }

        public ProviderMaster NHProvider { get; set; }
        public int NhProviderId { get; set; }
    }

    public partial class ProviderTypeXrefIms
    {
        public int RefId { get; set; }
        public string ImsProviderClassification { get; set; }
        public string ImsProviderFacilityType { get; set; }
        public string ImsProviderSpecialty { get; set; }
        public byte ProviderTypeId { get; set; }
    }
    public class ProviderTypeXrefImsRepository
    {
        private readonly MPDContext dbContext;

        public ProviderTypeXrefImsRepository(MPDContext dbContext)
        {
            this.dbContext = dbContext ?? throw new ArgumentNullException(nameof(dbContext));
        }

        public Dictionary<string, List<ProviderTypeXrefIms>> GetAll()
        {
            var typeXrefDictionary = new Dictionary<string, List<ProviderTypeXrefIms>>();

            var xRefTypes = dbContext.ProviderTypeXrefIms
                .AsNoTracking()
                .ToList();

            foreach (var xref in xRefTypes)
            {
                var key = GetMpdInstitutionTypeXrefKey(xref);
                if (!typeXrefDictionary.TryGetValue(key, out var mpdTypeList))
                {
                    mpdTypeList = new List<ProviderTypeXrefIms>();
                    typeXrefDictionary.Add(key, mpdTypeList);
                }

                mpdTypeList.Add(xref);
            }

            return typeXrefDictionary;
        }

        private static string GetMpdInstitutionTypeXrefKey(ProviderTypeXrefIms xref)
        {
            return (string.IsNullOrWhiteSpace(xref.ImsProviderClassification) ? "Unknown" : xref.ImsProviderClassification)
                   + (string.IsNullOrWhiteSpace(xref.ImsProviderFacilityType) ? "Unknown" : xref.ImsProviderFacilityType)
                   + (string.IsNullOrWhiteSpace(xref.ImsProviderSpecialty) ? "Unknown" : xref.ImsProviderSpecialty);
        }
    }
}

[smitpatel]

The code supplied to reproduce this issue is quite complex. It would help greatly to have a simplified project/solution or code listing that isolates the issue in EF.

Posting above code in console app is not working since lack of database and seed data. Especially seed data part is hard to arrive at given the complex model.

[scottctr]

Hi Smit. I apologise for the complexity and lack of seed data. I took quite a bit of time to scare down to this simplified version. The data is a mix of client and commercial data that I'll not able to share. I'm happy to generate some test data, but it will be a couple of weeks before I can get to it. I do appreciate your attention to this issue!

[ajcvickers]

EF Team Triage: Closing this issue as the requested additional details have not been provided and we have been unable to reproduce it.

BTW this is a canned response and may have info or details that do not directly apply to this particular issue. While we'd like to spend the time to uniquely address every incoming issue, we get a lot traffic on the EF projects and that is not practical. To ensure we maximize the time we have to work on fixing bugs, implementing new features, etc. we use canned responses for common triage decisions.