1

I have a NHibernate entity with the following field:

public virtual decimal ApprovedQuantity { get; set; }

and the ClassMap for this entity contains the following:

Map (x => x.ApprovedQuantity).Column("approved_qty").CustomType<decimal>().Precision(7).Scale(2);

The SQL Server table is defined as follows:

CREATE TABLE stock (
    project_code    NCHAR(30)      NOT NULL,
    item_code       NCHAR(25)      NOT NULL,
    classification  NCHAR(100)     NOT NULL,
    name            NCHAR(30)      NOT NULL,
    depot_reference NCHAR(12)      NOT NULL,
    total_qty       DECIMAL(7, 2) NOT NULL,
    approved_qty    DECIMAL(7, 2) NOT NULL,
    unapproved_qty  DECIMAL(7, 2) NOT NULL,
    last_modified   SMALLDATETIME,
    PRIMARY KEY (project_code, item_code, classification, name, depot_reference)

When I build a NHibernate criteria to run a query against this table, to get all stock items with an approved quantity > 200 (grouped by various other fields), the generated SQL as logged by NHibernate looks like the following:

NHibernate: SELECT this_.item_code as y0_, this_.name as y1_, this_.classification as y2_, sum(this_.total_qty) as y3_, sum(this_.approved_qty) as y4_, sum(this_.unapproved_qty) as y5_ FROM stock this_ WHERE this_.project_code = @p0 and ((this_.depot_reference = @p1 or this_.depot_reference = @p2 or this_.depot_reference = @p3)) GROUP BY this_.item_code, this_.name, this_.classification HAVING sum(this_.approved_qty) > @p4 ORDER BY y0_ asc;@p0 = 'MyProject' [Type: AnsiStringFixedLength (8000)], @p1 = '10R' [Type: AnsiStringFixedLength (8000)], @p2 = '16Z' [Type: AnsiStringFixedLength (8000)], @p3 = '17T' [Type: AnsiStringFixedLength (8000)], @p4 = 200 [Type: Currency (8)]

Note that at the very end of this log entry, the @p4 parameter is declared as type 'currency' by NHibernate, despite me explicitly mapping the field in the ClassMap to the 'decimal' type.

I wouldn't have thought that this in itself would cause too much of a problem, but on the SQL Server end, it ends up compiling the @p4 parameter into a value of '0.02'. I've obtained this by gaining the XML of the query plan at the SQL Server end:

<ColumnReference Column="@p4" ParameterCompiledValue="($0.0200)"

So SQL Server basically ends up treating my value of '200' as '0.02', and of course the query returns unexpected results.

If I can get NHibernate to declare @p4 as a decimal type, and not as a currency type, I'm hoping this problem will be resolved. Why does it appear to be ignoring my attempt at explicitly mapping this field to the 'decimal' SQL Server type in the ClassMap? Even without the explicit mapping to CustomType, it still uses the 'money' SQL Server type.

Thanks.

Jimidy
  • 2,741
  • 2
  • 14
  • 8
  • 1
    From your own answer below it seems you've found a working solution for you. However, this behaviour feels weird - it would be interesting if you could do two things: 1) get the generated XML mapping from FluentNHibernate so we can see what happens between the layers, and 2) ask NHibernate to generate the database schema (just to a string) to see which type it wants to use for the column (to see if the issue affects just the type of the parameter). Both without any CustomType<> or CustomSqlType(). – Oskar Berggren Mar 05 '16 at 18:03
  • I think this is a useful idea and I will see if I can have a go at it. – Jimidy Mar 08 '16 at 17:06

3 Answers3

0

Try changing

CustomType

to

CustomSqlType

granadaCoder
  • 26,328
  • 10
  • 113
  • 146
  • 1
    Also : look at the answer from aeliusd here: http://stackoverflow.com/questions/2033630/datetime-precision-in-nhibernate-and-support-for-datetime2-in-nhibernate-schemee – granadaCoder Mar 02 '16 at 16:05
  • CustomSqlType("decimal") does not work either. It looks like I'll have to override the behaviour using a Convention or similar, as suggested in that question you linked to. Thanks. – Jimidy Mar 02 '16 at 17:13
  • Did you try "inline" CustomType AND CustomSqlType (at the same time) ? Yeah, if you have to resort to custom-convention..that stinks. – granadaCoder Mar 02 '16 at 18:15
  • @Jimidy You sure you don't already have a convention somewhere that sets it to currency? – Oskar Berggren Mar 02 '16 at 21:51
  • @OskarBerggren No, it's only a small app and I haven't written any conventions up until now. I have read that the Decimal type in .NET was designed for financial calculations due to its high precision, so perhaps that's why NHibernate (or something underneath it, not sure) is choosing the SQL Server 'money' type. – Jimidy Mar 03 '16 at 08:38
0

Remove the call to CustomType<T>(). NHibernate should be able to resolve that from the type of the mapped property.

Oskar Berggren
  • 5,583
  • 1
  • 19
  • 36
  • Unfortunately it still tries to use the 'money' SQL Server type. That's why I've tried explicitly mapping it to CustomType and CustomType, to no avail in either case. – Jimidy Mar 02 '16 at 16:45
0

I've changed the entity's property type to 'Double' instead of 'Decimal':

public virtual Double ApprovedQuantity { get; set; }

and not made any CustomType/CustomSqlType() calls:

Map (x => x.ApprovedQuantity).Column("approved_qty");

and now the parameter used for the query is of the correct type according to the NHibernate logging:

NHibernate: SELECT this_.item_code as y0_, this_.name as y1_,....@p4 = 600 [Type: Double (8)]

and the query functions as expected.

We are not holding extremely precise quantities in this table (1 decimal place at best) so I don't expect to lose any precision even if the table column is of type DECIMAL (which I can't control, it's not my schema).

Thanks all.

Jimidy
  • 2,741
  • 2
  • 14
  • 8