Entity framework. Quering parent entity with subclass property loaded

Question

There is a simple domain..

public abstract class UserComment
{
    public string Text { get; set; }
}

public class BlogComment : UserComment
{
    public Blog Blog { get; set; }
}

public class PhotoComment : UserComment
{
    public Photo Photo { get; set; }
}

Is there way to query all entities of type UserComment with properties Blog and Photo loaded?

var comment = DbContext.Set<UserComment>()
    .Include(x => x.Blog) // will not compile
    .Include(x => x.Photo) // will not compile
    .FirstOrDefault();

if (comment is PhotoComment )
{
    string url = (comment as PhotoComment).Photo.Url;
}
if (comment is BlogComment)
{
    var dateCreated = (comment as BlogComment).Blog.DateCreated;
}

Thanks!

You should accept (mark as accepted) answers to your questions if they provide useful information (not to mention upvote). It is reward for people who answer your questions. — nrodic, Oct 21 '12 at 13:33
Where is this link located? I can't see any button or link called "Accept answer". — Jekas, Oct 21 '12 at 13:37
OK, now, I suppose it is not about code first? Did you try `comment.Blog.Load()` [explanation](http://msdn.microsoft.com/en-us/library/bb896376.aspx) — nrodic, Oct 21 '12 at 13:45
I'm using code first. I looked to Load extensions, you mentioned. And found possible solution - cast parent class to child and use load extension: `context.Entry(comment as BlogComment).Reference(p => p.Blog).Load();` — Jekas, Oct 21 '12 at 13:55
You're welcome. One thing, do you have `using System.Data.Entity;`? Plain `.Include` should work with code first. — nrodic, Oct 21 '12 at 13:56

score 5 · Accepted Answer · answered Oct 21 '12 at 15:30

You will probably need two queries to get the result. If you only want the first element (FirstOrDefault) explicit loading - as you already proposed in the comments - is a good approach:

var comment = DbContext.Set<UserComment>().FirstOrDefault();

if (comment is BlogComment)
    DbContext.Entry(comment as BlogComment).Reference(bc => bc.Blog).Load();
else if (comment is PhotoComment)
    DbContext.Entry(comment as PhotoComment).Reference(pc => pc.Photo).Load();

If you want to load a list of UserComments that's not the best solution as it would require to iterate over the loaded UserComments and to call explicit loading for each element which will result in many queries.

For a list you can use the following approach that also will generate only two queries:

IEnumerable<UserComment> blogComments = DbContext.Set<UserComment>()
    .OfType<BlogComment>()
    .Include(bc => bc.Blog)
    .Cast<UserComment>()
    .AsEnumerable();

IEnumerable<UserComment> photoComments = DbContext.Set<UserComment>()
    .OfType<PhotoComment>()
    .Include(pc => pc.Photo)
    .Cast<UserComment>()
    .AsEnumerable();

List<UserComment> comments = blogComments.Concat(photoComments).ToList();

Because of the usage of AsEnumerable() this will run two separate database queries and concat the results to a single collection in memory.

LINQ-to-Entities supports Cast but for some reason it is not possible to remove the two AsEnumerable() conversions to get only a single database query and concat the results in the database. The code would still compile but I had a runtime exception about an invalid Include path.

I have tested with EF 4.1. It might be worth to test the query without AsEnumerable() with EF 5.0 to see if it still fails.

Entity framework. Quering parent entity with subclass property loaded

1 Answers1