Hi Richard, You mention "You don't have an association in the other direction (at least not in the mapping snippets you provided)"... which leads me to a question. Is there a best practice for this? I.e., is it a convention or standard to have an association in both directions for relationships such as this?
Thinking out loud here... In database terms, the Categories table doesn't really know anything of or care about the Products table. However, the Products table has a reference to Categories via the category_id foreign key in the Products table. So I'd say a Product has a Category, but I wouldn't necessarily say a Category has Products (though clearly this is the case)... but because the data model doesn't express it that way, I (sub-consciously) created the mapping to reflect that. In object terms, though, I suppose there's no reason it can't be mapped in both directions... so is there any guidance that you're aware of for NH mappings in this case? Should the Product class have a Category reference *AND* the Category class has a List/Set/Collection of Products that are of that category? It seems that having the association go both ways could make some of these types of queries easier... though I wonder if I'd be creating complexity somewhere else. I'm just diving into some of the mapping options around <bag>, <set>, etc... so your mentioning this got me curious. Thanks again for all your help... FWIW, I also just ordered "NHibernate in Action" so I can hopefully ask fewer 'newb' questions. ;) JD On Jul 31, 2:55 pm, "Richard Brown \(gmail\)" <[email protected]> wrote: > Sorry, the CreateCriteria() vs. CreateAlias() doesn't change the returned > type, the second example should have been: > > > session.CreateCriteria(typeof(Product)) > > .CreateCriteria("Category") // note this is the property name on > > Product, not the type > > .List<Product>(); > > (i.e., still returns a List<Product>) > > You don't have an association in the other direction (at least not in the > mapping snippets you provided). > > -------------------------------------------------- > From: "Richard Brown (gmail)" <[email protected]> > Sent: Friday, July 31, 2009 9:04 PM > To: "nhusers" <[email protected]> > Subject: Re: [nhusers] Re: Criteria Query Example > > > Hi Jeff, > > > The projection is required if you need the results to be distinct. > > Unfortunately, it would appear when you project onto an entity using > > Projections.Distinct, then NH just returns the identity (rather than the > > whole object). > > >> my thinking was to return a List<Category>(), though I'm certainly > > >>> >> // assume an ICollection<Product> called "prodColl" > >>> >> var list = session.CreateCriteria(typeof(Category)).CreateCriteria > >>> >> (typeof(Product))... /* to join? */ > > > The CreateCriteria() and CreateAlias() work on association paths through > > the domain model, so you can write: > > > session.CreateCriteria(typeof(Product)) > > .CreateAlias("Category", "categoryAlias") > > .List<Product>(); > > > or > > > session.CreateCriteria(typeof(Product)) > > .CreateCriteria("Category") // note this is the property name on > > Product, not the type > > .List<Category>(); > > > So actually you were correct to use CreateCriteria() the first time (but > > using the association in the other direction). > > > Yet another way of writing the query is using a sub-query to determine the > > "which set of Categories is represented by this particular set of > > Products" that you wanted. This will give you correctly hydrated Category > > objects: > > > DetachedCriteria categoriesForProducts = > > DetachedCriteria.For<Product>() > > .Add(Expression.Lt("UnitPrice", 40)) > > .Add(Expression.In("Id", myProductList.Select(p => > > p.Id).ToArray())) > > .SetProjection(Projections.Property("Category")); > > > IList<Category> actual = > > s.CreateCriteria(typeof(Category)) > > .Add(Subqueries.PropertyIn("Id", categoriesForProducts)) > > .List<Category>(); > > > Again, all of this may be overkill if you can live with select N+1 from > > the simplest query suggestion ... or preferably just traverse through the > > collection of Product objects you already have (if, for example, you know > > this will only lazy-load a couple of Categories). > > > You could do this using regular Linq extension methods (no NH required, > > just plain domain logic), for example: > > > IEnumerable<Category> actual = > > myAlreadyHydratedProductList > > .Where(p => p.UnitPrice < 40) > > .Select(p => p.Category) > > .Distinct(); > > > Hope that's of help. > > > Regards, > > Richard > > > -------------------------------------------------- > > From: "jd-nhusers" <[email protected]> > > Sent: Friday, July 31, 2009 5:11 PM > > To: "nhusers" <[email protected]> > > Subject: [nhusers] Re: Criteria Query Example > > >> Hello Richard, > > >> Thanks for getting back to me... much appreciated. Since I'm new to > >> NH, there's a lot in your code that's new to me (particularly the > >> whole "Projections" concept)... so I'll do some Googling around and > >> mining through the docs to try to make sense of it. > > >> In the meantime, in your first message you mentioned "If you want to > >> return a List<Category>(), then I suspect you want to use CreateAlias > >> () instead of CreateCriteria(), however I suspect..." -- in this case, > >> my thinking was to return a List<Category>(), though I'm certainly > >> open to suggestion if there's a smarter way to handle it. "It" in this > >> case is simply answering the question of "which set of Categories is > >> represented by this particular set of Products?" > > >> Below are the (simplified) mappings for this test database... > >> "simplified" in that I've removed a few simple <property> elements > >> that mapped to int, string, etc... > > >> Thanks for any further assistance you can provide! > > >> Product: > > >> <class name="Product" table="products"> > >> <id name="Id" column="product_id" type="Int32"> > >> <generator class="native" /> > >> </id> > >> <property name="Name" not-null="true" column="productname" /> > >> <many-to-one name="Category" column="category_id" > >> class="Category" /> > >> .... > >> </class> > > >> Category: > > >> <class name="Category" table="productcategories"> > >> <id name="Id" column="category_id" type ="Int32"> > >> <generator class="native" /> > >> </id> > >> <property name="Name" column="categoryname" /> > >> <many-to-one name="ParentCategory" class="Category" > >> column="parentcategory" /> > >> </class> > > >> On Jul 31, 3:13 am, "Richard Brown \(gmail\)" > >> <[email protected]> wrote: > >>> Hopefully the formatting of the query will be correct this time: > > >>> var categoryDetails = > >>> s.CreateCriteria(typeof(Product)) > >>> .Add(Expression.Lt("UnitPrice", 40)) > >>> .Add(Expression.In("Id", myProductList.Select(p => > >>> p.Id).ToArray())) > >>> .CreateAlias("Category", "categoryAlias") > >>> .SetProjection( > >>> Projections.Distinct( > >>> Projections.ProjectionList() > >>> .Add(Projections.Property("categoryAlias.Id")) > >>> .Add(Projections.Property("categoryAlias.Name")))) > >>> .List<object[]>() > >>> .Select(customProject => new { > >>> Id = (int)customProject[0], > >>> Name = (string)customProject[1]}); > > >>> foreach (var detail in categoryDetails) > >>> Console.WriteLine(detail.Id + ", " + detail.Name); > > >>> -------------------------------------------------- > >>> From: "Richard Brown (gmail)" <[email protected]> > >>> Sent: Friday, July 31, 2009 9:56 AM > >>> To: "nhusers" <[email protected]> > >>> Subject: Re: [nhusers] Criteria Query Example > > >>> > Hi Jeff, > > >>> > If you want to return a List<Category>(), then I suspect you want to > >>> > use > >>> > CreateAlias() instead of CreateCriteria(), however I suspect judging > >>> > from > >>> > the projection in your SQL query you actually want some custom > >>> > object/projection returned. > > >>> > If you want to restrict the products to a specific list, then I > >>> > suspect > >>> > you are going to have to use an '.In' clause (made simple by a > >>> > sprinkling > >>> > of LINQ these days). > > >>> > Although you are querying for a List<Category>(), if you 'project' > >>> > just on > >>> > a distinct list of Category, the query will return a list of 'id's, > >>> > and > >>> > each object might (depending on your mappings/settings) get > >>> > lazy-loaded. > >>> > (causing N+1 - very bad) > > >>> > I suspect you want the projection to return a custom object that you > >>> > can > >>> > then make type-safe using another sprinkling of LINQ, anonymous > >>> > objects > >>> > this time. > > >>> > The resulting query might look something like: > > >>> > var categoryDetails = > >>> > s.CreateCriteria(typeof(Product)) > >>> > .Add(Expression.Lt("UnitPrice", 40)) > >>> > .Add(Expression.In("Id", myProductList.Select(p => p.Id).ToArray())) > >>> > .CreateAlias("Category", "categoryAlias") > >>> > .SetProjection( > >>> > Projections.Distinct( > >>> > Projections.ProjectionList() > >>> > .Add(Projections.Property("categoryAlias.Id")) > >>> > .Add(Projections.Property("categoryAlias.Name")))) > >>> > .List<object[]>() > >>> > .Select(customProject => new { > >>> > Id = (int)customProject[0], > >>> > Name = (string)customProject[1]}); > > >>> > foreach (var detail in categoryDetails) > >>> > Console.WriteLine(detail.Id + ", " + detail.Name); > > >>> > If the N+1 wasn't going to be a problem (e.g., you know the Categories > >>> > are > >>> > already loaded in the current session), then you might get away with > >>> > something simpler like: > > >>> > s.CreateCriteria(typeof(Product)) > >>> > .Add(Expression.Lt("UnitPrice", 40)) > >>> > .Add(Expression.In("Id", myProductList.Select(p => p.Id).ToArray())) > >>> > .SetProjection(Projections.Distinct(Projections.Property("Category"))) > >>> > .List<Category>(); > > >>> > Also, this is just a guess at your model - obviously I haven't seen > >>> > the > >>> > model or the mappings. > > >>> > Hope that helps. > > >>> > Regards, > >>> > Richard > > >>> > -------------------------------------------------- > >>> > From: "jd-nhusers" <[email protected]> > >>> > Sent: Thursday, July 30, 2009 11:42 PM > >>> > To: "nhusers" <[email protected]> > >>> > Subject: [nhusers] Criteria Query Example > > >>> >> Hello, > > >>> >> Just getting started with NH and have run into a hitch with a query > >>> >> I'm trying to create via the ICriteria approach. I'm certain that > >>> >> this > >>> >> is a fairly simple thing and the reference docs seem to get me close, > >>> >> but here's an example- > > >>> >> Given two tables, Products and Categories, where each row in Products > >>> >> has a "Category_Id" foreign key and a mapping where the Product class > >>> >> has a Category instance member... I want a query that returns the > >>> >> distinct list of the categories for an arbitrary list of products. In > >>> >> SQL: > > >>> >> SELECT DISTINCT c.category_id, c.categoryname > >>> >> FROM categories c > >>> >> INNER JOIN products p ON p.category_id = c.category_id > >>> >> WHERE p.unitprice < 40; > > >>> >> In code, I've got an ICollection<Product> that gets passed in as an > >>> >> argument and want to return an ICollection<Category> accordingly. I'd > >>> >> like to first do it assuming that the collection of products is > > ... > > read more » --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/nhusers?hl=en -~----------~----~----~----~------~----~------~--~---
