I have simple model with two-step relationship: River has many
Countries, Country has one Continent. I want to query for all rivers
located within given continent. For code simplicity, I've tried to use
two separate LINQ queries:
var countries = session.Query<Country>()
.Where(c => c.Continent.Id == continentId);
var data = session.Query<River>()
.Where(r => (r.Countries.Where(c => countries.Contains(c))).Any())
.ToList();
The Where condition in second query is satisfied if at least one of
river's countries exists in the country list for given continent. I
don't call ToList() on first query, so I assume it is not run
separately but merged (nested) into second one (or at least
NotSupportedException is thrown or something). Here is the SQL query
that actually runs:
select river0_.Id as Id2_,
river0_.Name as Name2_
from "River" river0_
where exists (select country2_.Id
from CountryToRiver countries1_,
"Country" country2_
where river0_.Id = countries1_.River_id
and countries1_.Country_id = country2_.Id
and (exists (select country3_.Id
from "Country" country3_
where country3_.Id =
country2_.Id)))
What do we have here? It gets all rivers, filter it for rivers that
are within countries (country2_ alias), that are equal to ANY country
(country3_ alias) - no continent condition at all! The query is
merged, it runs without errors and silently produces unexpected, wrong
results.
I know that this is not the best way of querying (I've described the
workaround I use in a blog post:
http://notherdev.blogspot.com/2011/10/linq-pitfalls-nested-queries.html),
but I feel it should work and I'll glad to know is this a bug or
expected behaviour and I'm doing something wrong.
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.
