CODEWITHSUNDEEP
c#linqnhibernate
mikkel
mikkel

Reputation: 45

NHibernate Linq - how to select WHERE IN

Given the following domain model:

Dog { Id, Name, Color }

Color { Id, Name }

How do I get the Colors for which there are dogs with NHibernate.Linq. In SQL I would 

SELECT Color.Id, Color.Name FROM Color 
    WHERE Id IN 
    (SELECT DISTINCT Dog.ColorId FROM Dog);

Upvotes: 3

Views: 5472

Answers (4)

hazzik
hazzik

Reputation: 13374

Why you need a subselect?

It is fairy simple.

from d in db.Dogs 
select d.Color

Upvotes: 0

Lukazoid
Lukazoid

Reputation: 19426

This might work:

Colors.Where(c => Dogs.Any(d => d.Color.Equals(c)))

However if you are matching purely on the color identifier, try this instead:

Colors.Where(c => Dogs.Any(d => d.Color.Id == c.Id))

These will give you all of the colors which are used by the dogs.

Upvotes: 5

cremor
cremor

Reputation: 6886

NHibernate will currently (3.2.0.GA) only create IN statements from Linq queries if you have a list of values. It will never create a IN ([subquery]) statement, that's just not implemented.

You may want to vote for it here: https://nhibernate.jira.com/browse/NH-2899

Upvotes: 1

apostolov
apostolov

Reputation: 1646

You can make a join :

from c in Color 
from d in dog
WHERE d.ColorId == c.Id 
select c

But that's still a join, so you could as easily try to get the dog colors with one query:

var dogColors = (from d in Dogs
                 select d.ColorId).Distinct().List()

And then get the Colors:

from c in Color
where dogColors.Contains(c.Id)
select c

Upvotes: 0

Related Questions