Having troubles mapping standard aspnet_ tables.

[Pete]

There are four tables that cover permission.

aspnet_Roles
aspnet_UsersInRole
aspnet_User
aspnet_Membership

These are the standard MS tables. I plan to just use these tables for
reading only. Then use the standard membership routines to modify
the data. Mostly I am doing this to test mapping on a set of tables
everyone has. My database is express. I am having problems with the
many to many

Here are the EO:


public class UserRoles
{
public virtual Guid RoleId { get; set; }
public virtual string RoleName { get; set; }
public virtual IList<UserPermission> UserWithRole { get;
private set; }

public UserRoles()
{
UserWithRole = new List<UserPermission>();
}

}

public class UserPermission
{
public virtual Guid UserId { get; private set; }
public virtual string UserName { get; set; }
public virtual List<UserRoles> allRoles { get; set; }
public virtual Membership oneMembership { get; private set; }

public virtual void AssignMembership(Membership membership)
{
oneMembership = membership;
membership.Owner = this;
}

public virtual void AddUserRole(UserRoles userRole)
{
userRole.UserWithRole.Add(this);
allRoles.Add(userRole);
}

public UserPermission()
{
allRoles = new List<UserRoles>();
}
}

public class Membership
{
public virtual Guid UserId { get; set; }
public virtual string Email { get; set; }
public virtual bool IsLockedOut { get; set; }
public virtual bool IsApproved { get; set; }
public virtual DateTime LastLoginDate { get; set; }
public virtual UserPermission Owner { get; set; }
}


Here are my mappings

public class UserRolesMap : ClassMap<UserRoles>
{
public UserRolesMap()
{
Id(x => x.RoleId);
Map(x => x.RoleName);


HasManyToMany<UserPermission>(x => x.UserWithRole)
.Cascade.All().IsInverse()
.WithTableName("[SnapData].[dbo].[aspnet_UsersInRole]");

WithTable("[SnapData].[dbo].[aspnet_Users]");
}
}

public class UserPermissionMap : ClassMap<UserPermission>
{
public UserPermissionMap()
{
Id(x => x.UserId);
Map(x => x.UserName);
//Map(x => x.LastActivityDate);
WithTable("[SnapData].[dbo].[aspnet_Users]");

HasManyToMany<UserRoles>(x => x.allRoles)
.Cascade.All()
.AsBag()
.WithTableName("[SnapData].[dbo].
[aspnet_UserInRole]");

HasOne(x => x.oneMembership)
.PropertyRef(p => p.Owner)
.Cascade.All()
.FetchType.Join();

}
}

public class MembershipMap : ClassMap<Membership>
{
public MembershipMap()
{
Id(x => x.UserId);
Map(x => x.Email);
Map(x => x.IsLockedOut);
Map(x => x.IsApproved);
Map(x => x.LastLoginDate);

References(x => x.Owner)
.WithUniqueConstraint()
.TheColumnNameIs("UserId")
.LazyLoad()
.Cascade.None();

WithTable("[SnapData].[dbo].[aspnet_Membership]");
}
}

You can tell from the UserPermission mapping that I am mapping one to
one for aspnet_Membership to aspnet_Users. I am also mapping many to
many for aspnet_UserRoles to aspnet_User. My one to one is working
fine. My many to many is not working. I think it mght have to do
lack of understanding of List<> vs bag

Error Message from nUnit:
_Test_EOMapping.UserPermissionTest.DisplayOneRow:
NHibernate.PropertyAccessException : The type
NHibernate.Collection.Generic.PersistentGenericBag`1
[SnapsInTime.EO.UserRoles] can not be assigned to a property of type
System.Collections.Generic.List`1[SnapsInTime.EO.UserRoles] setter of
SnapsInTime.EO.UserPermission.allRoles
----> System.ArgumentException : Object of type
'NHibernate.Collection.Generic.PersistentGenericBag`1
[SnapsInTime.EO.UserRoles]' cannot be converted to type
'System.Collections.Generic.List`1[SnapsInTime.EO.UserRoles]'.

Thanks in advance
Pete

[Pete]

[James Gregory]

Your UserPermission.allRoles is an List<T>, when it should be an IList<T>; that's why you're getting the conversion error. 

[Pete]

James, thanks for the quick response. The IList fix the problem.
But I am still having issues with the collections. I am now getting
this error message....

_Test_EOMapping.UserPermissionTest.DisplayOneRow:
NHibernate.Exceptions.GenericADOException : could not initialize a
collection: [SnapsInTime.EO.UserPermission.allRoles#ab68b9ac-f2e5-4147-
a143-00a405d47e39][SQL: SELECT allroles0_.UserPermission_id as
UserPerm1_1_, allroles0_.UserRoles_id as UserRoles2_1_,
userroles1_.RoleId as RoleId1_0_, userroles1_.RoleName as RoleName1_0_
FROM [SnapData].[dbo].[aspnet_UsersInRole] allroles0_ left outer join
[SnapData].[dbo].[aspnet_Users] userroles1_ on
allroles0_.UserRoles_id=userroles1_.RoleId WHERE
allroles0_.UserPermission_id=?]
----> System.Data.SqlClient.SqlException : Invalid object name
'SnapData.dbo.aspnet_UsersInRole'.

[James Gregory]

Is there a reason you're using the full database.schema.table format?
Have you tried just specifying the table name?

[Pete]

Yes, if I just specify the the table name, sometimes the DB likes
me and will find the table other times it will not like me and tell me
the table is not found. I get this with linq-sql too. This is not
a fluent issue. Therefore if I specify the full db.schema.table I do
not have an issue about access.

On Feb 9, 7:43 am, James Gregory <jagregory....@gmail.com> wrote:
> Is there a reason you're using the full database.schema.table format?
> Have you tried just specifying the table name?
>

> On 2/9/09, Pete <pete554is...@snapsintime.com> wrote:
>
>
>
>
>
> > James,  thanks for the quick response.   The IList fix the problem.
> > But I am still having issues with the collections.   I am now getting
> > this error message....
>
> > _Test_EOMapping.UserPermissionTest.DisplayOneRow:
> > NHibernate.Exceptions.GenericADOException : could not initialize a
> > collection: [SnapsInTime.EO.UserPermission.allRoles#ab68b9ac-f2e5-4147-
> > a143-00a405d47e39][SQL: SELECT allroles0_.UserPermission_id as
> > UserPerm1_1_, allroles0_.UserRoles_id as UserRoles2_1_,
> > userroles1_.RoleId as RoleId1_0_, userroles1_.RoleName as RoleName1_0_
> > FROM [SnapData].[dbo].[aspnet_UsersInRole] allroles0_ left outer join
> > [SnapData].[dbo].[aspnet_Users] userroles1_ on
> > allroles0_.UserRoles_id=userroles1_.RoleId WHERE
> > allroles0_.UserPermission_id=?]
> >   ----> System.Data.SqlClient.SqlException : Invalid object name
> > 'SnapData.dbo.aspnet_UsersInRole'.
>
> > On Feb 8, 2:24 pm, James Gregory <jagregory....@gmail.com> wrote:
> >> Your UserPermission.allRoles is an List<T>, when it should be an IList<T>;

> >> that's why you're getting the conversion error.- Hide quoted text -
>
> - Show quoted text -

[James Gregory]

I'd say that issue is still affecting you then, because that exception
is saying it can't find the table.

Have you investigated this issue much? Sounds like it's either
something wrong with your connection string or the priviledges for the
user you're connecting as.

On 2/9/09, Pete <pete55...@snapsintime.com> wrote:
>

[Brian DeMarzo]

Don't put the database name in the mapping file. Let NHibernate's
connection string figure out the database location. In your mappings,
you only need to specify the table names (e.g. 'aspnet_users') in the
WithTable() method. If you need to identify schema, specify SchemaIs
("myschemaname"). NHibernate takes it from there.


- b

On Feb 9, 10:28 am, James Gregory <jagregory....@gmail.com> wrote:
> I'd say that issue is still affecting you then, because that exception
> is saying it can't find the table.
>
> Have you investigated this issue much? Sounds like it's either
> something wrong with your connection string or the priviledges for the
> user you're connecting as.
>

[Pete]

Brian and James thanks for your help. I have gotten past the
'SnapData.dbo.aspnet_UsersInRole' error. Now I think the problem is
column names. I am now getting this error..

_Test_EOMapping.UserPermissionTest.DisplayOneRow:
NHibernate.Exceptions.GenericADOException : could not initialize a
collection: [SnapsInTime.EO.UserPermission.allRoles#ab68b9ac-f2e5-4147-
a143-00a405d47e39][SQL: SELECT allroles0_.UserPermission_id as
UserPerm1_1_, allroles0_.UserRoles_id as UserRoles2_1_,
userroles1_.RoleId as RoleId1_0_, userroles1_.RoleName as RoleName1_0_

FROM aspnet_UsersInRoles allroles0_ left outer join aspnet_Users

userroles1_ on allroles0_.UserRoles_id=userroles1_.RoleId WHERE
allroles0_.UserPermission_id=?]

----> System.Data.SqlClient.SqlException : Invalid column name
'UserRoles_id'.
Invalid column name 'RoleId'.
Invalid column name 'UserPermission_id'.
Invalid column name 'UserPermission_id'.
Invalid column name 'UserRoles_id'.
Invalid column name 'RoleId'.
Invalid column name 'RoleName'.

> >> > On Feb 8, 2:24 pm, James Gregory <jagregory....@gmail.com> wrote:
> > >> >> Your UserPermission.allRoles is an List<T>, when it should be an
> > >> >> IList<T>;
> > >> >> that's why you're getting the conversion error.- Hide quoted text -
>

> > >> - Show quoted text -- Hide quoted text -