EF Include Repository Pattern

Question

I use ASP Net Core Web API and EF Core. There is an interesting magic that could not be comprehended.

IEfCotntext is IEfCotntext :

 public interface IEfContext : IDisposable { DbSet<TEntity> Set<TEntity>() where TEntity : BaseEntity; ... } public class EfContext : DbContext, IEfContext { ... public new DbSet<TEntity> Set<TEntity>() where TEntity : BaseEntity { return base.Set<TEntity>(); } ... }

Repository:

 public class Repository<TEntity> : IRepository<TEntity> where TEntity : BaseEntity { public DbSet<TEntity> Entities { get { return dbset ?? context.Set<TEntity>(); } } ... public async Task<IQueryable<TEntity>> GetAsync(Expression<Func<TEntity, bool>> where = null, params Expression<Func<TEntity, object>>[] includes) { IQueryable<TEntity> query = Entities; if (includes != null) foreach (var include in includes) query = query.Include(include); if (where != null) query = query.Where(where); var result = await query.ToListAsync(); query = result.ToList().AsQueryable(); return query; } ... }

Category Category:

 public class Category : BaseEntity { public string Name { get; set; } public int? ParentId { get; set; } public virtual Category Parent { get; set; } public virtual ICollection<Product> Products { get; set; } public virtual ICollection<Category> Children { get; set; } }

Through ICategoryService I try to get data from the database:

 return await categoryRepository.GetAsync(model => model.ParentId == null, x => x.Children, x => x.Products);

In the returned data for each object from Childred , the Childen and Products properties are null . But passing by the debugger

It is only when I try to get the Results View data is returned correctly.

Tried to close connections ( .ToList() ) wherever possible. But the function also continues to work incorrectly.

As I understand it, the following happens: when you open the Results View, a request is made to select all data + include (), and then Where () is filtered.
Without a debugger, a query is assembled using IQueryable, and then sent and the result becomes Where () objects and Include () applied to them, i.e.
Therefore, I need to include ("Children.Children.Children ...") or unload all the elements into memory, and then apply Where ().

Answer 1 · 2016-12-30T11:23:05

Judging by the screenshots, the debugger you use and view the Results View before the EF context was destroyed. Accordingly, when opening a tree in the debugger, LazyLoad simply works and you even get the data that was not included in the Include . If you display all the requests that your context makes in the Output window, you will see the add. requests:

 _context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);

Looking through the returned data, you already operate 'POCO' objects, not EF proxy objects, respectively, all navigation properties that were not explicitly specified in the Include will be null .

.Database.Log is missing from EF Core. Apply loggerFacroty, etc. - Alexander Sankov

Pavel Mayorov Pavel Mayorov 48.9k five 50 110 · Answer 2 · 2016-12-30T11:34:54

Judging by the screenshots, you are viewing the query before applying the predicate. This causes the entire table to be loaded and the materialization of all objects.

When the materialized object is already in context - it will be recorded in the navigation properties regardless of the inclusions.

To leave your comments or ask the author to clarify, leave a comment to the appropriate post.
@VadimOvchinnikov why do you think this is not the answer to the question?
Perhaps, but it seems to me completely incomplete, so it looks more like a comment.
Still, the comment @AleksandrSankov would be much more suitable because he also has a solution, and not just an answer why.

Accepted Answer · 2016-12-30T16:52:05

Focus is revealed: As soon as the Results View is accessed via the debugger, a database request is immediately sent. In my case, context.Categories.Include(model=>model.Children) (everything is logical, because we want to see what is happening at this stage).

After that, the data is filtered using Where(...) . And the result is the hierarchical structure that was needed.

When called without debugging, a context.Categories.Where(model.ParentId == null).Include(model=>model.Children) request is sent context.Categories.Where(model.ParentId == null).Include(model=>model.Children) . Those. first, parent categories are selected, and the INNER JOIN for the Children collections is applied to them (respectively, only at the first level).

Therefore, you need to remember that the query is executed as soon as IQueryable is fully formed, and opening the Results View during debugging makes an additional query.

loggerFactory.AddDebug(LogLevel.Debug); in Startup.cs in Configure(...) shows this query when the Results View is expanded.

Hierarchical structure: As for unloading the hierarchical structure from the database, everything is much simpler. The idea to unload from the database all the data that are included in this structure. Those. if you need to create all categories in the form of a tree, you need to unload them all :

Make a request from the IRepository:

 var categories = await categoryRepository.GetAsync(); //т.е. все категории return categories.ToList();

Make the ViewModel

 public class CategoryViewModel { public int Id { get; set; } public string Name { get; set; } public int? ParentId { get; set; } public virtual IEnumerable<CategoryViewModel> Children { get; set; } }

Through AutoMapper all virtual properties will be populated:

 CreateMap<Category, CategoryViewModel>();

In Controller:

 var categoriesViewModel = mapper.Map<IEnumerable<Category>, IEnumerable<ViewModels.Tree.CategoryViewModel>>(categories.Where(model=>model.ParentId == null));

And in the end we get the desired result.

If you need to get only one category with all children, then accordingly you need to unload this category and all children by request, but this is another question

This answer is the most complete and comprehensive, put a daw on it.

EF Include Repository Pattern

3 answers 3

More articles: