Q:

Entity using include to load related tables but how to left outer join it

I need to load some users and some stats, but not all the stats. Normal I can load something like this:
db.stat_user.ToList()

and access it by db.stat_user.local (it then have the added items, and the preloaded)
stat_user is 1 to manny to stat_counter, and is set up with the relation, from the sql server. There holds stat and a year, I just want to preload etc. one year.
This expression works, but it loads all related stat_counters
db.stat_user.include("stat_counter").tolist();

Then I tried this:
db.stat_user
    .Include("stat_counter")
    .Where(e => e.stat_counter.Any(a => a.year == year))
    .ToList();

Still loads all, but only needs for one year!
How to get data and still have the feature of etc. db.stat_user.local. have preloaded related stat_counters?
I run with these flags:
db.Configuration.AutoDetectChangesEnabled = false;
db.Configuration.LazyLoadingEnabled = false;

Need to preload in memory, else it takes for ages to calculate.

A:

Well as Sergey mentioned its not possible right away so ended up with:
        List<stat_user> lsu = s.stat_user.ToList();
        List<int> ids = lsu.Select(e => e.id).ToList();
        List<stat_counter> lsc = s.stat_counter.Where(e => e.year == year 
                                                      && ids.Contains(e.user_fk))
                                                            .ToList();

Then lsu users is auto linked with those stat_counters loaded later in EF.
That give antoher issue, how to clear the EF, only way i found is to create a hole new instance!

