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