How to execute and return value form a stored procedure inside FluentNHibernate Mapping?

Question

Well, I have a ClassMap like this for my mapping in FluentNHibernate

public class AbNopCommereceMap : ClassMap<AbNopCommerece>
{
    public AbNopCommereceMap()
    {
        Table("AbNopCommerece");
        Id(d => d.Id).CustomSqlType("BIGINT").GeneratedBy.Native();

        Map(d => d.Name).CustomSqlType("NVARCHAR(100)").Unique().Not.Nullable();    

        Map(u => u.PublisherName);
        Map(u => u.Author);
        Map(u => u.Country);
        Map(u => u.LastModifiedByFullName).Formula("(SELECT b.FirstName +' '+ b.LastName FROM Users b WHERE b.Id=CreatedBy)");

        Map(d => d.IsActive);
        Map(d => d.IsDeleted);
        Map(d => d.IsLocked);
        Map(d => d.CreatedBy).CustomType<TypedAggregateReferenceType<User>>();
        Map(d => d.CreatedByDate);
        Map(d => d.LastModifiedBy).CustomType<TypedAggregateReferenceType<User>>();
        Map(d => d.LastModifiedByDate);
        Map(d => d.Organization).Column("OrganizationId").CustomType<TypedAggregateReferenceType<Organization>>();
        Cache.Region("General").NonStrictReadWrite();
        ApplyFilter<OrganizationFilter>();
    }
}

Here LastModifiedByFullName is my User which I am selecting from the database, I want to execute a stored procedure here which I have already in my SQL server like

CREATE PROCEDURE pop_use(@id int) 
as
DECLARE @FullName NVARCHAR(MAX)
SET @FullName = ''
SELECT @FullName = FirstName + ' '+ LastName FROM Users WHERE Id = @UserId

I have gone through this question1, question2 but they have used made a new function or XML to retrieve the value from data base. So, my question is, as I NHibernate has already provide some feature like Format by which I am getting the value from database directly, therefore is there any way in ClassMap to execute the stored Procedure and let not me do some repetitive code again inside ClassMap. In a word I am seeking for any way to collect the data from database through previously defined particular store procedure and add it into model.

Did you see this answer (http://stackoverflow.com/a/278807/1162077). Suggests it might be possible using `Formula()`. — David Osborne, Mar 01 '17 at 08:50

score 2 · Answer 1 · edited May 23 '17 at 12:25

2

As stated here, you can't use a stored procedure in a Select, Where or Having statement, you need to use a function that returns a value.

CREATE FUNCTION pop_use(@UserId int) 
as
DECLARE @FullName NVARCHAR(MAX)
SET @FullName = ''
SELECT @FullName = FirstName + ' '+ LastName FROM Users WHERE Id = @UserId
Return(@FullName)

Then you can use your function

Map(u => u.LastModifiedByFullName).Formula("mySchema.pop_use(myIdColumnName)");

myIdColumnName is the name of your identifier. Please note that you must specify your schema in the function call.

You can take a look to Ayende, he gives you a example how to use a function in a formula.

Kudos to Frédéric for his valuable input.

edited May 23 '17 at 12:25

Community

1
1

answered Mar 01 '17 at 08:59

Rabban

2,451
1
19
21

@ShoumenAgdm Please show us what you tried. It's definitely working. We uses this in multiple mappings. – Rabban Mar 01 '17 at 11:22
Look on my [StoredProcedure](http://imgur.com/a/99o4h) My [C# COde](http://imgur.com/a/MeS49) Let's enjoy the [error](http://imgur.com/a/jrDQw) – Shoumen Agdm Mar 01 '17 at 11:37
@ShoumenAgdm i compared your code with mine and the only difference i could find was that you use a `Procedure` and we use `Function`. Can you change it to a function? The difference between the two is stated [here](http://stackoverflow.com/a/1179778/6666799) and it looks that you can easily use a function instead. – Rabban Mar 01 '17 at 11:51
Look I have made a function now [link](http://imgur.com/a/1DrIq), invoked it, but still not working. – Shoumen Agdm Mar 01 '17 at 12:20
@ShoumenAgdm 1. Did you tried your function by hand? 2. And did you tried to specifiy the schema on calling it like i suggested? 3. Do you get any exceptions while executing a query to the Database? 4. Do you have the possibility to look at the generated sql from NHibernate? If not, i would suggest to use [NhProfiler](https://hibernatingrhinos.com/products/nhprof), its free for 30 days, so give it a try. – Rabban Mar 01 '17 at 12:28
1. Yes, I did tried by hand. 2. Yes. 3. No, I didn't got exception. 4. Exception is coming before the execution in the mapping. Anyway I am trying the link you provided. Good day. – Shoumen Agdm Mar 01 '17 at 12:37
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/137015/discussion-between-shoumen-agdm-and-rabban). – Shoumen Agdm Mar 02 '17 at 05:10

How to execute and return value form a stored procedure inside FluentNHibernate Mapping?

1 Answers1