asp.net membership - Entity Framework 5 - Insert data into UsersInRoles Many to Many table -
i have scenario need manually insert user, roles & usersinroles entries database without using asp.net membership. when try insert many many relation usersinroles following exception
invalid column name 'role_roleid'. i'm using ef 5, database first approach pocos. have defined association in edmx should ok ... here model entities
/// <summary> /// no metadata documentation available. /// </summary> [datacontract(isreference = true)] [knowntype(typeof(user))] public partial class user : iuser { #region primitive properties /// <summary> /// no metadata documentation available. /// </summary> [datamember] public virtual system.guid applicationid { get; set; } /// <summary> /// no metadata documentation available. /// </summary> [datamember] public virtual system.guid userid { get; set; } /// <summary> /// no metadata documentation available. /// </summary> [datamember] public virtual string username { get; set; } /// <summary> /// no metadata documentation available. /// </summary> [datamember] public virtual bool isanonymous { get; set; } /// <summary> /// no metadata documentation available. /// </summary> [datamember] public virtual system.datetime lastactivitydate { get; set; } #endregion #region navigation properties /// <summary> /// no metadata documentation available. /// </summary> [notmapped] public virtual imembership membership { { return _membership; } set { if (!referenceequals(_membership, value)) { var previousvalue = _membership; _membership = value; fixupmembership(previousvalue); } } } private imembership _membership; /// <summary> /// gets or sets roles associated user /// </summary> [datamember] public virtual icollection<role> roles { { if (_roles == null) { _roles = new fixupcollection<role>(); } return _roles; } set { _roles = value; } } private icollection<role> _roles; #endregion #region association fixup private void fixupmembership(imembership previousvalue) { if (previousvalue != null && referenceequals(previousvalue.user, this)) { previousvalue.user = null; } if (membership != null) { membership.user = this; } } #endregion } note: unidirectional relationship users roles
/// <summary> /// no metadata documentation available. /// </summary> [datacontract] [knowntype(typeof(role))] public partial class role : irole { #region primitive properties /// <summary> /// no metadata documentation available. /// </summary> [datamember] public virtual system.guid applicationid { get; set; } /// <summary> /// no metadata documentation available. /// </summary> [datamember] public virtual system.guid roleid { get; set; } /// <summary> /// no metadata documentation available. /// </summary> [datamember] public virtual string rolename { get; set; } /// <summary> /// no metadata documentation available. /// </summary> [datamember] public virtual string description { get; set; } #endregion } working code
mycontext context = new mycontext(); role usersrole = new role(); usersrole.rolename = "users"; usersrole.description = "user role"; usersrole.roleid = guidextension.newsequentialguid(); usersrole.applicationid = application.id; //init users user adminuser = new user(); adminuser.username = "admin"; adminuser.isanonymous = false; adminuser.lastactivitydate = datetime.utcnow; adminuser.userid = guidextension.newsequentialguid(); adminuser.applicationid = application.id; //init user roles adminuser.roles.add(usersrole); context.user.add(adminuser); context.savechanges(); i have tried adding role database first, adding user (without added role roles collection) , same exception. added model binding via fluent api this:
modelbuilder.entity<user>() .hasmany(u => u.roles) .withmany() .map(m => { m.mapleftkey("userid"); m.maprightkey("roleid"); m.totable("usersinroles"); }); and tried add binding this:
modelbuilder.entity<user>() .hasmany(u => u.roles) .withmany() .map(m => { m.mapleftkey("userid"); m.maprightkey("roleid"); var mapping = m.totable("usersinroles"); mapping.mapleftkey("userid"); mapping.maprightkey("roleid"); }); also here sql trace above code
exec sp_executesql n'insert [dbo].[roles]([roleid], [applicationid], [rolename], [description]) values (@0, @1, @2, @3) ',n'@0 uniqueidentifier,@1 uniqueidentifier,@2 nvarchar(max) ,@3 nvarchar(max) ',@0='46e39982-e490-4f79-b457-a1ab012948ce',@1='79d75e2d-9923-48dc-a4d6-ae0ced0edd58',@2=n'users',@3=n'user role' exec sp_executesql n'insert [dbo].[users]([userid], [applicationid], [username], [isanonymous], [lastactivitydate], [role_roleid]) values (@0, @1, @2, @3, @4, null) ',n'@0 uniqueidentifier,@1 uniqueidentifier,@2 nvarchar(max) ,@3 bit,@4 datetime2(7)',@0='b5ea5052-71c9-411e-91c7-a1ab012948cf',@1='79d75e2d-9923-48dc-a4d6-ae0ced0edd58',@2=n'admin',@3=0,@4='2013-04-25 14:14:08.4930381' above can see source of issue, [role_roleid] column in user insert sql i'm not sure if have setup navigation property correctly ?
no matter above exception fluent api bindings ignored. know how add many many entries usersinroles table or i'm missing in code.
note: i'm new ef information can helpful.
update have tried save user entry database , same [role_roleid] exception, hope clarify issue bit more, maybe isn't many many insert issue rather invalid many many configuration issue ?
regards
i have found 2 issues in code, after long long code review, first issue property "hidden" in 1 of partial classes called
public virtual icollection<user> members { get; set; } it there copy-paste error, couldn't find because in collapsed region , looking users property name of navigation item generated earlier.
second issue more serious 1 slipped through, , related "database.setinitializer", db initializer put in
protected override void onmodelcreating(dbmodelbuilder modelbuilder) { database.setinitializer<coredalcontext>(null); } which wrong , should called once , put in static constructor per microsoft implementation.
static coredalcontext() { database.setinitializer<coredalcontext>(null); } i hope other ef newcomers not make same mistake did.
regards
