nHibernate Mapping with Stored procedure

[jt4000]

Goal: *Use Stored procedure to retrieve the data and then paste it in the class personprofile and order. *Use Fluent Nhibernate in order to retrieve the mapping data that is one to many mapping. *Get or retrieve the data only and not edit, delete or add data.

Problem: It doesn't work. What part am I missing?

Info: Using Asp.net core, SQL server, C#

Thank you!

`SQL

CREATE TABLE [dbo].[PersonProfil]
(
    [PersonProfilId] [int] IDENTITY(1,1) NOT NULL,
    [Firstname] [nvarchar](50) NOT NULL,
    [Lastname] [nvarchar](50) NOT NULL
)


CREATE TABLE [dbo].[Order]
(
    [OrderId] [int] IDENTITY(1,1) NOT NULL,
    [PersonProfilId] [int] NOT NULL,
    [ProductName] [nvarchar](50) NOT NULL,
)


INSERT INTO [dbo].[PersonProfil]([Firstname],[Lastname])
     VALUES ('a' ,'a')
GO

INSERT INTO [dbo].[PersonProfil]([Firstname],[Lastname])
     VALUES ('a' ,'a')
GO

INSERT INTO [dbo].[Order]([PersonProfilId],[ProductName])
     VALUES (1, 'aaa')
GO

INSERT INTO [dbo].[Order]([PersonProfilId],[ProductName])
     VALUES (1, 'bbb')
GO

INSERT INTO [dbo].[Order]([PersonProfilId],[ProductName])
     VALUES (2, 'ccc')
GO


CREATE PROCEDURE GetPersonProfil
AS  
BEGIN  

    SELECT
        a.[PersonProfilId],
        a.[Firstname],
        a.[Lastname]
    FROM 
        [dbo].[PersonProfil] a
        LEFT JOIN [dbo].[Order] b ON a.[PersonProfilId] = b.[PersonProfilId]
    WHERE
        a.[PersonProfilId] = 1

END  
GO`



C sharp

`public class PersonProfilFactory
{
    public static PersonProfil GetPersonProfil()
    {
        using (NHibernate.ISession session = FluentNHibernateHelper.OpenSession())
        {
            return session.CreateSQLQuery("EXEC GetPersonProfil")
                                    .AddEntity(typeof(PersonProfil))
                                    .List<PersonProfil>().Single();
        }
    }
}




public static class FluentNHibernateHelper
{
    public static NHibernate.ISession OpenSession()
    {
        string connectionString = "";

        ISessionFactory sessionFactory = Fluently.Configure()
            .Database(MsSqlConfiguration.MsSql7.ConnectionString(connectionString).ShowSql())
            .Mappings(m => m.FluentMappings.AddFromAssemblyOf<PersonProfil>()
                                           )
            .ExposeConfiguration(cfg => new SchemaExport(cfg).Create(false, false))
            .BuildSessionFactory();

        return sessionFactory.OpenSession();
    }
}




public class PersonProfil
{
    public virtual int PersonProfilId { get; set; }
    public virtual string Firstname { get; set; }
    public virtual string Lastname { get; set; }
    public virtual List<Order> Order { get; set; }
}




public class Order
{
    public virtual int OrderId { get; set; }
    public virtual int PersonProfilId { get; set; }
    public virtual string ProductName { get; set; }
}




public class PersonProfilMapping : ClassMap<PersonProfil>
{

    public PersonProfilMapping() : base()
    {
        Id(x => x.PersonProfilId);
        Map(x => x.Firstname);
        Map(x => x.Lastname);

        HasMany<Order>(x => x.Order).KeyColumn("PersonProfilId").Cascade.AllDeleteOrphan();
    }
}`

[satishviswanathan]

I was also looking for a way we can integrate stored procedures. Any help is appreciated !