Problem Using NHibernate Criteria API to Get Specific Results from a Referenced Object
I have an Class that is named Show one of the properties "Country" is a reference to another table.
Show Class
public class Show
{
public virtual int ID { get; set; }
public virtual Countr开发者_如何学运维y CountryOrigin { get; set; }
public virtual string EnglishName { get; set; }
}
Country Class
public class Country
{
public virtual int ID { get; set; }
public virtual string Name { get; set; }
}
I have it all mapped and working, but now I am wanting to get more specific results. I have used the criteria api to get all the data and sort it, but now I only want to get shows based on country name. Here is what I thought would work, but apprently doesn't.
public IList<Show> AllShowsByCountry(string countryName)
{
IList<Show> shows;
shows = _session.CreateCriteria(typeof(Show))
.Add(Restrictions.Eq("CountryOrigin.Name", "China" ))
.AddOrder(Order.Asc("EnglishName"))
.List<Show>();
return shows;
}
I was thinking that the first part of the restriction might work similar to HQL and you can use objects.
1) The question I guess is am I mis-understanding how HQL works or criteria or both? 2) Also how would you do this properly using criteria?
Update Here is the error I am getting
could not resolve property: CountryOrigin.Name of: Entities.Show
For Criteria, use the following:
_session.CreateCriteria<Show>()
.CreateAlias("CountryOrigin", "country")
.Add(Restrictions.Eq("country.Name", countryName))
.AddOrder(Order.Asc("EnglishName"))
.List<Show>();
Of course HQL is easier when you are not constructing a dynamic query (search):
_session.CreateQuery(
@"
from Show
where CountryOrigin.Name = :countryName
order by EnglishName
")
.SetParameter("countryName", countryName)
.List<Show>();
And Linq always rocks:
_session.Query<Show>()
.Where(s => s.CountryOrigin.Name = countryName)
.OrderBy(s => EnglishName)
.ToList();
(.Query
is for NH 3.x; for 2.x use .Linq
)
精彩评论