LINQ query returns NULL instead of expected result

[haefele]

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.

[haefele]

Thanks a lot for taking care of it! Looking at the PR now, I probably would not have been able to fix it myself. It seems to be quite deep in the guts of NHibernate.

A bit of a unrelated question: Is there a way to support you, the maintainers of this project? Something like GitHub sponsors, patreon, or something else?

[bahusoid]

Thanks a lot for taking care of it!

np. me break me fix :)

Is there a way to support you, the maintainers of this project?

Nope for me. GitHub doesn't work for my country. And didn't really investigate other options.. Seems need to do. So check my profile next time you feel to donate - if I find some convenient option I will add it there.

[haefele]

Oh, that's very unfortunate. I will definitely keep an eye on your profile for the future!

I'm actually in talks with my employer to sponsor some of the OSS projects we are using. NHibernate as our ORM of choice is my number 1 OSS project that I would like us to support - in some way.

Donations and money would probably be very easy for my employer to do, but I could also imagine a package of german sweets or beer. Of course, I understand that contributing with PRs, code-review or documentation would be most useful to you, but I don't know how easily these things would be possible for us (especially without much knowledge of NHibernate internals).

Anyways, would love to show our appreciation somehow.

And a big thank you for keeping NHibernate alive and well 😄

[fredericDelaporte]

NHibernate is no big organization needing funding. It is mainly volunteer work needing volunteers to contribute.