NHibernate exception when creating SQL for DELETE with a condition from a JOIN

Question

Tech Stack

SQL Server 2019, NHibernate 5.3.5, .NET Framework 4.8

Setup

Database

CREATE TABLE First (
    Id UNIQUEIDENTIFIER PRIMARY KEY
);
CREATE TABLE Second (
    Id UNIQUEIDENTIFIER PRIMARY KEY,
    FirstId UNIQUEIDENTIFIER FOREIGN KEY REFERENCES First(Id),
    Criteria BIT NOT NULL,
);

Mappings

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Test" namespace="Test">
  <class name="First" table="First" lazy="false">
    <id name="Id" type="Guid" column="Id">
      <generator class="guid"/>
    </id>
  </class>
</hibernate-mapping>

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Test" namespace="Test">
  <class name="Second" table="Second" lazy="false">
    <id name="Id" type="Guid" column="Id">
      <generator class="guid"/>
    </id>
    <property name="FirstId" column="FirstId"/>
    <property name="Criteria" column="Criteria"/>
  </class>
</hibernate-mapping>

Configuration

The Second and First table map one-to-one.

My dialect is NHibernate.Dialect.MsSql2012Dialect (I didn't find anything more recent), my driver is NHibernate.Driver.MicrosoftDataSqlClientDriver (though I also tried with NHibernate.Driver.SqlClientDriver).

Scenario

I want to delete records from the First table, but filter on the corresponding record from the Second table using LINQ. This was my attempt:

CurrentSession.Query<First>()
    .Join(
        CurrentSession.Query<Second>(),
        first => first.Id,
        second => second.FirstId,
        (first, second) => new { First = first, Second = second }
    )
    .Where(joined => joined.Second.Criteria == 1)
    .Select(joined => joined.First)
    .Delete();

I get the following exception:

NHibernate.Exceptions.GenericADOException: could not execute update query[SQL: delete from First inner join Second second_1 on (FirstId=Id) where Criteria = 1] ---> System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'inner'.

Cleaned up a bit, this seems to generate this SQL:

DELETE FROM First INNER JOIN Second s ON (FirstId=Id) WHERE Criteria = 1

This seems to be correct generic SQL, at least based on examples I'm finding online. I'm not an SQL expert, so I'm not sure if this is down to the SQL dialect, but it seems that SQL Server at least expects this format (notice the table aliases, especially after DELETE):

DELETE f FROM First f INNER JOIN Second s ON (s.FirstId=f.Id) WHERE s.Criteria = 1

I did manually run the second query in the server, and it worked.

Is the reason the above is generated down to the configuration? Can I adjust the query or mapping somehow to generate SQL with table aliases? I noticed that it does create an alias for the joined table (second, above), but it doesn't use that alias in the query. Though that alone is still not enough, the first table (designated in the FROM) still needs to be aliased, it seems.

Can this be done without writing the SQL manually, and if so, how?

Answer 1

NHibernate has troubles with DML queries with joins in main query. But moving it to subquery should work (at least with NHibernate 5.3+). Try this:

var itemsToDeleteSubquery = CurrentSession.Query<First>()
    .Join(
        CurrentSession.Query<Second>(),
        first => first.Id,
        second => second.FirstId,
        (first, second) => new { First = first, Second = second }
    )
    .Where(joined => joined.Second.Criteria == 1)
    .Select(joined => joined.First);

    CurrentSession.Query<First>().Where(e => itemsToDeleteSubquery.Contains(e)).Delete();