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.
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()
.
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
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);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With