EntityFramework code first keys

Question

I have following tables: User, UserGroups, and UserInGroups. You can see them on picture below. When i call User i want to be able to get Groups that user is in (UserInGroups). I am reading materials about EntityFramework but i am unable to make connections in code to to that, what am i missing? Do i need to connect them onModelCreating?

Currently i am not getting any data from UserInGroup or UserGroups.

DB looks like this

My classes look like this:

public class User : BaseEntity
{
    public int RoleId { get; set; }

    public Role Role { get; set; }

    public UserGroup UserGroup { get; set; }

    public UserInGroup UserInGroup { get; set; }

}

public class UserGroup : BaseEntity
{
    public UserGroup()
    {
        Users = new List<User>();
        UserInGroups = new List<UserInGroup>();
    }

    [StringLength(255)]
    public string Name { get; set; }

    public string KeyName { get; set; }

    public List<User> Users { get; set; }

    public List<UserInGroup> UserInGroups { get; set; }

}

public class UserInGroup : BaseEntity
{
    public UserInGroup()
    {
        Users = new List<User>();
        UserGroups = new List<UserGroup>();
    }

    public int UserId { get; set; }

    public User User { get; set; }

    public int UserGroupId { get; set; }

    public UserGroup UserGroup { get; set; }

    public List<User> Users { get; set; }

    public List<UserGroup> UserGroups { get; set; }

}

DbContext:

public DbSet<GlobalSettings> GlobalSettings { get; set; }
    public DbSet<Role> Roles { get; set; }
    public DbSet<User> Users { get; set; }
    public DbSet<UserGroup> UserGroups { get; set; }
    public DbSet<UserInGroup> UsersInGroups { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<GlobalSettings>().Property(x => x.Key).HasColumnAnnotation("Index", new IndexAnnotation(new[] { new IndexAttribute("Index_VariablenName") { IsClustered = false, IsUnique = true } }));
    }

you should not have `List Users` in UserGroup and you should not have collections in UserInGroup. If I well undestand your model a user can join a group only by a UserInGroup. — tschmit007, Sep 21 '15 at 14:07
I have added DB context to top post. DB i created without problems, UserInGroups does have foreign keys to User and Groups tables. Only problem i am facing is that i don't know how to get data in code. @tschmit007 yes user can join group only in UserInGroup I removed List from both User and UserGroup — azza idz, Sep 22 '15 at 08:43
NHibernate will let you create a many-to-many relation between User and UserGroup by specifying the UsersInGroups table only in mapping.With Entity Framework you must have the association entity: http://stackoverflow.com/a/19343047/995219 — user995219, Sep 22 '15 at 11:49

Sherif Ahmed · Answer 1 · 2015-09-22T11:43:52.410

public abstract partial class BaseEntity
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
}

public class User : BaseEntity
{
    public string Username { get; set; }
    public string Title { get; set; }
    public FirstName { get; set; }
    public string LasName { get; set; }
    public Genders Gender { get; set; }
    public string Email { get; set; }
    public int RoleId { get; set; }
    public string TechUser { get; set; }
    public DateTime TechChangeDate { get; set; }
    public int TechVersion { get; set; }
    public bool IsDeleted { get; set; }

    public virtual Role Role { get; set; }
    public virtual ICollection<UserInGroup> UserInGroups { get; set; }
}

public class UserInGroup : BaseEntity
{
    public int UserId { get; set; }
    public int UserGroupId { get; set; }
    public string TechUser { get; set; }
    public DateTime TechChangeDate { get; set; }
    public int TechVersion { get; set; }
    public bool IsDeleted { get; set; }

    public virtual User User { get; set; }
    public virtual UserGroup UserGroup { get; set; }
}

public class UserGroup : BaseEntity
{
    public string Name { get; set; }
    public string KeyName { get; set; }
    public string TechUser { get; set; }
    public DateTime TechChangeDate { get; set; }
    public int TechVersion { get; set; }
    public bool IsDeleted { get; set; }
}

public class Role : BaseEntity
{
    public string Name { get; set; }
}

public enum Genders 
{
    Male = 1,
    Female = 2
}

Ty, i understand connection now, much appreciated. – azza idz Sep 22 '15 at 21:31 — azza idz, Sep 22 '15 at 21:31

Mark Shevchenko · Answer 2 · 2015-09-23T04:16:06.163

You can use two methods to fill navigation properties. First is lazy-loading and second is explicit specifying of required properties.

For lazy-loading you should declare your navigation properties as virtual:

public class User : BaseEntity
{
    public int RoleId { get; set; }

    public virtual Role Role { get; set; }

    public virtual UserGroup UserGroup { get; set; }

    public virtual UserInGroup UserInGroup { get; set; }
}

public class UserGroup : BaseEntity
{
    public UserGroup()
    {
        Users = new HashSet<User>(); // HashSet is more effective than List
        UserInGroups = new HashSet<UserInGroup>();
    }

    [StringLength(255)]
    public string Name { get; set; }

    public string KeyName { get; set; }

    public virtual ICollection<User> Users { get; set; } // ICollection is less restrective

    public virtual ICollection<UserInGroup> UserInGroups { get; set; }
}

Now, you can load f.e. single user:

var justUser = dbContext.Users.Single(u => u.Id == 100);

When you need its properties they will by transparently loaded:

foreach (var userInGroup in user.UsersInGroups) // here is second loading
{
    . . .
}

The second way is the calling of the Include method to explicit specifying required properties:

var userWithGroups = dbContext.Users
                              .Include(u => u.UserInGroups) // include single navigation property
                              .Include(ugs => ugs.Groups.Select(ug => ug.Group)) // include collection navigation property
                              .Single(u => u.Id == 100); // get the user with specified id and filled specified properties

Btw, how can i get only non deleted UserInGroup results, this can't be done with .Include from what i see — azza idz, Oct 06 '15 at 08:57
@azzaidz Deleted records should not appear in resultsets. If you mean, how to append additional conditions to query, you can use the `Where` method in addition to the `Include`: `Users.Include(u => u.UserInGroups).Where(u => u.UserInGroups.Contains(...))`. — Mark Shevchenko, Oct 06 '15 at 09:11

EntityFramework code first keys

2 Answers2