> (Again, I hope I'm not confusing the issue.) Nope... not at all. That's all very useful info and I appreciate it.
I haven't (yet) gone down the HQL route... I'll be investigating that soon as well. I think my main concern with that, and the reason I'd prefer going the ICriteria route, is that HQL seems like it'd have some of the same problems that embedded SQL has -- magic strings. So I don't find out until runtime if there's some goofy typo or syntax error... I do need to dig into it, though, and see if I can track down some guidance on when HQL makes more sense than the ICriteria approach. Thanks again, Jeff On Aug 5, 2:39 am, "Richard Brown \(gmail\)" <[email protected]> wrote: > Hi Jeff, > > You're right that the database tables have the relation in one direction, > while the model can have it in any direction (one example of 'object > relational impedance mismatch'). I'm not sure, however, that there's a > correct answer (convention/standard) for how to write the model. > > If you imagine a model with and Order and OrderLine, you would imagine a > bag/set relation between them. Typically I would also make this a > bi-directional relationship (that is, I would also let OrderLine have a back > reference to its Order). I think I'd be out on a limb to suggest that there > is a standard, or best practice for this though - I think it depends on the > semantics of your model. > > In your example, I'm not convinced I would create a bag/set relationship > fromCategoryto Product just to make a single query easier. Personally, I > think I would avoid the query, and use the model to directly collect the > information you're after (letting NH lazy-load behind the scenes). > > Oh, and one final addition to throw into the mix ... the examples I gave > were all ICriteria queries ... you can also use HQL, and I think it will > eagerly load the property out of the box: > > s.CreateQuery(@" > select distinct p.Category > from Product p > where p in (:myProductList) > and p.UnitPrice < :unitPrice > ") > .SetParameterList("myProductList", myProductList.ToArray()) > .SetParameter("unitPrice", 40) > .List<Category>(); > > (Again, I hope I'm not confusing the issue.) > > Regards, > Richard > > -------------------------------------------------- > From: "jd-nhusers" <[email protected]> > Sent: Wednesday, August 05, 2009 12:44 AM > To: "nhusers" <[email protected]> > Subject: [nhusers] Re: Criteria Query Example > > > > > 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 aCategory, but I wouldn't necessarily say aCategoryhas 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 > >Categoryreference *AND* theCategoryclass has a List/Set/Collection > > of Products that are of thatcategory? 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); > > >> >>> -------------------------------------------------- > > ... > > 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 -~----------~----~----~----~------~----~------~--~---
