Entity Framework 6: virtual collections are lazy loaded, even explicitly loaded into the request

Question

I have a problem with EF6 when trying to optimize queries. Consider this class with one collection:

public class Client
{
    ... a lot of properties
    public virtual List<Country> Countries { get; set; }
}

As you may know, with Lazy Loading, I have this n + 1 problem when EF tries to get all countries for each client.

I tried using Linq forecasts; eg:

        return _dbContext.Clients
            .Select(client => new
            {
                client,
                client.Countries
            }).ToList().Select(data =>
            {
                data.client.Countries = data.Countries; // Here is the problem
                return data.client;
            }).ToList();

Here I use two choices: the first for Linq projection, so EF can create SQL, and the second for matching the result with the Client class. The reason for this is because I use the repository interface that returns List<Client>.

, , EF - Lazy Loading, ( n + 1). - :

public class Client
{
    ... a lot of properties
    public List<Country> Countries { get; set; } 
}

, . , Lazy Loading.

, "" EF , Linq. ? , - ? n + 1 , 1000 .

Edit

. , Include() , , ( , , , ):

public class Client
{
    ... a lot of properties
    public virtual List<Country> Countries { get; set; }
    public virtual List<Action> Actions { get; set; }
    public virtual List<Investment> Investments { get; set; }
    public User LastUpdatedBy {
        get {
          if(Actions != null) {
              return Actions.Last();
          }       
        }
    }
}

, (Count()), Include() . , ,

        return _dbContext.Clients
              .Select(client => new
              {
                    client,
                    client.Countries,
                    NumberOfInvestments = client.Investments.Count() // this is translated to an SQL query
                    LastUpdatedBy = client.Audits.OrderByDescending(m => m.Id).FirstOrDefault(),
              }).ToList().Select(data =>
              {
                    // here I map back the data
                    return data.client;
              }).ToList();

, ( LastUpdatedBy getter/setter, , ).

Select() ( ), Include() EF.

Answer 1

,

_dbContext.LazyLoading = false;

var clientWithCountres =  _dbContext.Clients
                                    .Include(c=>c.Countries)
                                    .ToList();

Client Countries. , . .

FYI: Projection Include() , . , . fooobar.com/questions/474301/...

Answer 2

, , lambda, linq, - .

data.client client, data.Countries - client.Countries, data.client.Countries = data.Countries .

Countries, _dbContext.Clients.Include("Countries").Where() or select ().

Answer 3

, Include. MSDN https://msdn.microsoft.com/en-us/library/jj574232(v=vs.113).aspx.

, - :

 return _dbContext.Clients.Include(c=>c.Countries).ToList();

Answer 4

Im 100% , , , - .

( ), , , , .

, , :

1 , ,

return _dbContext.Clients
            .Select(client => new
            {
                client,
                Countries = client.Countries.Select(c=>c)// or a new Country
            })

2 include ( , include , . , ), :

_dbContext.Clients
            .Select(client => new
            {
                client,
                client.Countries
            }.Include(c=>c.Countries)`

3 , :

_dbContext.Clients
            .Select(client => new
            {
                client,
                Countries = client.Countries.AsEnumerable() //perhaps tolist if it works
            }`

, , , , .

IMO . , . , IMO - (, ).