NHibernate Criteria: concatenating two columns with an IN Expression

Question

This is the SQL that I want to accomplish:

WHERE domain_nm + '\' + group_nm in ('DOMAINNAME\USERNAME1','DOMAINNAME2\USERNAME2')

I can't for the life of me find an appropriate Expression for this. And I don't think I can use two expressions as the domain name and the group name need to be concatenated.

Thanks!

Answer 1

Can you not use two Expressions?

criteria
  .Add(Expression.In("DomainName", new string[] { "DOMAINNAME", "DOMAINNAME2" }))
  .Add(Expression.In("GroupName", new string[] { "USERNAME1", "USERNAME2" })

The other option is to use Expression.Sql.

Answer 2

I would just like to point out that two expressions will NOT work as I need a single expression.

DomainName and GroupName need to be concatenated before the in occurs. Think user names on a domain account. The DomainName and GroupName together are unique, not each separately.

Answer 3

The Expression.Sql is as follows:

.Add(Expression.Sql(String.Format("{{alias}}.domain_nm + '\' + {{alias}}.group_nm in ({0})", getSqlInString(userGroups))))