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
>>> >> 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
-~----------~----~----~----~------~----~------~--~---
