NHibernate to return records, that contain a value in the List<string>

Question

I can't figure out how to write a NHibernate query, which would return all records, that have a certain value in the emails list. As I understand it doesn't support Contains() and if I use IsLike() it says "Cannot resolve property Emails" or smth.

Entity:

public class DemoClass
    {
        public virtual string Name { get; set; }
        public virtual string LastName { get; set; }
        public virtual List<string> Emails { get; set; }
    }

What I'm trying to achieve:

private DemoClass GetDemoClassByEmails(string email)
    {
        using (var session = _databaseFacade.OpenSession())
        {
            DemoClass demoClassAlias = null;

            var result = session.QueryOver(() => demoClassAlias)
                .Where(x => x.Emails.Contains(email))
                .SingleOrDefault();

            return result;
        }
    }

Answer 1

Does your version of NHibernate support LINQ queries? If so it's worth trying the LINQ equivalent. It's more readable, IMHO:

using (var session = _databaseFacade.OpenSession())
{
    return
        session
            .Query<DemoClass>()
            .SingleOrDefault(c => c.Emails.Contains(email));
}

There's a chance that the LINQ provider is unable to interpret your query and generate valid SQL, but I'm sure I've done something like this in the past.

Answer 2

You have to use JoinQuery over the Email association (Chapter "17.4. Associations" here) :

using (var session = _databaseFacade.OpenSession())
    {
        DemoClass demoClassAlias = null;

        var result = session.QueryOver(() => demoClassAlias)
            .JoinQueryOver<EmailClass>(x=>x.Emails)
                .Where(e => e.Email == email)
            .SingleOrDefault();

        return result;
    }