EF Core 2.0 Roles Navigation Property only work for one user in collection

Question

I followed the guide here : reference

When I try to use include Roles on my user's collection only one of them actually return roles. Other user doesn't have any roles collection despite the fact that there are in the database. It feels like the roles fill out only for one user. If I change the OrderBy condition well it will be another user that will have Roles.

I really don't know what could create this behavior.

Here is my code to permit roles navigation.

In my user context :

builder.Entity<ESUserIdentity>()
       .HasMany(e => e.Roles)
       .WithOne()
       .HasForeignKey(e => e.UserId)
       .IsRequired()
       .OnDelete(DeleteBehavior.Cascade);

builder.Entity<IdentityUserRole<Guid>>(i =>
{
    i.ToTable("AspNetUserRoles");
    i.HasKey(x => new { x.RoleId, x.UserId });
});

In my user identity :

  public class ESUserIdentity : IdentityUser<Guid>

  public virtual ICollection<IdentityUserRole<Guid>> Roles { get; } = new List<IdentityUserRole<Guid>>();

In my services :

       services.AddIdentity<ESUserIdentity, IdentityRole<Guid>>()
        .AddEntityFrameworkStores<ESUsersContext>()
        .AddDefaultTokenProviders();

My query used :

       identities = await _dbUsers.Users
            .Include(r => r.Roles)
            .OrderBy(order)
            .Where(q =>
                (q.FullName != null && q.FullName.ContainsValue(query.search) ||
                q.UserName != null && q.UserName.ContainsValue(query.search) ||
                q.Email != null && q.Email.ContainsValue(query.search) ||
                query.search == null) &&
                q.Companyid == cieId &&
                q.Deleted == false)
            .Skip((query.limit * (query.page - 1))).Take(query.limit)
            .AsNoTracking().ToArrayAsync();

(Note that I have two users in my database)

Here is a screenshot of my problem for proof :

SQL Generate from this method IQueryableExtensions -> https://github.com/aspnet/EntityFrameworkCore/issues/6482 Seem not to convert all :

    SELECT [r].[Id], [r].[AccessFailedCount], [r].[Companyid], [r].[ConcurrencyStamp], [r].[Deleted], [r].[DeletedDate], [r].[Email], [r].[EmailConfirmed], [r].[FullName], [r].[HasSeenTutorial], [r].[Language], [r].[LockoutEnabled], [r].[LockoutEnd], [r].[NormalizedEmail], [r].[NormalizedUserName], [r].[PasswordHash], [r].[PhoneNumber], [r].[PhoneNumberConfirmed], [r].[SecurityStamp], [r].[Status], [r].[TimezoneId], [r].[TwoFactorEnabled], [r].[UserName]
FROM [AspNetUsers] AS [r]
WHERE [r].[Deleted] = 0
ORDER BY [r].[Id] DESC

Can you post the SQL of the query? (if you save the `IQueryable` into a local variable and do a .`ToString()` might help spot something. — Michal Ciechan, Jun 06 '18 at 12:36
With 2.0 you cannot just ToString() IQueryable, i did take the IQueryableExtensions from https://github.com/aspnet/EntityFrameworkCore/issues/6482 unfortunately it seem to not convert all the SQL — Pilouk, Jun 06 '18 at 13:13
Can you confirm that both users returned from the `dbUsers.Users` context query have the same `GUID` as the 2 single users above them? — Michal Ciechan, Jun 06 '18 at 19:54

score 0 · Answer 1 · edited Jun 11 '18 at 14:35

0

It was caused by my ContainsValue extension method which I use everywhere but for some reason, when using with roles these will not be included for the first user.

Method :

public static bool ContainsValue(this string str, string value)
{
    bool rs = false;

    if (str != null && value != null)
        rs = str.ToUpper().Contains(value.ToUpper());

    return rs;
}

edited Jun 11 '18 at 14:35

johnny 5

19,893
50
121
195

answered Jun 11 '18 at 14:00

Pilouk

1,267
1
18
36

It's not the roles, it's the underlying Entity Framework IQueryable provider, which can not translate this this Extension method to sql. Todo so, you need to build an expression tree `>`. That said `Contains` should be enough alone, if you don't have some exotic database configuration. – Christian Gollhardt Jun 11 '18 at 14:42
Run the console app with logging instead of iis. I bet you have many warnings about client side evaluation. – Christian Gollhardt Jun 12 '18 at 13:04

EF Core 2.0 Roles Navigation Property only work for one user in collection

1 Answers1