nHibernate method using Criteria for selecting parents for which strings in their child collection contain a certain searchterm

I need to do a search on company address - if a company has a certain string in one of it's addreses it must appear in the search results ( something like regex '%string%' ).

nHibernate mapping file for the Company looks like this:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="TaskMappings"
                   namespace="TaskMappings">
    <class name="Company">
        <id name="Id">
            <generator class="sequence">
                <param name="sequence">company_id_seq</param>
            </generator>
        </id>
        <property name="Name" />
        <property name="Fax" />
        <property name="PostalCode" />

        <bag name="Users" cascade="all-delete-orphan" inverse="true">
            <key column="UserCompany" />
            <one-to-many class="User" />
        </bag>

        <bag name="Phone" cascade="all-delete-orphan" lazy="false">
            <key column="PhoneCompany" />
            <element column="Phone" />
        </bag>

        <bag name="Email" cascade="all-delete-orphan" lazy="false">
            <key column="EmailCompany" />
            <element column="Email" />
        </bag>

        <bag name="Addresses" table="address" cascade="all-delete-orphan" lazy="false">
            <key column="AddressCompany" />
            <element column="Address" type="String"/>
        </bag>
    </class>
</hibernate-mapping>

and the Company entity class like this:

public class Company : Entity<int>
{
    public virtual string Name { get; set; }
    public virtual string Fax { get; set; }
    public virtual string PostalCode { get; set; }

    private IList<string> _phone = new List<strin开发者_开发技巧g>();
    public virtual IList<string> Phone
    {
        get { return _phone; }
        set { _phone = value; }
    }

    private IList<string> _email = new List<string>();
    public virtual IList<string> Email
    {
        get { return _email; }
        set { _email = value; }
    }

    private IList<string> _addresses = new List<string>();
    public virtual IList<string> Addresses
    {
        get { return _addresses; }
        set { _addresses = value; }
    }

    private IList<User> users = new List<User>();
    public virtual IList<User> Users
    {
        get { return users; }
        set { users = value; }
    }
}

My question is: how can i do the search using criteria preferably? I need the result as an IList. Thanks for your answers! :)

You could try:

ensure your Address entity has a Company on it and references it in its mapping then change your company entity for addresses to:

private IList<Address> _addresses = new List<Address>();
public virtual IList<Address> Addresses
{
    get { return _addresses; }
    set { _addresses = value; }
}

and then try this criteria:

var criteria = DetachedCriteria.For<Company>()
   .CreateCriteria("this.Addresses", "a")
   .SetFetchMode("a", FetchMode.Join)
   .Add(Restrictions.InsensitiveLike("a.Address", <string variable>, MatchMode.Anywhere))
   .SetResultTransformer(new DistinctRootEntityTransformer());

And then just execute that criteria in whatever session you have. My question is, why are addresses mapped onto Company, but are simply a list of strings? It would simplify things for you if Addresses were an entity mapped onto company.

Looks like this cannot be done using the Criteria API (although I'm not 100%) see here for another similar question. But I have managed to get it to work using a HQL query.

var query = session.CreateQuery("select c from Company c 
join c.Addresses a where a like '%string%'").List<Company>();

Something like:

HibernateDelegate<IList<IAssetLiabilityModel>> del = delegate(ISession session)
    {
        ICriteria criteria = session.CreateCriteria(typeof(ICompany));
        criteria.CreateCriteria("Company.Addresses", "Addresses");
        criteria.Add(Restrictions.Like("Addresses",<your_search_string>)); 
        criteria.SetResultTransformer(CriteriaSpecification.DistinctRootEntity);
        HibernateTemplate.PrepareCriteria(criteria);
        return criteria.List<ICompany>();
    };
    IList<ICompany> companies = HibernateTemplate.Execute(del);