Check if user exists and return user + roles EF6

Question

I'm trying to figure out how can i retrieve roles with Any and check if user exists on the same query and return as SingleOrDefault() but doesn't seem to work.

private User Authenticate(LoginViewModel model)
{
     using (HutLogisticaContext context = new HutLogisticaContext())
     {
         return context.Users.Where(u => u.Username.Contains(model.Username)).SingleOrDefault();
     }
}

However it won't let met add single or default if i add Any. How can i do both on same query?

EDIT

I forgot to mention but I want to return the user object with all the roles related to it, not sure if it's possible without navigation property because i could not use include

public class Role
{
    public int Id { get; set; }
    public string Nome { get; set; }

    public ICollection<User> Users { get; set; }
    public ICollection<Claim> Claims { get; set; }
}

public class User
{
    public int Id { get; set; }
    public string Username { get; set; }
    public DateTime? DataSessao { get; set; }
    public bool IsActivo { get; set; }

    public virtual Colaborador Colaborador { get; set; }

    public ICollection<Role> Roles { get; set; }
    public ICollection<App> Apps { get; set; }
}

i'm using EF 6 so this created automatically the join table with UserId and RoleId as PK and FK

It's a many to many relation, so it should be returning every role where it matches the user ID, I saw this on an example, I mean, it shoudl return the user object with all the roles associated — Jackal, Apr 09 '19 at 09:57
I want to return the user object with the roles, i'm not sure if this is possible without navigation property tho — Jackal, Apr 09 '19 at 10:01
And whats the filter criteria for this? either UserID or Username or any else — er-sho, Apr 09 '19 at 10:04
Do you have set PK, FK and is there navigation property available. Could you please post User and Role classes? and all relevant classes? — er-sho, Apr 09 '19 at 10:08
As you said *I want to return the user object with the roles,* then you can try like this => `context.Users.Where(x => x.Id == 123).SingleOrDefault();` — er-sho, Apr 09 '19 at 10:13
problem with this the return type of function is User and roles at end turns it into a collection — Jackal, Apr 09 '19 at 10:16
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/191517/discussion-between-er-sho-and-jackal). — er-sho, Apr 09 '19 at 10:16

score 1 · Answer 1 · answered Apr 09 '19 at 10:27

Since your user EF entity already has the roles association setup as a navigation collection property, i.e.

public class User
{
    ...
    public ICollection<Role> Roles { get; set; }
   ...

What you can do use tell EF to eager load this navigation when you retrieve your user:

return context.Users
  .Include(u => u.Roles)  // << i.e. Force EF to Join the UserRoles assocated with User
  .Where(...)
  .SingleOrDefault()

In the calling method, you can now access the user.Roles, or use your user.Roles.Any() logic to see whether a user has the role etc.

There's a couple of other things - Since user names seemingly should be unique in the System, Contains (mapped to SQL IN) seems the wrong tool here. I would do a direct comparison, especially if you have the default Case Insensitive collation on the User.UserName column. - SingleOrDefault also has an overload allowing for the same predicate as a Where filter, so you can simplify this to:

return context.Users
   .Include(u => u.Roles)
   .SingleOrDefault(u => u.UserName == model.Username);

Thanks for the correct answer, sorry i can't choose it right because someone gave me this solution on chat before this post — Jackal, Apr 09 '19 at 10:33
Out of interest, why did you say that you can't use `.Include` in the OP? Eager loading with `.Include` is a really powerful tool which allows you to precisely define the 'depth' ([can be deeply nested](https://stackoverflow.com/a/29092178)) - of table joins in SQL, and easily allows you to build up a entity and all it's relations in memory (e.g. like an AggregateRoot in DDD terminology). — StuartLC, Apr 09 '19 at 10:36
I'm not sure, maybe i did a mistake when writting include, but when i tried it gave me an exception about not having navigation. Probably a syntax error, i'm not very experienced with EF yet — Jackal, Apr 09 '19 at 10:39

score 1 · Accepted Answer · answered Apr 09 '19 at 10:40

After the long discussion on chat the below code works for OP.

private User Authenticate(LoginViewModel model) 
{ 
    using (HutLogisticaContext context = new HutLogisticaContext()) 
    { 
        return context.Users
                      .Include(u => u.Roles)
                      .Where(u => u.Id == 123)
                      .SingleOrDefault(); 
    } 
}

Check if user exists and return user + roles EF6

2 Answers2