Linq query with multiple objects in parent child relationship

Question

I have a schema like this

Package -> Lists -> Users

All 'one to many' down the line...

So I want to run a query where I get all packages that a userID matches in users.

var pck = (from pk in context.Package
             where pk.Lists[here's my problem]

I would assume the navigation properties here would be: pk.Lists. *Users.UserId* == MyUserId, however I'm not seeing the navigation properties at the Lists level.

I haven't gotten to more complex EF queries like this yet. I've looked around the web but haven't found anything to make it click. I turn to you stack. Somebody help me see the light!

EDIT: Thanks again stack, I will do my best to pay it forward! Also, all of these answers enlightened me to the power of ef4!

Jon Skeet · Accepted Answer · 2011-06-13 16:16:53Z

8

I assume that a package contains multiple lists, and a list contains multiple users? You could try:

var pck = content.Package
     // Outdented just for Stack Overflow's width
     .Where(pk => pk.Lists.Any(list => list.Any(u => u.UserId == myUserId)));

Or use a cross-join:

var pck = from pk in content.Package
          from list in pk.Lists
          from user in list.Users
          where user.UserId == myUserId
          select ...; // Select whatever you're interested in

answered Jun 13, 2011 at 16:16

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

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

Comments

Vlad · Accepted Answer · 2011-06-13 16:16:04Z

1

context.Packages.Where(p => p.Lists.Any(l => l.Users.Contains(MyUserId)))

or, if your user is something other then just a user id,

context.Packages.Where(p => p.Lists.Any(l => l.Users.Any(u => u.Id == MyUserId)))

answered Jun 13, 2011 at 16:16

Vlad

35.7k7 gold badges82 silver badges205 bronze badges

1 Comment

abend Over a year ago

I'm starting to see the light!

Justin Niessner · Accepted Answer · 2011-06-13 16:17:12Z

1

var packages =
    context.Package.Where(p =>
        p.Lists.Any(l => 
            l.Users.Any(u => u.UserId == MyUserId
        )
    );

answered Jun 13, 2011 at 16:17

Justin Niessner

246k40 gold badges416 silver badges546 bronze badges

Comments

Jacob Proffitt · Accepted Answer · 2011-06-13 16:18:40Z

1

If the links are non-nullable and direction is package has many lists and list has many users, then the query is pretty easy.

var pck = from user in context.Users
          where user.UserId == userId
          select user.List.Package;

answered Jun 13, 2011 at 16:18

Jacob Proffitt

12.8k3 gold badges43 silver badges47 bronze badges

1 Comment

abend Over a year ago

this select technique helped me weeks after I asked the question! This page has been a great reference for me! Really opening my eyes to the ease of EF!

Magnus · Accepted Answer · 2011-06-13 16:22:32Z

1

try this:

pk.Lists.Any(l => l.Users.Any(u => u.UserId == MyUserId))

edited Jun 13, 2011 at 16:22

answered Jun 13, 2011 at 16:15

Magnus

47.3k8 gold badges88 silver badges128 bronze badges

1 Comment

abend Over a year ago

That does make perfect sense, Thanks!

Collectives™ on Stack Overflow

Linq query with multiple objects in parent child relationship

5 Answers 5

Comments

1 Comment

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related