Got it figured out. Feel free to comment if this isn't the best way to do
this:
// Create a sub-query for ingredients to include
Models.Recipes recipe = null;
var subquery = QueryOver.Of<RecipeIngredients>()
.Where(item => item.Recipe.RecipeId == recipe.RecipeId)
.Where(Restrictions.In("Ingredient", query.Include.Select(ing =>
Ingredients.FromId(ing)).ToArray()))
.Select(i => i.RecipeIngredientId).Take(1);
var q = session.QueryOver<Recipes>(() => recipe)
.WithSubquery.WhereExists(subquery)
.Take(100);
var results = q.List();
On Thu, Jan 23, 2014 at 8:40 AM, Mike Christensen <[email protected]>wrote:
> Thanks!
>
> So, if I run that code I get:
>
> An unhandled exception of type 'System.Exception' occurred in
> NHibernate.dll
>
> Additional information: Unrecognised method call:
> System.Linq.Enumerable:Boolean
> Any[TSource](System.Collections.Generic.IEnumerable`1[TSource],
> System.Func`2[TSource,System.Boolean])
>
> On the line:
>
> q = q.Where(r => r.Ingredients.Any(i =>
> query.Include.Contains(i.Ingredient.IngredientId)));
>
> r.Ingredients is an IList<RecipeIngredients>
>
> Mike
>
>
> On Wed, Jan 22, 2014 at 10:19 PM, Ted P <[email protected]> wrote:
>
>> And of course I meant to write "new List<int>() { 5, 10, 15 };", assuming
>> you have integers as primary keys.
>> :)
>>
>>
>> On Thursday, January 23, 2014 4:06:51 AM UTC+1, Mike Christensen wrote:
>>
>>> I have a table called `Recipes` which contain one recipe per row. I
>>> also have a table called `RecipeIngredients` which contain one ingredient
>>> as used by a particular recipe. Thus, each `Recipe` row has one or more
>>> children `RecipeIngredients` rows.
>>>
>>> What I'm trying to do is create a query to find all recipes that contain
>>> any ingredients in a list of desired ingredients. For example, show me all
>>> recipes that use either flour, eggs, or bananas.
>>>
>>> The SQL would look something like this:
>>>
>>> SELECT * FROM Recipes r
>>> WHERE EXISTS (select 1 from RecipeIngredients where RecipeId =
>>> r.RecipeId and IngredientId = ANY (5, 10, 15) limit 1);
>>>
>>> However, I'm having a tough time figuring out how to express this as a
>>> LINQ query, or using the QueryOver<T> method. I don't want to hard code in
>>> the SQL since this needs to be database agnostic and I want the configured
>>> NHibernate dialect to generate the correct code.
>>>
>>> Any ideas?
>>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "nhusers" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected].
>> To post to this group, send email to [email protected].
>> Visit this group at http://groups.google.com/group/nhusers.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>
>
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/nhusers.
For more options, visit https://groups.google.com/groups/opt_out.
