Is it posibble to add postgresql array operation translation for NHibernate Linq Query?

[beginor]

I have already add a custom NpgSqlDialect and add support of postgresql array types, works for column binds and parameter bindings.

I want to ask is it posibble to add postgresql array operation translation for NHibernate Linq Query?

Just like these Array Type Mapping for Entity Framework Core

@fredericDelaporte , do you any ideas?

[hazzik]

Yes. Just add relevant method generators and provide custom linq registry

[beginor]

Thanks for your advice!

With your points, I found a greate article https://www.primordialcode.com/blog/post/nhibernate-customize-linq-provider-user-defined-sql-functions .

I'll try somethind like that later.

[beginor]

I got it!.

• Add array_contains and array_intersects function to hql.
• Add ArrayContains and ArrayIntersects extension to linq.

Here are some demo codes.

1. Use array_contains in hql:

var num = 1;
var query1 = session.CreateQuery(
    $"from ArrTestEntity e where array_contains(e.IntArr, :{nameof(num)})"
);
query1.SetParameter(nameof(num), num, NHibernateUtil.Int32);
var data1 = query1.List<ArrTestEntity>();
Assert.That(data1, Is.Not.Empty);

var str = "a";
var query2 = session.CreateQuery(
    $"from ArrTestEntity e where array_contains(e.StrArr, :{nameof(str)})"
);
query2.SetParameter(nameof(str), str, NHibernateUtil.String);
var data2 = query2.List<ArrTestEntity>();
Assert.That(data2, Is.Not.Empty);

var query3 = session.CreateQuery(
    $"from ArrTestEntity e where array_contains(e.StrArr, :{nameof(str)})"
    + $" and array_contains(e.IntArr, :{nameof(num)})"
);
query3.SetParameter(nameof(str), str, NHibernateUtil.String);
query3.SetParameter(nameof(num), num, NHibernateUtil.Int32);
var data3 = query3.List<ArrTestEntity>();
Assert.That(data3, Is.Not.Empty);

2. Use Array.Contains in linq query:

var num = 1;
var query1 = session.Query<ArrTestEntity>().Where(
    x => x.IntArr.ArrayContains(num)
);
var data1 = query1.ToList();
Assert.That(data1, Is.Not.Empty);

var str = "a";
var query2 = session.Query<ArrTestEntity>().Where(
    x => x.StrArr.ArrayContains(str)
);
var data2 = query2.ToList();
Assert.That(data2, Is.Not.Empty);

var query3 = session.Query<ArrTestEntity>().Where(
    x => x.IntArr.ArrayContains(num) && x.StrArr.ArrayContains(str)
);
var data3 = query3.ToList();
Assert.That(data3, Is.Not.Empty);

3. Use array_intersects in hql:

string[] strArr = ["a", "c"];
var query1 = session.CreateQuery(
    $"from ArrTestEntity e where array_intersects(e.StrArr, :{nameof(strArr)})"
);
query1.SetParameter(nameof(strArr), strArr, new CustomType(typeof(StringArrayType), null));
var data1 = query1.List<ArrTestEntity>();
Assert.That(data1, Is.Not.Empty);

int[] intArr = [1, 3];
var query2 = session.CreateQuery(
    $"from ArrTestEntity e where array_intersects(e.IntArr, :{nameof(intArr)})"
);
query2.SetParameter(nameof(intArr), intArr, new CustomType(typeof(Int32ArrayType), null));
var data2 = query1.List<ArrTestEntity>();
Assert.That(data2, Is.Not.Empty);

var query3 = session.CreateQuery(
    $"from ArrTestEntity e where array_intersects(e.StrArr, :{nameof(strArr)})"
    + $" and array_intersects(e.IntArr, :{nameof(intArr)})"
);
query3.SetParameter(nameof(strArr), strArr, new CustomType(typeof(StringArrayType), null));
query3.SetParameter(nameof(intArr), intArr, new CustomType(typeof(Int32ArrayType), null));
var data3 = query3.List<ArrTestEntity>();
Assert.That(data3, Is.Not.Empty);

4. Use ArrayIntersects in linq:

string[] strArr = ["a", "c"];
var query1 = session.Query<ArrTestEntity>().Where(
    x => x.StrArr.ArrayIntersects(strArr)
);
var data1 = await query1.ToListAsync();
Assert.That(data1, Is.Not.Empty);

int[] intArr = [1, 3];
var query2 = session.Query<ArrTestEntity>().Where(
    x => x.IntArr.ArrayIntersects(intArr)
);
var data2 = await query2.ToListAsync();
Assert.That(data2, Is.Not.Empty);

var query3 = session.Query<ArrTestEntity>().Where(
    x => x.StrArr.ArrayIntersects(strArr) && x.IntArr.ArrayIntersects(intArr)
);
var data3 = await query3.ToListAsync();
Assert.That(data3, Is.Not.Empty);

Please refer ArrTest for more details.

[beginor]

Since NHibernate.Linq.DefaultQueryProvider does not konw how to set custom array parameters, I have to create NHibernate.Extensions.NpgSql.QueryProvider to set custom array parameters.

I think I should override SetParameters of DefaultQueryProvider , but it's not virtual, so I have to override PrepareQuery , which has call SetParameters inside it, and call SetParameters again in my QueryProvider to set custom array parameters .

I think it's better to mark SetParameters of DefaultQueryProvider as virtual, so custom query providers can just override it to set custom parameters.

[hazzik]

I think you don’t need to override PrepareQuery nor SetParameter. It feels that this logic should be handled by ArrayType / and or generator. Note, that I have not reviewed the code nor ran it yet.

[beginor]

Set ArrayType as query (sql/hql) parameter, I should wrap it as a CustomType like this:

query.SetParameter(nameof(intArr), intArr, new CustomType(typeof(Int32ArrayType), null));

I have searched the code related to CustomType in nhibernate-core , but can't find any info about how to set CustomType parameter with linq query.

Would you please provide more information on it?

[beginor]

I checked the parameter setting logic, I found the magic.

Just call NHibernate.Type.TypeFactory.RegisterType() to register custom types in constructor of NpgSqlDialect , every thing works! no need to override DefaultQueryProvider.

Final C# entity is:

[Class(Schema = "public", Table = "arr_test")]
public class ArrTestEntity {
    [Id(Name = nameof(Id), Column = "id", Type = "long", Generator = "trigger-identity")]
    public virtual long Id { get; set; }
    [Property(Column = "int_arr", Type = "int[]")]
    public virtual int[] IntArr { get; set; }
    [Property(Column = "str_arr", Type = "string[]")]
    public virtual string[] StrArr { get; set; }
}

and the xml map generated is:

<class table="arr_test" schema="public" name="UnitTest.ArrTestEntity, UnitTest">
  <id name="Id" column="id" type="long" generator="trigger-identity" />
  <property name="IntArr" type="int[]" column="int_arr" />
  <property name="StrArr" type="string[]" column="str_arr" />
</class>

and I can use array in linq query like this:

var str = "a";
var query2 = session.Query<ArrTestEntity>().Where(
    x => x.StrArr.ArrayContains(str)
);

string[] strArr = ["a", "c"];
var query1 = session.Query<ArrTestEntity>().Where(
    x => x.StrArr.ArrayIntersects(strArr)
);
var data1 = await query1.ToListAsync();

var idArr = idList.ToArray();
var query2 = session.Query<ArrTestEntity>().Where(
    x => idArr.ArrayContains(x.Id)
);

Anyway, thanks for your kind help! @hazzik .