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