3

I have a simple model and a simple query. I'm trying to get EF to eager load my navigation properties:

// Document object has a navigation property "DocType" of type DocType
// DocType object has a navigation property "Documents" of type ICollection<Documents> that is NOT virutal

context.Set<Document>().Where(d=>d.Id == id).Include(d=>d.DocType).SingleOrDefault();

The problem is that this doesn't actually eager load DocType. The stranger thing is that excluding the Include() call does load the DocType property, but as a second query.

I've looked all around and applied every fix I found:

  1. Added a call to Include()
  2. Removed virtual from both navigation properties

Any idea what's going on here? Is it possible to coerce EF to merge this into a single query that's eager loaded?

EDIT: This is my data model:

namespace Data.Models {

    class Document {
        public int Id { get; set;}
        public int TypeId { get; set; }
        public DocType DocType { get; set; }
    }

    class DocType {
        public int Id { get; set; }
        public string FullName { get; set; }
        public ICollection<Document> Documents { get; set; }
    }
}

namespace Data.Mappings {

    internal class DocumentTypeConfiguration : EntityTypeConfiguration<Document> {

        public DocumentTypeConfiguration() {
            ToTable("ProsDocs");

            HasKey(m => m.Id);

            Property(m => m.Id)
                .HasColumnName("ProsDocId")
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

            Property(m => m.TypeId)
                .HasColumnName("ProsDocTypeId")
                .HasMaxLength(3);

            HasRequired(d => d.DocType)
                .WithMany(dt=>dt.Documents)
                .WithForeignKey(d=>d.TypeId);
        }
    }

    internal class DocTypeTypeConfiguration : EntityTypeConfiguration<DocType> {

        public DocTypeTypeConfiguration() {
            ToTable("DocType");

            HasKey(m => m.Id);

            Property(m => m.Id)
                .HasColumnName("DocTypeId")
                .HasMaxLength(4);

            Property(m => m.FullName)
                .HasColumnName("DocTypeDesc")
                .HasMaxLength(255);
        }
    }
}

The oddest thing is that when I call:

context.Set<Document>().Find(id);

The DocType properties are populated, but EF does this by executing two separate queries. Is it possible to design this in such a way the EF understands that this can be accomplished with one query?

EDIT 2: This question seems to address the same problem, but only states the calling Include() fixes it, which is not working in my case.

Community
  • 1
  • 1
Stephen Collins
  • 3,124
  • 8
  • 35
  • 57
  • Does it not eager load *`DocType`*? Or does it not eager load `DocType.Documents`, as mentioned in your comments? – Kirk Woll Sep 04 '14 at 04:45
  • The result of the query is a populated collection of `Document`s, with the `DocType` properties set to `null`. When I check the SQL being executed, the query does not include anything about `DocType` and there is not subsequent SQL being executed to retrieve that data. – Stephen Collins Sep 04 '14 at 04:47
  • I can't help further, but I've used EF for years and have never seen this behavior. I hope you find a resolution. – Kirk Woll Sep 04 '14 at 04:50
  • I'm afraid if your foreign key Id is `TypeId` and your foreign key reference is `DocType`, it doesn't match the convention. Did you properly mention this fluent api `.HasForeignKey(d => d.TypeId);` or using Foreign Key attribute `[ForeignKey("DocType")]` on top of `TypeId` property? – Yuliam Chandra Sep 04 '14 at 04:54
  • You need to include your entity definitions, including attributes as well as any fluent mappings... Otherwise we have no idea how your model is defined. – Erik Funkenbusch Sep 04 '14 at 04:56
  • Yes, I have. Like I mentioned, EXCLUDING `Include()` loads the data correctly, but as two separate queries. I'm trying to get EF to execute a single query that retrieves all the data at once. – Stephen Collins Sep 04 '14 at 04:56
  • @Yuliam, this is the exact data structure as the question you helped me with a few minutes ago. – Stephen Collins Sep 04 '14 at 04:57
  • @ErikFunkenbusch, I've included my data model in the question. – Stephen Collins Sep 04 '14 at 05:06
  • That's your fluent mappings, not your entities. It's useless without them. – Erik Funkenbusch Sep 04 '14 at 05:20
  • @ErikFunkenbusch, I've added my models. – Stephen Collins Sep 04 '14 at 05:26
  • I there anything uncommon (= not straightforward/standard) about the `DocType` table in the database? – Gert Arnold Sep 04 '14 at 10:00
  • Not other than you see in the type configuration there. – Stephen Collins Sep 04 '14 at 11:01

2 Answers2

0

To include navigation properties I use this syntax (with quotes in the Include):

context.Documents.Where(d=>d.Id == id).Include("DocType").SingleOrDefault();

VanRaidex
  • 21
  • You're saying that using a string instead of expression is preferable? Somehow that feels like that defeats the purpose of having expressions as an option. – Stephen Collins Sep 04 '14 at 05:15
  • I´m not saying what is preferable or not. I´m just saying what code works for me. – VanRaidex Sep 04 '14 at 05:20
0

In general case, using string instead of expression (as mention @vanraidex) is not a good practice. In general case. However, when using third party providers (e.g. Oracle Provider) it can be the only way to get correct sql (with joins).

So, if you using special Data Provider and .Include() method doesn't work, try to use string instead of expression.

context.Documents.Where(d=>d.Id == id).Include("DocType").SingleOrDefault();
resnyanskiy
  • 1,490
  • 1
  • 22
  • 23