Listing entities pointed by foreign keys in Entity Framework

Question

I have two Entities, let's say Car and Photo. Each photo has foreign key to Car, so each car has set of its photos.

I want to list some subset of cars and for each listed car I want to list all of each photos.

How can I do this in Entity Framework with 1 db query? I know from the beginning that I would need photos.

My code for now look like:

var carList = CarEntities.Where(...).ToList();
foreach(var car in carList){
    var photoList = car.Photos.ToList();
}

I think, EF would do separately db query for each car.

"I think, EF would do separately db query for each car." Why do you think this? Or do you mean for each car's photos? — cadrell0, Nov 21 '12 at 16:23
Let's say D -fk-> C -fk-> B -fk-> A. If I list all AEntities in EF I probably don't want list all BEs pointed any A, all CEs pointes any ... So I think EF would get from db data when needed, it would be when I call `A.Bs` ar `A.Bs.First().Cs` or sth like this. — Ari, Nov 21 '12 at 16:39

score 6 · Accepted Answer · answered Nov 21 '12 at 16:28

6

You can tell Entity Framework in include Photos when querying Cars.

var carList = CarEntities.Include(c => c.Photos).Where(...).ToList();

answered Nov 21 '12 at 16:28

ckal

score 5 · Answer 2 · edited May 23 '17 at 10:24

5

ckal's answer is pretty close except use include in the end otherwise EF may not always include it (cant recall exact reason at the moment),

var carList = CarEntities.Where(...).Include(c => c.Photos).ToList();

edited May 23 '17 at 10:24

Community

answered Nov 21 '12 at 16:30

activebiz

Charlie Salts · Answer 3 · 2012-11-21T16:49:54.373

1

"Select new" is what you likely want to do. Create a new class called CarWithPhotos and use that to return a set of results:

var carWithPhotos = from car in CarEntities
                    where (...) 
                    select new CarWithPhotos(car, car.Photos.ToList());

As I understand it, this compiles to one a single database trip, which I think is what you're after.

Edit: I've used this technique when the objects I'm querying are large and I don't always want to retrieve an entire "Car" object for example.

edited Nov 21 '12 at 16:49

answered Nov 21 '12 at 16:27

Charlie Salts

I don't want to create fake class for this. I want to use EF features to work with all I have. Otherwise it won't work for more complex usage. – Ari Nov 21 '12 at 16:43
The other answers will work just fine. I've edited my answer to give some rationale behind why someone else might use my approach. – Charlie Salts Nov 21 '12 at 16:51

3 Answers3