CODEWITHSUNDEEP
c#sqllinqentity-frameworkmany-to-many
bwerks
bwerks

Reputation: 8991

How to load many-to-many object graphs using Entity Framework?

I have a many-to-many relationship (for this example "Left" "Right" and "Joinder") and another entity "Satellite" that keys into "Left." It just so happens that Sattelite's FK also has a unique index on it. My goal is to load one Joinder entity, along with its Left and Right entities, using properties on Satellite for a where clause.

I've tried a number of approaches, but my vocabulary for Linq is weak and I don't even really know the terms for what I'm looking for.

var joinder = dbContext.Joinders
                .Include(j => j.Left)
                .Include(j => j.Right)
                .Include(j => j.Left.Satellites)
                .FirstOrDefault(s => s.Name == "Hubble");

This doesn't work because the FirstOrDefault clause doesn't have context of s to analyze the name.

var joinder = dbContext.Joinders
                .Include(j => j.Left)
                .Include(j => j.Right)
                .Include(j => j.Left.Satellites)
                .Select(j => j.Left.Satellites)
                .Where(s => s.Name == "Hubble");

This doesn't work because the type coming out of Select is IQueryable<Collection<Satellite>> which is confusing.

var query = from j in dbContext.Joinders
    join l in dbContext.Lefts on j.LeftId equals l.Id
    join r in dbContext.Rights on j.RightId equals r.Id
    join s in dbContext.Satellites on l.Id equals s.LeftId
    where s.Name == "Hubble"
    select j;

This query compiles and runs, but returns totally dehydrated objects to me--the Joinder reference I get back has Left and Right properties both null.

var query = from j in dbContext.Joinders
    join l in dbContext.Lefts on j.LeftId equals l.Id
    join r in dbContext.Rights on j.RightId equals r.Id
    join s in dbContext.Satellites on l.Id equals s.LeftId
    where s.Name == "Hubble"
    select new Joinder
    {
        Left = l,
        Right = r,
        Left.Satellites = ...?
    };

This doesn't appear to work because I can't seem to dereference these property names inside an automatic initializer.

Anyone know how to do this? Essentially I would like to search "entity framework many-to-many deep load" but I guess not everyone else would word it like me.

Upvotes: 1

Views: 1059

Answers (2)

Alexandre Rondeau
Alexandre Rondeau

Reputation: 2687

Try this

var joinders = dbContext.Joinders
                        .Include(j => j.Left)
                        .Include(j => j.Right)
                        .Include(j => j.Left.Satellites)
                        .Where(j => j.Left.Satellites.Any(s => s.Name == "Hubble");

This will return All Joinders where any of the Satellites linked to a "Left" has the name "Hubble". I know that Left and Right will be eager loaded, but for Satellites, I'm not sure.

Upvotes: 0

Slauma
Slauma

Reputation: 177133

var joinder = dbContext.Joinders
    .Include(j => j.Right)
    .Include(j => j.Left.Satellites)
    .FirstOrDefault(j => j.Left.Satellites.Any(s => s.Name == "Hubble"));

It returns the first joinder that has at least one satellite with the given name. .Include(j => j.Left.Satellites) will include the Left entity as well (everything that is on the path to the last property), so that a separate Include(j => j.Left) is not necessary.

Edit

If you don't want to load the related Satellites together with the Joinder, just replace .Include(j => j.Left.Satellites) by .Include(j => j.Left). The predicate in FirstOrDefault (that depends on Satellite properties) will still work.

Upvotes: 2

Related Questions