translating SQL into NHibernate Query

[Karl Cassar]

I have a database schema, where I have a `Product`, `Category`,
`CategoryFeature`, and an `ProductCategoryFeatureValue`.

Basically, the features available to a product are listed in the
`ProductCategoryFeatureValue` table, which is the 'middle-link' for
the many-to-many collection.

I need to create a query, where i can find all products, which have
ALL the features selected by the user.

Example, doing a search for two features with ids 643229 & 667811 in
SQL terms, I would do something like this:

SELECT * FROM Product
JOIN ProductCategoryFeatureValue AS feature1 ON Product.id =
feature1.ProductID AND feature1.categoryfeatureid = 643229
JOIN productcategoryfeaturevalue AS feature2 ON Product.id =
feature2.ProductID AND feature2.categoryfeatureid = 667811

Another query which I could do is this:

SELECT * FROM product WHERE
((SELECT id FROM productcategoryfeaturevalue AS feature1 WHERE
feature1.ItemGroupID = product.id AND feature1.categoryFeatureID =
643229 LIMIT 1) IS NOT NULL)
AND
((SELECT id FROM productcategoryfeaturevalue AS feature2 WHERE
feature2.ItemGroupID = product.id AND feature2.categoryFeatureID =
667811 LIMIT 1) IS NOT NULL)

Both have been tested and work well. However, I cannot seem to
reproduce them using NHibernate. Any ideas?

Thanks a lot in advance!

[Gunnar Liljas]

Well, in LINQ it could be something like this..

Session.Query<Product>().Where(p=>p.Features.Any(f=>f.Id==643229) && p.Features.Any(f=>f.Id==667811))

I guess that the number of features in dynamic, so you could just concatenate in a loop.

var q=Session.Query<Product>();

foreach(var featureId in featureList)

{

   q=q.Where(p=>p.Features.Any(f=>f.Id==featureId);

}

 

And there are a bunch off other alternatives (using the .All method feel most right, but I don't think NH handles it)

Where(p=> p.Features.Count(f=>featureList.Contains(f.Id))==featureList.Length)

/G

2011/6/10 Karl Cassar <casa...@gmail.com>

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

[Maximilian Raditya]

>   SELECT * FROM Product
JOIN ProductCategoryFeatureValue AS feature1 ON Product.id =
feature1.ProductID AND feature1.categoryfeatureid = 643229
JOIN productcategoryfeaturevalue AS feature2 ON Product.id =
feature2.ProductID AND feature2.categoryfeatureid = 667811

 

I wonder why you don't use IN over JOIN in there so it could be like this:

 

SELECT * FROM Product

(INNER) JOIN ProductCategoryFeatureValue AS feature ON Product.id = feature.ProductID

WHERE feature.categoryfeatureid IN (643229, 667811)

 

Could it be an issue?

 

If NH Linq can't be used, perhaps you can use Criteria to express it.

 

 

 

 

--
Regards,

Maximilian Haru Raditya

 

 

 

 

[Gunnar Liljas]

That returns products with any of the given features. The OP wanted products with ALL the given features.

/G

2011/6/11 Maximilian Raditya <m4h...@gmail.com>

[Karl Cassar]

Yes, I want to do it such that the products contain ALL the given features.  Also, forgot to mention that I would like to do it in Queryover or the Criteria API.  Is it possible?  I'm still pondering my mind over this question!

[Gunnar Liljas]

Yes, it's certainly possible, using either of the two methods I showed, i.e adding association restrictions in a loop (JoinQueryOver or aliases) or by using the projected Count.

I don't have the tools at hand to create an example, but it shouldn't be too hard.

/G

2011/6/15 Karl Cassar <casa...@gmail.com>

Yes, I want to do it such that the products contain ALL the given features.  Also, forgot to mention that I would like to do it in Queryover or the Criteria API.  Is it possible?  I'm still pondering my mind over this question!

--

You received this message because you are subscribed to the Google Groups "nhusers" group.

To view this discussion on the web visit https://groups.google.com/d/msg/nhusers/-/lQ5NTBkmVQgJ.

[Karl Cassar]

I'm not using LINQ - Session.Query<T>() does not exist as a method.

I cannot find any way how to replicate that functionality using Criteria API or QueryOver.  I've tried various ways:

crit1.Add(NHibernate.Criterion.Restrictions.On<Product>(item => item.Features).IsInG(featureList)); 

Brings up error 'Cannot use collections with InExpression'

crit1.Add(NHibernate.Criterion.Restrictions.On<Product>(item => item.Features).IsIn(featuresPKeysList));

Brings up same error - 'Cannot use collections with InExpression'

crit1.Add(NHibernate.Criterion.Restrictions.Where<PropertyItem>(item=>item.PropertyItemFeatures.Contains(featureTmp1)));

Brings up error -  Unrecognised method call: System.Linq.Enumerable:Boolean Contains

--

Any ideas?  There doesn't seem to be much help over the internet on assocation/collection restrictions!

Thanks,

Karl

[Karl Cassar]

anything?  seems to be pretty complicated to do - I've spend days scouring Google for answers, to no avail :/

[John Davidson]

If all else fails and you know the sql you want to use then see

http://knol.google.com/k/nhibernate-chapter-14-native-sql#

CreateSQLQuery will allow direct execution of sql you define

John Davidson

On Tue, Jun 21, 2011 at 1:06 PM, Karl Cassar <casa...@gmail.com> wrote:

anything?  seems to be pretty complicated to do - I've spend days scouring Google for answers, to no avail :/

--

You received this message because you are subscribed to the Google Groups "nhusers" group.

To view this discussion on the web visit https://groups.google.com/d/msg/nhusers/-/5O7Qd42fbF8J.

[Karl Cassar]

Yes, but the problem is this criteria is for an advanced search, which has many conditions.  I dont want to convert it ALL to SQL, because of this one condition.

It can be done via HQL, but still again, I have it all setup in QueryOver and Criteria API.  HQL is a bit a headache for maintainability as it involves a lot of magic strings and concatenation to generate a criteria.

[CSharper]

I think the problem with your approach is that the LEFT hand side of
the IsInG restriction is a collection.
I'm not experienced in the Criterion API but I think you need to do
some subquery for the navigation from the product to the Features
property because it's a collection type.

And if you want the ALL semantics, you need to create one predicate
for each item in the "featureList" collection so the IsInG predicate
is not the right choice.

So create a subquery expressoin with Eq-predicate for each item in
your "featureList" variable.

On 16 Jun., 14:13, Karl Cassar <casas...@gmail.com> wrote:
> I'm not using LINQ - Session.Query<T>() does not exist as a method.
>
> I cannot find any way how to replicate that functionality using Criteria API
> or QueryOver.  I've tried various ways:
>
> crit1.Add(NHibernate.Criterion.Restrictions.On<Product>(item =>
> item.Features).IsInG(featureList));
>

> *Brings up error **'Cannot use collections with InExpression'*
> *
> *
>
> *crit1.Add(NHibernate.Criterion.Restrictions.On<Product>(item =>
> item.Features).IsIn(featuresPKeysList));*
>
> *Brings up same error - 'Cannot use collections with InExpression'*
> *
> *
>
> crit1.Add(NHibernate.Criterion.Restrictions.Where<PropertyItem>(item=>item.­PropertyItemFeatures.Contains(featureTmp1)));
>
> Brings up error -  *Unrecognised method call: System.Linq.Enumerable:Boolean
> Contains*
> *
> *
> *--*
> *
> *