Failure of contains subquery with parameter

[ricanbm]

I tried to update NHibernate from version v5.3.3 to v5.4 and I noticed that until v.5.3.10, the following code worked fine:

var integralPlans = _repository.Queryable<IntegralPlan>()
    .Where(_ => _.Clients.Select(_ => _.Id).Contains(clientId))
    .OrderByDescending(_ => _.CreatedOn)
    .ToList();

But since v5.3.11, in that line I get an exception, with the following message:

{"Unable to cast object of type 'System.Guid' to type 'Contoso.ProjectName.Domain.Client'."}

The generated query is:

could not execute query
[ select integralpl0_.id as id1_202_, integralpl0_.changedBy as changedby2_202_, integralpl0_.changedOn as changedon3_202_, integralpl0_.createdBy_id as createdby4_202_, integralpl0_.createdOn as createdon5_202_, integralpl0_.dossierEntry_id as dossierentry6_202_, integralpl0_.entityNumber as entitynumber7_202_, integralpl0_.integralPlanTemplate_id as integralplantemplate8_202_, integralpl0_.status_id as status9_202_ from integralPlan integralpl0_ where exists (select client2_.id from integralPlan_clients clients1_, client client2_ where integralpl0_.id=clients1_.integralPlan_id and clients1_.client_id=client2_.id and client2_.id=?) order by integralpl0_.createdOn desc ]
  Name:p1 - Value:71460036-73dd-4f22-1234-633143769ac7 [Type: NHibernate.Type.ManyToOneType(Contoso.ProjectName.Domain.Client)]
[SQL: select integralpl0_.id as id1_202_, integralpl0_.changedBy as changedby2_202_, integralpl0_.changedOn as changedon3_202_, integralpl0_.createdBy_id as createdby4_202_, integralpl0_.createdOn as createdon5_202_, integralpl0_.dossierEntry_id as dossierentry6_202_, integralpl0_.entityNumber as entitynumber7_202_, integralpl0_.integralPlanTemplate_id as integralplantemplate8_202_, integralpl0_.status_id as status9_202_ from integralPlan integralpl0_ where exists (select client2_.id from integralPlan_clients clients1_, client client2_ where integralpl0_.id=clients1_.integralPlan_id and clients1_.client_id=client2_.id and client2_.id=?) order by integralpl0_.createdOn desc]

Looks like the p1 value is not placed in the generated query, but a ? appears in its place.

[gliljas]

The question mark signifies the SQL parameter, so that's correct.

I'm not able to reproduce the problem. However, using discard lambda parameters (underscore) is a potentially bad idea, and if there's any (badly written) expression rewriting going on, where the parameter names are relevant, it may be a good idea to write .Where(ip => ip.Clients.Select(client => client.Id)

[ricanbm]

Thank for your quick answer.

I just tried renaming the lambda parameters but it doesn't work. I can run manually the generated query correctly after replacing the ? with the Guid, so query generation looks good too, and in v5.3.10 that code works, that's why I think it might be some bug introduced in v5.3.11.

I hope somebody is able to reproduce it so we can find a solution for this.

[bahusoid]

To my surprise we never properly supported Contains(parameter) subqueries with Select.
It seems select part is simply ignored and we always use entity comparison with contains argument.
So for subquery like subquery.Select(s => s.Name).Contains(name) we generate

... AND subquery.ID = @name`

while it should be comparison on Name:

... AND subquery.Name = @name`

It started working by chance for your case in 5.3 due to other bug :)

As a workaround you can use Any instead of Contains. Something like:

_.Clients.Any(_ => _.Id == clientId)

[ricanbm]

Oh, ok. Good to know, thank you for your reply.

Yes, with the Any it works fine.

I see you created a new feature to support the Contains(parameter), and I would prefer delaying my version update than rewriting multiple places where we use the Contains into Any.
Do you have any estimate about when it could be production ready?

[bahusoid]

Fixed by #3220