nPoco V3 - many to many not working

Question

I have users which have many roles

public class User 
{
   public int Id {get;set;}
   public string Name {get;set;}
   public List<Role> Roles {get;set;}
}

public class Roles 
{
   public int Id {get;set;}
   public string Key{get;set;}
}

public class UserRoles 
{
   public int UserId {get;set;}
   public int RoleId {get;set;}
}

what I try to achieve is getting a user with all its roles in one query, but so far I failed. For Mapping I use a custom Conventionbased mapper (I can provide the code, but it's rather big)

I tried FetchOneToMany and I tried Fetch as described here

https://github.com/schotime/NPoco/wiki/One-to-Many-Query-Helpers https://github.com/schotime/NPoco/wiki/Version-3

But Roles is always empty. Role and User by itself are mapped correctly and I did try to specify the relation like

For<User>().Columns(x =>
        {
            x.Many(c => c.Roles);
            x.Column(c => c.Roles).ComplexMapping();
        }, true);  

Again it didn't help, roles is empty.

I have no idea what I'm missing. Any ideas?

Answer 1

ComplexMapping and relationship mapping(1-to-n, n-to-n) are two different things.

ComplexMapping is for mapping nested objects for data that usually resides in the same table where there is a one-to-one relationship. For something like this:

public class Client 
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public Address Address { get; set; }

    public Client()
    {
        Address = new Address();
    }
}

public class Address
{
    public string Street { get; set; }
    public string City { get; set; }
    public string PostalCode { get; set; }
    public string Telephone { get; set; }
    public string Country{ get; set; }
}

If you're using a convention-based mapper your override would look something like this:

 For<Client>().Columns(x =>
 {
     x.Column(y => y.Address).ComplexMapping();
 });

One thing to watch for when using a convention-based mapper; you have to enable ComplexMapping in your scanner with the following code:

scanner.Columns.ComplexPropertiesWhere(y => ColumnInfo.FromMemberInfo(y).ComplexMapping);

Otherwise ComplexMapping() calls in your overrides will simply be ignored.

One-to-Many mapping would work like this (see NPoco on Github for more):

For<One>()
    .TableName("Ones")
    .PrimaryKey(x => x.OneId)
    .Columns(x =>
    {
        x.Column(y => y.OneId);
        x.Column(y => y.Name);
        x.Many(y => y.Items).WithName("OneId").Reference(y => y.OneId);
    }, true);

For<Many>()
    .TableName("Manys")
    .PrimaryKey(x => x.ManyId)
    .Columns(x =>
    {
        x.Column(y => y.ManyId);
        x.Column(y => y.Value);
        x.Column(y => y.Currency);
        x.Column(y => y.OneId);
        x.Column(y => y.One).WithName("OneId").Reference(y => y.OneId, ReferenceType.OneToOne);
    }, true);