CODEWITHSUNDEEP
c#-4.0entity-framework-5eager-loading
Álvaro García
Álvaro García

Reputation: 19356

how to filter the child entities but get all with eager loading?

I have two tables in my database in a 1:N relation and I would like to do a left join query with eager loading.

My tables are:

Well, in a video I can have many versions (DVD, Blu-Ray... etc) and a version only can belong to a video.

I would like to get all the videos which I have at least one available version (perhaps some version are in possession of one friend).

I would like to get all the videos that have at least avaliable version, but of this videos I want all the versions, avaliable and not avaliable.

So the first step is to know all the videos that have at least ona avaliable version and the second step is to get all the videos and all their versions (avaliable and not avaliable).

I would like to that with raw sql but how it is not possible to use eager loading with raw sql, I would like to use linq.

I want to use eager loading to use only one query to the database and not many, and because I would like to populate the collection versions in the video entity with its versions.

Thanks.

Upvotes: 1

Views: 297

Answers (1)

Slauma
Slauma

Reputation: 177133

The solution using LINQ is rather straight-forward and would be:

var videos = context.Videos
    .Include(v => v.Versions)
    .Where(v => v.Versions.Any(vers => vers.Available))
    .ToList();

If you really prefer raw SQL for this you can extract the SQL from this LINQ query:

var sql = context.Videos
    .Include(v => v.Versions)
    .Where(v => v.Versions.Any(vers => vers.Available))
    .ToString();

Edit

Most likely a query with raw sql won't populate navigation properties, so it doesn't seem to be possible to use it for eager loading. See the discussion here, in the answer and its comments.

Upvotes: 1

Related Questions