Hi Jeff,
You're right that the database tables have the relation in one direction,
while the model can have it in any direction (one example of 'object
relational impedance mismatch'). I'm not sure, however, that there's a
correct answer (convention/standard) for how to write the model.
If you imagine a model with and Order and OrderLine, you would imagine a
bag/set relation between them. Typically I would also make this a
bi-directional relationship (that is, I would also let OrderLine have a back
reference to its Order). I think I'd be out on a limb to suggest that there
is a standard, or best practice for this though - I think it depends on the
semantics of your model.
In your example, I'm not convinced I would create a bag/set relationship
from Category to Product just to make a single query easier. Personally, I
think I would avoid the query, and use the model to directly collect the
information you're after (letting NH lazy-load behind the scenes).
Oh, and one final addition to throw into the mix ... the examples I gave
were all ICriteria queries ... you can also use HQL, and I think it will
eagerly load the property out of the box:
s.CreateQuery(@"
select distinct p.Category
from Product p
where p in (:myProductList)
and p.UnitPrice < :unitPrice
")
.SetParameterList("myProductList", myProductList.ToArray())
.SetParameter("unitPrice", 40)
.List<Category>();
(Again, I hope I'm not confusing the issue.)
Regards,
Richard
--------------------------------------------------
From: "jd-nhusers" <[email protected]>
Sent: Wednesday, August 05, 2009 12:44 AM
To: "nhusers" <[email protected]>
Subject: [nhusers] Re: Criteria Query Example
>
> Hi Richard,
>
> You mention "You don't have an association in the other direction (at
> least not in the mapping snippets you provided)"... which leads me to
> a question. Is there a best practice for this? I.e., is it a
> convention or standard to have an association in both directions for
> relationships such as this?
>
> Thinking out loud here... In database terms, the Categories table
> doesn't really know anything of or care about the Products table.
> However, the Products table has a reference to Categories via the
> category_id foreign key in the Products table. So I'd say a Product
> has a Category, but I wouldn't necessarily say a Category has Products
> (though clearly this is the case)... but because the data model
> doesn't express it that way, I (sub-consciously) created the mapping
> to reflect that.
>
> In object terms, though, I suppose there's no reason it can't be
> mapped in both directions... so is there any guidance that you're
> aware of for NH mappings in this case? Should the Product class have a
> Category reference *AND* the Category class has a List/Set/Collection
> of Products that are of that category? It seems that having the
> association go both ways could make some of these types of queries
> easier... though I wonder if I'd be creating complexity somewhere
> else.
>
> I'm just diving into some of the mapping options around <bag>, <set>,
> etc... so your mentioning this got me curious.
>
> Thanks again for all your help... FWIW, I also just ordered
> "NHibernate in Action" so I can hopefully ask fewer 'newb'
> questions. ;)
>
> JD
>
>
>
> On Jul 31, 2:55 pm, "Richard Brown \(gmail\)"
> <[email protected]> wrote:
>> 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
>>
>> ...
>>
>> 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
-~----------~----~----~----~------~----~------~--~---
