none
Many to Many, access Junction table directly RRS feed

  • Question

  • i setup my entity relation with

    public class User {
    	public long Id { get; set; }
    	public string Name { get; set; }
    
            public virtual IList<Role> Roles { get; set; }
    } 
    
    public class Role {
    	public long Id { get; set; }
    	public string Name { get; set; }
    
            public virtual IList<User> Users { get; set; }
    } 
    
    
    modelBuilder.Entity<User>()
    	.HasMany<Role>(u => u.Roles)
    	.WithMany(r => r.Users)
    	.Map(m => m.ToTable("UserRoles")
    		.MapLeftKey("UserId")
    		.MapRightKey("RoleId")
    	);

    when 

    role.Users.Add(user)
    // or
    role.Users.Remove(user)
    it query all users from db before it add or remove (i believe is call linq to object),

    this is not an issue when the users list is small but not when the list is huge

    is there any way to directly insert or remove into the Junction table without query the full sets of users?
    i prefer not using custom sql command (context.Databse.ExecuteSqlCommand())

    thank you very much.

    Wednesday, October 23, 2013 10:19 AM

Answers

  • Yeah, what you have done actually is another way to disable the lazy load, it will not cause side issues.

    For disable lazy load:

    http://msdn.microsoft.com/en-us/data/jj574232.aspx

    You can have a check.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by TakoLittle Monday, October 28, 2013 3:47 AM
    Monday, October 28, 2013 3:40 AM
    Moderator

All replies

  • Hello,

    From your description, you want to add the new record into database directly, for this, Entity Framework has provided a method named Attach() method and I made a sample below:

    using (UserRoleContext db = new UserRoleContext())
    
                {
    
                    //db.Database.Create();
    
    
                    Role role = new Role() { Name = "Role001" };
    
    
                    db.Roles.Attach(role);
    
    
                    db.Entry<Role>(role).State = EntityState.Added;
    
    
                    db.SaveChanges();
    
                }
    

    It will insert the new record into database without querying the databasefirst.

    More information regarding Attach() method:

    http://msdn.microsoft.com/en-us/data/jj592676.aspx

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, October 24, 2013 1:46 AM
    Moderator
  • i mean add records to the Junction Table not the object table

    var role = db.Roles.FirstOrDefault();
    role.Users.Add(...); // <<-- here will query all Users records

    since the role.Users.Add are actually not adding record to Users table if adding exiting user, instead it will add record to junction table UserRoles. any way to add record to junction table without query users list?

    thank you.

    Friday, October 25, 2013 2:57 AM
  • Hi TakoLittle,

    Sorry for being misunderstanding you.

    Actually when we set the list to be virtual, it is lazy load model.

    If we do not want to load the users, we need to change the entity class to be:

    public class Role
    
        {
    
            public Role()
    
            {
    
                Users = new List<User>();
    
            }
    
            public long Id { get; set; }
    
            public string Name { get; set; }
    
            public List<User> Users { get; set; }
    
        }
    

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, October 25, 2013 9:22 AM
    Moderator
  • i solve the issue by

    context.Configuration.LazyLoadingEnabled = false;

    right before calling Add to the virtual list, and turn it on after SaveChanges

    seem working, now the add does not query all user list,
    but not sure if this will cause side issues?

    thank you very much.

    Monday, October 28, 2013 3:36 AM
  • Yeah, what you have done actually is another way to disable the lazy load, it will not cause side issues.

    For disable lazy load:

    http://msdn.microsoft.com/en-us/data/jj574232.aspx

    You can have a check.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by TakoLittle Monday, October 28, 2013 3:47 AM
    Monday, October 28, 2013 3:40 AM
    Moderator
  • thank you very much.
    Monday, October 28, 2013 3:47 AM