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 again, I'm not even 100% certain that chaining the two
> >> CreateCriteria() calls like that is correct. Any pointers/guidance?
>
> >> Thanks!
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
