CODEWITHSUNDEEP
nhibernatestored-proceduresfluent-nhibernate
Anthony Shaw
Anthony Shaw

Reputation: 8166

Populate Entities from Stored Procedure

I am using nHibernate and am trying to populate an entity, which has a subclass of another entity from a stored procedure. I have a RankedListing which contains an Id, a Rank and a Listing

public class RankedListing : DomainEntity
{
    public virtual int Id { get; set; }
    public virtual int Rank { get; set; }
    public virtual Listing Listing { get; set; }
}

I am calling a stored procedure to populate this entity from a FULLTEXT search. The stored procedure returns three columns (Id, Rank, and Listing_Id). The code that I'm executing is below.

IQuery query = ServiceLocator.Current.GetInstance<INHibernateUnitOfWork>().CurrentSession.CreateSQLQuery("exec dbo.usp_SearchListings :Search");
query.SetString("Search", search);

var products = query.SetResultTransformer(Transformers.AliasToBean(typeof(RankedListing))).List<RankedListing>().ToList();
return products;

When I execute this, the error that I get back is: 

Could not find a setter for property 'Listing_Id' in class 'RankedListing'

Is there anyway to get this to map the Listing on the RankedListing rather than trying to map it as the column name as a property.

Upvotes: 0

Views: 1196

Answers (1)

Firo
Firo

Reputation: 30813

i dont know if it's the best way but a custom resulttransformer could do

class MyTransformer : IResultTransformer
{
    private ISession _session;
    private Dictionary<int, int> _listingIds = new Dictionary<int, int>();

    public MyTransformer(ISession session)
    {
        _session = session;
    }
    public IList TransformList(IList collection)
    {
        var listings = _session.QueryOver<Listing>()
            .WhereRestrictionOn(l => l.Id).IsIn(_listingIds.Values)
            .List().ToDictionary(l => l.Id);
        return collection.Cast<RankedListing>()
            .Select(rl => { rl.Listing = listings[_listingIds[rl.Id]]; return rl; })
            .ToList();
    }

    public object TransformTuple(object[] tuple, string[] aliases)
    {
        var rl = new RankedListing();
        rl.Id = (int)tuple[System.Array.FindIndex(aliases, name => name == "Id")];
        rl.Rank = (int)tuple[System.Array.FindIndex(aliases, name => name == "Rank")];

        _listingIds.Add(rl.Id, (int)tuple[System.Array.FindIndex(aliases, name => name == "Listing_Id")]);

        return rl;
    }
}

then use it like:

var products = query.SetResultTransformer(new MyTransformer(CurrentSession)).List<RankedListing>();

Update: if you want to lazyload the Listings you could also do

.Select(rl => { rl.Listing = session.Load<Listing>(_listingIds[rl.Id]); return rl; })

Upvotes: 2

Related Questions