Description
Hi, I'm working with a legacy database that doesn't have foreign-keys and sometimes has weird foreign-key values like -1 that don't actually exist in the foreign table.
So we use not-found=ignore many times in our mappings, which worked great so far.
Now I updated NHibernate from 5.1.x to the current 5.3.8, and some LINQ queries that used to work before, now return null.
Queries in the form of:
var pet = session.Query<Person>()
.Where(f => f.Id == SomeId)
.Select(f => new
{
Name = f.Name,
PetId = (int?)f.Pet.Id,
PetName = f.Pet.Name
})
.FirstOrDefault();pet will be null, even tho there exists a Person with Id SomeId in the database.
In version 5.1.x I would get a object with PetId -1, and PetName = null (as expected).
I managed to create a test case, and pinpoint the NHibernate version that introduced this change in behavior.
The test works correctly in version 5.2.7, and starts failing in 5.3.0.
To be fair, this case is quite atypical, but would be nice if it could get fixed (as it seems like a bug to me).
Here is the code for the test case:
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using FluentNHibernate.Mapping;
using NHibernate.Tool.hbm2ddl;
using System.Diagnostics;
using System.Linq;
namespace NHibernateQueryTest
{
class Program
{
static void Main(string[] args)
{
var factory = Fluently.Configure()
.Database(MsSqlConfiguration.MsSql2012
.ConnectionString(f => f.Server("CS-UL-HAEFELED\\SQL2019").Database("Pets").Username("centron").Password("1"))
.DefaultSchema("dbo")
.ShowSql().FormatSql())
.Mappings(f => f.FluentMappings.AddFromAssemblyOf<PersonMaps>())
.ExposeConfiguration(f => new SchemaExport(f).Execute(true, true, false))
.BuildSessionFactory();
//Arrange
using (var session = factory.OpenSession())
using (var transaction = session.BeginTransaction())
{
// We have a very old legacy database without foreign keys
// NHibernate turned out to be a great ORM to use in this case, because of its amazing flexibility
session.CreateSQLQuery(@"DECLARE @constraintName AS nvarchar(200) = (SELECT TOP 1 name FROM sys.foreign_keys);
DECLARE @sql AS nvarchar(max) = 'ALTER TABLE.dbo.Person DROP CONSTRAINT ' + @constraintName;
EXECUTE sp_executesql @sql").ExecuteUpdate();
var dave = new Person
{
Name = "Dave",
};
session.Save(dave);
// The problem only seems to occur when we have a NOT-NULL value in the reference-column, and .NotFound.Ignore() in the mapping
session.CreateSQLQuery("UPDATE dbo.Person SET PetId = -1").ExecuteUpdate();
transaction.Commit();
}
//Act
using (var session = factory.OpenSession())
{
var dave = session.Query<Person>().First();
var pet = session.Query<Person>()
.Where(f => f.Id == dave.Id)
.Select(f => new
{
Name = f.Name,
PetId = (int?)f.Pet.Id,
PetName = f.Pet.Name
})
.FirstOrDefault();
//Assert
Debug.Assert(pet != null);
}
}
}
public class Person
{
public virtual int Id { get; set; }
public virtual string Name { get; set; }
public virtual Pet Pet { get; set; }
}
public class PersonMaps : ClassMap<Person>
{
public PersonMaps()
{
this.Id(f => f.Id);
this.Map(f => f.Name).Not.Nullable().Length(255);
this.References(f => f.Pet).Column("PetId").Nullable().NotFound.Ignore(); //If I don't have a .NotFound.Ignore() here, I will not get NULL, but petId -1, and name = null
}
}
public class Pet
{
public virtual int Id { get; set; }
public virtual string Name { get; set; }
}
public class PetMaps : ClassMap<Pet>
{
public PetMaps()
{
this.Id(f => f.Id);
this.Map(f => f.Name).Not.Nullable().Length(255);
}
}
}I don't really know the NHibernate code-base good enough to fix this issue, but I would give it a try with some guidance from you guys.