none
Partial eager load on a many to many navigation property does not return any records (EF 4.3) RRS feed

  • Question

  • I have run into a problem with the "Query and Load" way of filtering the loading of a navigation property.

    The following code works for a 1:n relationship:

    context.Entry(entity).Collection(ent => ent.OneToMany).Query().Where(otm => otm.Name == "test").Load();

    For the following m:n relationship (with an intermediate table) nothing is returned.

    context.Entry(entity).Collection(ent => ent.ManyToMany).Query().Where(otm => otm.Name == "test").Load();

    SQL profiler does show the query being executed with a single record as the result. If I take off the where clause and just load the whole collection that works too.

    Has anyone else run into this problem?

    Tuesday, March 27, 2012 12:37 PM

All replies

  • Hi J Chase,

    Welcome to MSDN Forum.

    Could you please show the code which can let me test?

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, March 28, 2012 3:34 PM
    Moderator
  • Hi,

    Here are the entities with a 1:n collection and m:n collection on "Thing". 

        [Table("Thing")]
        public class Thing
        {
            public Thing()
            {
                Thing11toM = new List<Thing1N>();
                Thing2MtoM = new List<ThingMN>();
            }
    
            public int ThingId { get; set; }
    
            public string Name { get; set; }
    
            public List<Thing1N> Thing11toM { get; set; }
    
            public List<ThingMN> Thing2MtoM { get; set; }
        }
    
    
        [Table("Thing1N")]
        public class Thing1N
        {
            public int Thing1NId { get; set; }
    
            public int ThingId { get; set; }
    
            public string Name { get; set; }
    
            public Thing Thing { get; set; }
        }
    
        [Table("ThingMN")]
        public class ThingMN
        {
            public int ThingMNId { get; set; }
    
            public string Name { get; set; }
        }

    With a mapping for the the m:n collection:

    public class ThingMap : EntityTypeConfiguration<Thing>
        {
            public ThingMap()
            {
                this.HasMany(t => t.Thing2MtoM)
                    .WithMany()
                    .Map(m =>
                        {
                            m.ToTable("ThingThinMNs");
                            m.MapLeftKey("ThingId");
                            m.MapRightKey("ThingMNId");
                        });
            }
        }

    This is the context:

        public class ThingContext :DbContext
        {
            public ThingContext()
            {
                this.Configuration.LazyLoadingEnabled = false;
                this.Configuration.ProxyCreationEnabled = false;
                this.Configuration.AutoDetectChangesEnabled = true;
                this.Configuration.ValidateOnSaveEnabled = true;
            }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Configurations.Add(new ThingMap());
    
                base.OnModelCreating(modelBuilder);
            }
    
            public DbSet<Thing> Things { get; set; }
            public DbSet<Thing1N> Thing1s { get; set; }
            public DbSet<ThingMN> Thing2s { get; set; }
        }

    Using the following data:

            using (ThingContext context = new ThingContext())
            {
                Thing t1 = new Thing() { Name = "t1" };
    
                context.Entry(t1).State = EntityState.Added;
    
                Thing1N t1n1 = new Thing1N() { Name = "t1n1", Thing = t1 };
                Thing1N t1n2 = new Thing1N() { Name = "t1n2", Thing = t1 };
                Thing1N t1n3 = new Thing1N() { Name = "t1n3", Thing = t1 };
    
                context.Entry(t1n1).State = EntityState.Added;
                context.Entry(t1n2).State = EntityState.Added;
                context.Entry(t1n3).State = EntityState.Added;
    
                ThingMN tmn1 = new ThingMN() { Name = "tmn1" };
                ThingMN tmn2 = new ThingMN() { Name = "tmn2" };
                ThingMN tmn3 = new ThingMN() { Name = "tmn3" };
    
                context.Entry(tmn1).State = EntityState.Added;
                context.Entry(tmn2).State = EntityState.Added;
                context.Entry(tmn3).State = EntityState.Added;
    
                t1.Thing2MtoM.Add(tmn1);
                t1.Thing2MtoM.Add(tmn2);
                t1.Thing2MtoM.Add(tmn3);
    
                context.SaveChanges();
    
            }

    If you do a filtered query on both collections the many to many collection never gets loaded..

                using (ThingContext context = new ThingContext())
                {
                    Thing thing = context.Things.First();
    
                    context.Entry(thing).Collection(t => t.Thing11toM).Query().Where(t1s => t1s.Name == "t1n1").Load();
    
                    context.Entry(thing).Collection(t => t.Thing2MtoM).Query().Where(tmns => tmns.Name == "tmn1").Load();
    
                    //a normal load willl return the entities
                    context.Entry(thing).Collection(t => t.Thing2MtoM).Load();
    
                }

    Hopefully this makes it a bit clearer,

    Jonathan.

    Thursday, March 29, 2012 4:18 AM
  • Hi J Chase,

    I can repro the issue, but I haven't find the root cause now. I will do more research on this issue and come back as soon as possible, thanks for your under standing.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, March 29, 2012 7:43 AM
    Moderator
  • Hi J Chase,

    After researching, I find the issue is caused by the entities defination. In EF code-first, creating many to many relationship only need to define the two ends of the entities and configure them, the relationship table can't be defined like the code you posted. If you want the "Thing" entity as  the relationship table and also has its own properties, you have to creat a one to many relationship between "Thing" and "Thing1N", and create a one to many relationship between "Thing" and "ThingMN". Using "Include" method to inlude the related entities and load them into memory.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Allen_MSDNModerator Thursday, April 5, 2012 2:04 AM
    • Unmarked as answer by JayChase Saturday, April 7, 2012 1:35 AM
    Monday, April 2, 2012 8:39 AM
    Moderator
  • It is fine to have 1:n and m:n relationships defined in this way. It's as per the EF 4.x documentation. I've tried explicitly adding both ends to the m:n relationship (adding a 'Things' navigation collection to ThingMN)
      and the partial load does not work. You mention using include but this will always perform a full load, the point of this code is to filter the entities loaded. I haven't found any mention of a Query().Load() on a collection being specific to 1:n so I think this is probably a bug. Also the query itself will return results if you watch it in Sql Profiler it is that EF is not then loading the resulting entities on to the collection.
    Saturday, April 7, 2012 2:01 AM