20

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.

Improve this question

3 Answers 3

Reset to default
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().

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

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 It brings back all the data on that path. I'll edit with more info.
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 That's where it gets tricky. Call that a 'conditional include' - this is where msarchet's link is useful. (blogs.msdn.com/b/alexj/archive/2009/10/13/…) Essentially you build a query using anonymous types and then select the root from that query. Check that link, or these: stackoverflow.com/questions/1535443/… stackoverflow.com/questions/1085462/… (search SO for 'ef conditional include')
@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 github.com/mono/entityframework/blob/master/src/EntityFramework/…
10

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

Improve this answer

2 Comments

Yup that seems like what I need, I think, I'll have a go at it tomorrow, but I'll mark as answer.. cheers!!
@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.
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);
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.