Include nested entities using LINQ

Question

I'm messing around with LINQ for the first time, and I'm using EF 4.1 code first.

I have entities containing nested Lists of other entities, for example:

class Release
{
    int ReleaseID { get; set; }
    string Title { get; set; }
    ICollection<OriginalTrack> OriginalTracks { get; set; }
}

class OriginalTrack
{
    int OriginalTrackID { get; set; }
    string Title { get; set; }
    ICollection<Release> Releases { get; set; }
    ICollection<OriginalArtist> OriginalArtists { get; set; }
}

class OriginalArtist
{
    int OriginalArtistID { get; set; }
    string Name { get; set; }
    ICollection<OriginalTrack> OriginalTracks { get; set; }
}

I'm wondering what is the quickest way, in one LINQ query, to obtain all the information for where ReleaseID == some value.

I've done my homework, but have found solutions that require implicit rebuilding of an object (usually anonymous) with the required data. I want the data out of the database in the exact format that it is held within the database, i.e. pulling a Release object with relevant ReleaseID pulls and populates all the OriginalTrack and OriginalArtist data in the Lists.

I know about Include(), but am not sure how to apply it for multiple entities.

All help greatly appreciated.

Kirk Broadhurst · Answer 1 · 2011-08-25T12:34:46.973

14

Use Include. This is the purpose of Include, and there's no reason to write a bunch of nested select statements.

context.Releases.Include("OriginalTracks.OriginalArtist")
    .Where(release => release.ReleaseID == id);

This is simpler to write, simpler to read, and preserves your existing data structure.

To use Include you need to specify the name of the property you want to return - this means the name as it exists in your code, not in the database. For example:

.Include("OriginalTracks") will include the OriginalTracks property on each Release
.Include("OriginalTracks.OriginalArtist") will include OriginalTracks property on each Release, and the OriginalArtist on each Track (note that it's not possible - syntactically or logically - to include an OriginalArtist within including the OriginalTrack)
.Include("OriginalTracks").Include("OtherProperty") will include the OriginalTracks and OtherProperty objects on each Release.

You can chain as many of these as you like, for example:

.Include("Tracks.Artist").Include("AnotherProperty")
    .Include("ThirdProperty.SomeItems").Where(r => r.something);

is perfectly valid. The only requirement is that you put the Include on the EntitySet, not on a query - you can't .Where().Include().

edited Aug 25 '11 at 12:34

answered Aug 25 '11 at 01:28

Kirk Broadhurst

27,836
16
104
169

can you give me an example of chaining the includes? I've seen where two seperate paths are specified, but not multiple nested objects. Will the example above only bring back the originalartists, or will it bring back all data on that path? – Will Bithell Aug 25 '11 at 10:40
@Will It brings back all the data on that path. I'll edit with more info. – Kirk Broadhurst Aug 25 '11 at 12:27
Thanks, that looks good.. I'm now in a pickle with searching into the included entities, I guess that's not possible? Say Tracks.Artists.Name == searchterm? – Will Bithell Aug 25 '11 at 14:43
@Will That's where it gets tricky. Call that a 'conditional include' - this is where msarchet's link is useful. (http://blogs.msdn.com/b/alexj/archive/2009/10/13/tip-37-how-to-do-a-conditional-include.aspx) Essentially you build a query using anonymous types and then select the root from that query. Check that link, or these: http://stackoverflow.com/questions/1535443/ef-query-with-conditinal-include http://stackoverflow.com/questions/1085462/conditional-include-in-linq-to-entities (search SO for 'ef conditional include') – Kirk Broadhurst Aug 25 '11 at 23:32
@KirkBroadhurst, Perhaps you referred to an older version of EF, but you actually *can* call `Include` after `Where` (or any other `IQueryable` method) because `Include` extends `IQueryable`. See https://github.com/mono/entityframework/blob/master/src/EntityFramework/QueryableExtensions.cs#L450 – haim770 Nov 04 '15 at 14:14

score 10 · Accepted Answer · answered Aug 24 '11 at 21:49

Don't worry about using include here

just do something like the following

var query = 
    from release in ctx.Releases
    select new {
        release,
        originalTracks = from track in release.OriginalTracks
                         select new {
                               track,
                               releases = track.Releases,
                               orignialArtist = from artist in track.OriginalArtists
                                                select new {
                                                     artist,
                                                     artist.OriginalTracks
                                                }
                         }
        }

var Releases = query.Select(x => x.Release);

Should load all of your data

I worked with information from this post here.

http://blogs.msdn.com/b/alexj/archive/2009/10/13/tip-37-how-to-do-a-conditional-include.aspx

Yup that seems like what I need, I think, I'll have a go at it tomorrow, but I'll mark as answer.. cheers!! — Will Bithell, Aug 24 '11 at 21:57
@msarchet This is not a conditional include as per the link you've included. That relates to conditioning including (or filtering) the child objects based on some condition; a much more interesting problem. This is a simple select with include. — Kirk Broadhurst, Aug 25 '11 at 01:28

score 9 · Answer 3 · answered Jan 12 '14 at 01:18

9

To include the nested entities without using string literals, use Select, like this:

context.Releases.Include(r => r.OriginalTracks.Select(t => t.OriginalArtist))
    .Where(release => release.ReleaseID == id);

answered Jan 12 '14 at 01:18

Edward Brey

40,302
20
199
253

Include nested entities using LINQ

3 Answers3

Linked