NHibernate Mapping for string Foreign Key with specific type

Question

I'm trying to map a string foreign key in NHibernate. This works but the field is a varchar(10). This is causing performance problems and the script is timing out because NH is using a nvarchar(4000). When we change the variable type in SSMS to a varchar(10) the query takes under a second.

I have the following setup in NHibernate:

public class Product
{
  ProductCode:string;
}
public class ProductMap : ClassMap<Product>
{
  public ProductMap()
  {
    Id(p=>p.ProductCode);
  }
}

public class Holding
{
  public long HoldingID {get;set;
  public Product Prod {get;set;}
  public decimal SomeValue {get;set;}
}
public class HoldingMap
{
  public HoldingMap()
  {
    Id(h=>h.HoldingID);
    References(h=>h.Product, "ProductCode");
    Map(h=>h.SomeValue);
  }
}

I've tried changing the Id(p=>p.ProductCode); so that it is better typed:

Id(c => c.Code, "portfolio_code").CustomSqlType("varchar(10)");

but that doesn't seem to change anything. Does anyone know how to have NH generate the query with a properly typed parameter?

Thanks

See this answer (stackoverflow.com/a/48642861/1162077). I think it might be what you're looking for, or at least provide some insight into what's going on. — David Osborne
That's abolutely brilliant, thank you so much. It's resolved my problem. — Stu

Stu Stu · Accepted Answer · 2018-02-07T13:33:06

@David Osbourne was correct, this did answer my underlying question: Always encrypted mapping in NHibernate but I'll summarise here.

NHibernate will always use the maximum field size for a parameter to allow for wildcards so it's a bit risky to force field length. If you do need to you can see the answer in the driver in the linked answer.

My problem was actually caused by NH using a NVARCHAR on a VARCHAR field. By changing the column to being and AnsiString I dropped the time taken from 40 seconds to sub-second.

NHibernate Mapping for string Foreign Key with specific type

1 Answers