Nhibernate: invoke stored procedure with CreateSqlQuery using already defined mapping

Question

I Invoke a stored procedure using ISession.CreateSQLQuery.

Then I use

SetResultTransformer(new AliasToBeanResultTransformer(typeof(Article))).List<Article>().ToList()

The problem with this approach is that the AliasToBeanResultTransformer only maps the Article table to the Article class one to one.

public class Article : Entity
{
    public virtual string Description { get; set; }
}

public class Entity
{
    public virtual int Id { get; set; }
}

public class ArticleRepository : Repository<Article>, IArticleRepository
{
    private ISession _session;

    public ArticleRepository(ISession session) : base(session)
    {
        _session = session;
    }

    public List<Article> GetByDescription(string description)
    {
        return _session
            .CreateSQLQuery("EXEC ArticlesByDescription :Description")
            .SetString("Description", description)
            .SetResultTransformer(new AliasToBeanResultTransformer(typeof(Article)))
            .List<Article>().ToList();
    }
}

But my primary key on my Article table is called ArticleId, so that the AliasToBeanResultTransformer throws an exception.

Could not find a setter for property 'ArticleId' in class 'Core.DomainModels.Article'

Is there a way of reusing the FluentNhibernateMapping when using CreateSqlQuery?

EDIT:

The Nhibernate Documentation describes how you can use an already mapped entity with hbm files.

<sql-query name="GetProductsByCategoryId">
   <return class="Product" />
   exec dbo.GetProductsByCategoryId :CategoryId
</sql-query>

I really ask myself why is it not possible to do this just by code?!

Of course, but this is really cumbersome. When I would use hbm files would then Nhibernate use the FluentNhMappings when I invoke a sp? — Rookian, Apr 11 '12 at 13:05
The problem is you inherit from `public class Entity` that expects Id in your resultset. The only other way is to create a ArticleDto — Rippo, Apr 11 '12 at 13:10
`AliasToBeanResultTransformer` is not clever enough to transform column names based on your mappings. — Rippo, Apr 11 '12 at 13:12
@Rippo yes. Is there another way of getting a typed result from a store procedure that uses the FNH mappings? — Rookian, Apr 11 '12 at 13:19
Nothing that I am aware of, btw its not a FNH problem rather a NH issue. To be fair a `SP` should populate a DTO as it is never really mutable. — Rippo, Apr 11 '12 at 13:22

score 4 · Answer 1 · answered Sep 09 '13 at 18:04

I am using this code for mapping to entity in repository. I believe it would work for calling procedure also:

public IEnumerable<Address> Search(string query, int maxCount)
{
    return session.CreateSQLQuery("SELECT * FROM address WHERE fts_col @@ plainto_tsquery('cs', :query) LIMIT :limit;")
        .AddEntity(typeof(Address)) // this is probably what you need to map to entity
        .SetString("query", query)
        .SetInt32("limit", maxCount)
        .List<Address>();
}

score 3 · Accepted Answer · answered Apr 13 '12 at 13:04

It seems not possible to use ISession.CreateSQLQuery and get a mapped entity back from the ISession.

What I do for now is using hbm files and FluentNhibernate mappings together.

Hbm:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Core" namespace="Core.DomainModels">
  <sql-query name="ArticlesByDescription">
    <return class="Article" />
    EXEC ArticlesByDescription :Description
  </sql-query>
</hibernate-mapping>

FluentNhibernate:

public class ArticleMapping : ClassMap<Article>
{
    public ArticleMapping()
    {
        Id(x => x.ArticleId).GeneratedBy.Identity();
        Map(x => x.Description).UniqueKey("Article_Description_Unique");
    }
}

Configuration:

public class ConfigurationFactory
{
    const string Database = "";
    const string Server = "";

    public static Configuration Build()
    {           
        return Fluently.Configure()
            .Database(MsSqlConfiguration.MsSql2008.ConnectionString(
                c => c.Database(Database).TrustedConnection().Server(Server)))
            .Mappings(m =>
            {
                m.FluentMappings.AddFromAssemblyOf<ArticleMapping>();
                m.HbmMappings.AddFromAssemblyOf<ArticleMapping>();
            })
            //.ExposeConfiguration(c => new SchemaExport(c).Execute(true, true, false))
            .BuildConfiguration();
    }
}

Repository:

public class ArticleRepository : Repository<Article>, IArticleRepository
{
    private ISession _session;

    public ArticleRepository(ISession session) : base(session)
    {
        _session = session;
    }

    public List<Article> GetByDescription(string description)
    {
        return _session
            .GetNamedQuery("ArticlesByDescription")
            .SetString("Description", description)
            .List<Article>().ToList();
    }
}

Nhibernate: invoke stored procedure with CreateSqlQuery using already defined mapping

2 Answers2