NHibernate Criteria: Join Two Columns Using an IN Expression

Question

This is the SQL I want to execute:

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

I cannot find a suitable expression for this for me. And I don’t think I can use two expressions, because the domain name and group name should be combined.

Thank!

Answer 1

Can you use two expressions?

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

Another option is to use Expression.Sql.

Answer 2

Expression.Sql is as follows:

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

Answer 3

, , .

_ , . . _ , .

Answer 4

. . , nhibernate

Map(x => x.FullName).Formula("[domain_nm] + '\' + [group_nm]")

Then the request will look like this:

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