I'm building a simple movie database with NH to learn how it all works, and have a typical many-to-many relationship between movies & genres. I want to create a feature to browse movies by genre, and while I can get this working, it feels a bit off.
You'll notice that it's pulling rows from both the genre table & the mapping table, which seems unecessary (though by no means horrible). Is there a more efficient means for querying based on a many-to-many relationship? Or is this as good as it gets?
I'm using 2.0.0Beta1 with SQL Server 2005 and .NET 3.5.
Mappings:
Code:
<class name="Movie">
<id name="Id">
<generator class="identity" />
</id>
<property name="Title" type="String" length="255" />
<bag name="Genres">
<key column="MovieId" />
<many-to-many class="Genre" column="GenreId" />
</bag>
</class>
<class name="Genre">
<id name="Id">
<generator class="identity" />
</id>
<property name="Title" type="String" />
</class>
Classes:
Code:
public class Movie
{
public virtual int Id { get; set; }
public virtual string Title { get; set; }
public virtual IList<Genre> Genres { get; set; }
}
public class Genre
{
public virtual int Id { get; set; }
public virtual string Title { get; set; }
}
Code:
Code:
_session.CreateCriteria(typeof(Movie))
.CreateCriteria("Genres")
.Add(Expression.Eq("Id", genre.Id));
This generates the following query:
Code:
SELECT this_.Id as Id0_1_, this_.Title as Title0_1_,
genres3_.MovieId as MovieId, genre1_.Id as GenreId, genre1_.Id as Id2_0_,
genre1_.Title as Title2_0_, this_.Title as __hibernate_sort_expr_1__
FROM Movie this_ inner join MoviesToGenres genres3_ on this_.Id=genres3_.MovieId
inner join Genre genre1_ on genres3_.GenreId=genre1_.Id WHERE genre1_.Id = @p0
Login
FAQ
Search