Hi, Richard... Wow - lots to get my head around here. I appreciate your help and will start going through those examples and pointers line by line so I can grok the various options/approaches.
Thanks again! On Jul 31, 2:04 pm, "Richard Brown \(gmail\)" <[email protected]> wrote: > 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 > >> >> already filtered down to those that are $40 and under... but also want > >> >> to know how to add that criteria into the same query (as in the SQL). > >> >> From the docs, it looks like I want to start with > > >> >> // assume an ICollection<Product> called "prodColl" > >> >> var list = session.CreateCriteria(typeof(Category)).CreateCriteria > >> >> (typeof(Product))... /* to join? */ > >> >> /* And if I were including the $40 criteria, then I'd follow the > >> >> line above with: > >> >> .Add(Expression.Lt("UnitPrice", 40)) > >> >> */ > >> >> /* - but what do I do with prodColl here to filter the resulting > >> >> category list? */ > >> >> .List<Category>(); > > >> >> I want to ensure that I'm only getting categories for the passed > >> >> collection of products. It seems one "brute force" approach would be > >> >> to iterate through the prodColl collection, grab the IDs, and then add > >> >> an Expression.In() criteria to the Product criteria, but that doesn't > >> >> feel right... > > >> >> Then > > ... > > 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 -~----------~----~----~----~------~----~------~--~---
