WebAPI – DRNJ

NHIbernate, Sybase and .NET Core

June 28, 2021

The Problem

I was working on a system utilising .NET 4.x and Sybase. This combination is not supported by Entity Framework so NHibernate was used.

It was planned to migrate the project to .NET Core, however a number of issues were encountered ustilising NHibernate.

Much investigation was performed, espcially using Resharper to disassemble the Sybase driver

The solution is shown below. Voila, you can use .NET Core and NHibernate and Sybase.

Solution Attempt Number 1

Code

Create WebAPI project on DotNet Core 3.1
Add references to NHibernate 5.2.7

Errors

Discussion

It appears that the NHibernate code cannot find the the Sybase ASE driver Sybase.ADONet2.AseClient installed as part of the nuget package

Solution Attempt Number 2

Code

As per Verrsion 1 but with

NHibernate 5.2.7 Source code downloaded and .csproj added to solution and references instead of NHibernate nuget package (so can step through code)
Reference added to Sysbase DLLs

Errors

Discussion

An exception is thrown in the Sybase.AdoNet2.ASeClient.dll in the constructor which calls LoadLibraries():

NB Disassembly of the driver done by ReSharper

The error is thrown at the line 252 on the Directory.GetAccessControl method – this is not supported in .Net Core:

https://stackoverflow.com/questions/41584932/support-for-file-security-in-net-core

The “workaround” suggested does not appear to work within the context of the Driver (not known why but driver might run in a restricted execution context). This article also discusses the driver error and that some System.IO methods have been removed in dotnetcore.

The Real Solution

Code

Add AdoNetCore.AseClient nuget package – this is a 3rd party Sybase ASE driver
Reference NHibernate nuget version 5.2.7
Add Driver classes as below

These files are “driver” classes customised to use the AdoNetCore.AseClient driver. These are “copies” of the equivalent files in the NHibernate source code with the following modifications

The above specification for connection.driver_class is necessary as “full assembly specification” giving assembly name and class name otherwise NHibernate doesn’t know in which DLL to “looK” for the class.

The CustomDialect class was changed to reference these files:

Discussion

he connection.driver_class specification in SybaseASECoreDialect was the only “tricky” thing to “get right”. In the NHibernate “virgin” SysbaseASE15Dialect only specifies the “Driver” class name for the connection.driver_class

The NHibernate can “find” Nhibernate.Driver.SybaseAseClientDriver as it is contained within the NHibernate DLL. Using the custom classes it is necessary to specify the “full name” as the code is in separate assembly

API Versioning and Swagger

June 28, 2021

The Problem

API Versioning was added to a Microsoft C# Web API project .NET Core.

[ApiVersion("2.1")]
public class MyController: ApiController
{
}

The Swagger page for the API then did not list the API endpoints

The Solution

Simply add the [ApiController] attribute to the class

[ApiVersion("2.1")]
[ApiController]
public class MyController: ApiController
{
}

NHibernate, Sybase, CHAR(10) Columns and Poor Performance

April 17, 2020

The Problem

I was working on a C# ODATA WebAPI development that utilised NHibernate (version 4.1.1.4), Sybase, a Sybase ASE driver, .Net 4.7 etc. The tables being queried were from a legacy system and contain many char(1), char(10) and char(20) colums rather than varchar columns.

The performance of the API was not stunning.

Using an ODATA $filter against the API endpoints also did not achieve stunning performance – the response was the order of 10-20 Seconds. But why?

Investigation

NHibernate query logging was enabled so it was possible to see the queries being fired at the database – all looked fine. The queries were extracted from the log files and run directly against the database using TOAD – the queries ran quickly (a few hundred mS). So why was the API so slow?

The API code itself was quite simple – they controller method returned an IQueryable object and the magic of the Microsoft WebAPI infrastructure applied the $filter “WHERE CLAUSE” to the IQueryable and the results were returned as JSON to the caller.

However, many of the columns used in the WHERE clause were char(10) and char(20) columns rather than VARCHAR. In the NHIbernate XML mappings these fields were mapped to strings

 <property column="ExampleCharDBCol" type="String" name="ExampleModelPropertu" not-null="true" length="10"/>

From investiation it was surmised (NB surmised, I have no hard facts) that tomewhere in the NHibernate database communication the WHERE parameters were being transferred as some unicode encoded value which when, on the DB server, the query was “unpacked” and run the various indexes were ignored. Hence the slow querying

The Solution

I must give thans to B Boy and StackOverflow and Todd for pointing me in the right direction.

The answer is to use type=”AnsiString” in the NHibernate XML mappings:

 <property column="ExampleCharDBCol" type="AnsiString" name="ExampleModelPropertu" not-null="true" length="10"/>

Doing this made the queries run a factor 100 faster !!

Epilogue

I don’t think this solution is pertinent to only Sybase, I think it is valid for any database that NHibernate talks to.

NHibernate Connection Cycling

July 26, 2018

The Problem

On a webAPI (C#/.Net 4) using NHibernate 4 an issue was observed under stress testing. The issue was associated with the user-impersonation being done on the database connection – the impersonation would work correctly, however, for subsequent database operations would fail. After much (and I do mean much) investigation it was found that the connections seem to be “cycled” amongst the NHibernate sessions – so we could observe the session using a connection with one particular HashCode (unique object identifier) and see the user-impersonation SQL execute, but when it came to some ORM write-backs to the DB another connection with another user’s impersonation (and differing HashCode) was observed in the session for the db operation.

Weird or what ?

The Solution

I must give a round of applause to the guys at StackOverflow for this one.

Basically, create a dummy session from the session factory , then pass the connection of this session to the OpenSession method of the session factory to create the session you’re going to use.

Simples

ISession dummy = sessionFactory.Factory.OpenSession();
this.Session = sessionFactory.Factory.OpenSession(dummy.Connection);

DRNJ

Category: WebAPI

The Problem

Solution Attempt Number 1

Code

Errors

Discussion

Solution Attempt Number 2

Code

Errors

Discussion

The Real Solution

Code

Discussion

The Problem

The Solution

The Problem

Investigation

The Solution

Epilogue

The Problem

The Solution