You doubt with Fluentnhibernate mapping?

Question

I am trying to create a scheme to define permissions on my system and these permissions are given to the user profile, where one can choose what each profile is authorized to do on each module. For this I created some tables and I am in doubt of how to map between them, mainly in the permissions part.

In Java I do the mapping on the entities themselves and in Nhibernate I’m new so I still don’t understand the concept of how to do the mapping. My question is between relating the entity Profile/Module and Permission. In case I created an entity only for Permissao where it makes the relationship of Profile(Hasone) and Module(Hasmany), this is correct ?

Note: When setting the permissions for the profile, I want to display all the modules in a Datagridview with checkbox checkbox options (insert, change, search, print) and so set the permissions of each module in the table permissoes.

What’s the best way to do it ?

Entities

Profile

public class Perfil {

        public virtual int id { set; get; }
        public virtual string descricao { set; get; }

        public Perfil() {
        }
    }

Modules

public class Modulo {

        public virtual int id { set; get; }
        public virtual string nome { set; get; }
        public virtual string descricao { set; get; }

        public Modulo() {
        }

        public Modulo(string nome, string descricao) {
            this.nome = nome;
            this.descricao = descricao;
        }

    }

Permissions

 public class Permissao {

    public virtual int id { set; get; }
    public virtual Perfil perfil { set; get; }
    public virtual IList<Modulo> modulos { set; get; }
    public virtual int inserir { set; get; }
    public virtual int alterar { set; get; }
    public virtual int pesquisar { set; get; }
    public virtual int imprimir { set; get; }

    public Permissao() {
        modulos = new List<Modulo>();
    }

}

Mappings

Profile

public class PerfilMap : ClassMap<Perfil>{

        public PerfilMap() {
            Table("perfis");
            Id(p => p.id).GeneratedBy.Identity();
            Map(p => p.descricao).Unique().Not.Nullable();

        }
    }

Module

public class ModuloMap : ClassMap<Modulo>{

        public ModuloMap() {
            Table("modulos");
            Id(m => m.id).GeneratedBy.Identity();
            Map(m => m.nome).Unique().Not.Nullable();
            Map(m => m.descricao).Not.Nullable();

        }
    }

Permission

public class PermissaoMap : ClassMap<Permissao>{

        public PermissaoMap() {
            Table("permissoes");
            Id(p => p.id).GeneratedBy.Identity();
            Map(p => p.inserir).CustomType<int>();
            Map(p => p.alterar).CustomType<int>();
            Map(p => p.pesquisar).CustomType<int>();
            Map(p => p.imprimir).CustomType<int>();
            HasOne(p => p.perfil);
            HasMany(p => p.modulos).LazyLoad();
        }

    }

model

Answer 1

This answer is not intended to solve the problem, but rather to point out flaws and suggest possible alternatives.

Possible problems/failures

Based on your clarifications in the comments, your model is wrong in relation to your database, because in your model Permissoes you have:

public virtual IList<Modulo> modulos { set; get; }

And in the database in the table Permissoes you have the relationship field with Modelos as a whole(INT):

modelos_id : INT

How you would store a list of table references Modulo in a column of the type INT?

Suggestion

If I were to model your case, I would make the table Permissoes a relationship between the Perfil and the Modulo specifying permissions for this relation:

Permissoes
--------------------------------------------
id : INT | perfil_id: INT | modulo_id : INT | insert: INT (0 = false, 1 = true) | update: INT (0 = false, 1 = true) | search: INT (0 = false, 1 = true) | print: INT (0 = false, 1 = true)

Getting very similar to what it was before.

And on the model I’d switch to:

public class Permissao {

    public virtual int id { set; get; }
    public virtual Perfil perfil { set; get; }
    public virtual Modulo modulo { set; get; }
    public virtual bool inserir { set; get; }
    public virtual bool alterar { set; get; }
    public virtual bool pesquisar { set; get; }
    public virtual bool imprimir { set; get; }

    public Permissao() {
    }

}

And the mapping something like this:

public class PermissaoMap : ClassMap<Permissao>{

    public PermissaoMap() {
        Table("permissoes");
        Id(p => p.id).GeneratedBy.Identity();
        Map(p => p.inserir);
        Map(p => p.alterar);
        Map(p => p.pesquisar);
        Map(p => p.imprimir);
        References(p => p.perfil);
        References(p => p.modulo);
    }

}

Would also create a unique index between the columns perfil_id and modulo_id, making it not allowed to add more than one permission to the Perfil related to Modulo.

To illustrate how to get permissions by Perfil we could have a similar consultation to this:

int idPerfil = 1;

// Em lambda expression
IList<Permissao> permissoesPerfilLambda = Session.QueryOver<Permissao>()
    .Where(t => t.perfil.id == idPerfil)
    .List();

// Em LINQ
IList<Permissao> permissoesPerfilLinq = (from permissao in Session.Query<Permissao>()
                                            where permissao.perfil.id == idPerfil
                                            select permissao).ToList();

// Em SQL nativo
SELECT * FROM permissoes p WHERE p.perfil_id == 1;