I have the following code in aync method:
using System.Data.Entity;
// ...
protected internal async Task<Customer> GetCustomerAsync(int entityId, CancellationToken cancellationToken)
{
IQueryable<Customer> query = (await this.Repository.GetAll(cancellationToken)).Where(e => e.Id == entityId);
query = query.Include(e => e.Partners);
string sql = query.ToString();
return await query.AsNotTracking().FirstOrDefaultAsync(cancellationToken);
}
The sql variable contains, something like this:
SELECT
[Project1].[UserId] AS [UserId],
[Project1].[EntityId] AS [EntityId],
[Project1].[Id] AS [Id],
[Project1].[OtherColumn] AS [OtherColumn],
[Project1].[Name] AS [Name],
[Project1].[C1] AS [C1]
FROM ( SELECT
[Extent1].[UserId] AS [UserId],
[Extent1].[EntityId] AS [EntityId],
[Extent2].[Id] AS [Id],
[Extent2].[OtherColumn] AS [OtherColumn]
[Extent3].[Id] AS [Id1],
[Extent3].[CustomerId] AS [CustomerId],
[Extent3].[PartnerId] AS [PartnerId],
CASE WHEN ([Extent3].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
FROM [op].[CustomerAcls] AS [Extent1]
INNER JOIN [op].[Customers] AS [Extent2] ON [Extent1].[EntityId] = [Extent2].[Id]
LEFT OUTER JOIN [op].[CustomerPartners] AS [Extent3] ON [Extent2].[Id] = [Extent3].[CustomerId]
WHERE ([Extent1].[UserId] = @p__linq__0) AND ([Extent1].[AccessRight] >= @p__linq__1) AND ([Extent2].[Id] = @p__linq__2)
) AS [Project1]
ORDER BY [Project1].[UserId] ASC, [Project1].[EntityId] ASC, [Project1].[Id] ASC, [Project1].[C1] ASC
UPDATE -----
The code of the repository is like the following:
public class CustomerRepository<TAcl> : ICustomerRepository
where TAcl : class, IAcl<Customer>, new()
{
private readonly DbSet<TAcl> aclSet;
public CustomerRepository(
BranchDbContext branchDbContext,
Func<BranchDbContext, DbSet<TAcl>> aclSetFunc)
{
this.aclSet = aclSetFunc(branchDbContext);
}
public async Task<IQueryable<Customer>> GetAll(CancellationToken cancellationToken)
{
int minAccessRight = //...
int currentUserId = //...
return from acl in (from acl in this.aclSet
where acl.UserId == currentUserId &&
acl.AccessRight >= minAccessRight
select acl)
select acl.Entity;
}
}
------------
Despite of the use of the AsNoTracking() method (see return statement), and even though that I made sure the repository gives me back a LinqToEntities query (it's not already materialized), the resulting returned instance (of the method GetCustomerAsync) has still lazy loading capabilities.
The documentation is so vague, it says:
If the underlying query object does not have a AsNoTracking method, then calling this method will have no affect.
Does someone see where the problem is in my code or understand what the documentation says?
THX
This is not necessarily indicative that the entities are tracked as a result of that query. EF 6 will also still lazy load referenced entities in non-tracked results provided lazy loading is enabled and the navigation properties are virtual.
As a simple example with a Parent and Children collection:
EF 6:
using (var context = new TestDbContext())
{
var parent = context.Parents.AsNoTracking().Single(x => x.ParentId == 1);
var count = parent.Children.Count;
}
Here with lazy loading enabled and the Children declared as a virtual ICollection<Child>, EF 6 will trigger a lazy load call when parent.Children is accessed. This can be verified by running a profiler against your DB with a breakpoint and you will see the lazy load call go out.
To avoid this you would need to turn Lazy Loading off, or ensure the Children property is not declared as virtual. In either of those cases, "count" would come back as 0 (or trigger a null ref if not initialized to an empty list)
With EF core things are a bit different. By default with EF Core, Lazy Loading is disabled so "count" would always come back as 0. If you enable lazy loading proxies in your DbContext then the above code would result in an InvalidOperationException as now an AsNoTracking() entity is properly detected as Detached and throws this exception if you attempt to lazy load from a detached instance. (EF6 allowed this if the DbContext was still in scope)
Both EF6 and EF Core will not pre-populate related entities that might already be tracked when fetching a NoTracking() instance.
For example, provided lazy loading is disabled or non-virtual collection:
using (var context = new TestDbContext())
{
var parent = context.Parents.Include(x => x.Children).AsNoTracking().Single(x => x.ParentId == 1);
var count = parent.Children.Count; // eager load, will return count.
}
using (var context = new TestDbContext())
{
var parent = context.Parents.AsNoTracking().Single(x => x.ParentId == 1);
var count = parent.Children.Count; // 0.
}
using (var context = new TestDbContext())
{
var children = context.Children.Where(x => x.ParentId == 1).ToList();
var parent = context.Parents.Single(x => x.ParentId == 1);
var count = parent.Children.Count; // not eager or lazy loaded, will still return count.
// (This can be inaccurate if only *some* children were previously loaded.)
}
using (var context = new TestDbContext())
{
var children = context.Children.Where(x => x.ParentId == 1).ToList();
var parent = context.Parents.AsNoTracking().Single(x => x.ParentId == 1);
var count = parent.Children.Count; // will still return 0.
}
So if you are seeing something else unexpectedly and lazy loading is definitely disabled and confirmed not to be happening (no queries observed in a profiler) then I suspect you have something else at play.
This code for instance sends off red flags:
public async Task<IQueryable<Customer>> GetAll(CancellationToken cancellationToken)
{
int minAccessRight = //...
int currentUserId = //...
return from acl in (from acl in this.aclSet
where acl.UserId == currentUserId &&
acl.AccessRight >= minAccessRight
select acl)
select acl.Entity;
}
IQueryable returning methods do not need to be declared as async and possibly should not as this may be triggering a tracking-like query actually executing or some other odd behaviour. Instead, try:
public IQueryable<Customer> GetAll(CancellationToken cancellationToken)
{
int minAccessRight = //...
int currentUserId = //...
var query = (from acl in this.aclSet
where acl.UserId == currentUserId &&
acl.AccessRight >= minAccessRight
select acl);
return query;
}
Then in calling:
IQueryable<Customer> query = Repository.GetAll(cancellationToken)
.Include(e => e.Partners)
.Where(e => e.Id == entityId)
.AsNoTracking();
return await query.FirstOrDefaultAsync(cancellationToken);
The execution of the query is still asynchronous, and that is where the async matters. The only reason the query composition might need to be asynchronous is if in building that query resulted in some particularly expensive checks etc. that might benefit from being async, however I'd probably delegate that back out and have those values pre-fetched asynchronously and have the values passed in as parameters. (I.e. if getting minAccessRight/currentUserId etc. handed off to async calls, load these IDs and pass them in as parameters instead)