Wednesday, 11 September 2013

How to query many-to-many relationship with 'AND' condition using LINQ and Entity Framework

How to query many-to-many relationship with 'AND' condition using LINQ and
Entity Framework

My current database solution includes three tables called Establishment,
Feature, and a linking many-to-many table called EstablishmentFeature
(since an establishment can have many features, and a feature can exists
across multiple establishments).
I need to generate a query that returns establishments that meet only
certain criteria, namely, which establishments have X features based on a
collection of featureId's being passed in. The establishment must have ALL
features that are being search, i.e.. AND not OR condition.
I got the SQL to achieve the desired result, but I am pulling my hair out
trying to work out the LINQ (lambra prefereably) equivalent. The T-SQL is:
SELECT e.[EstablishmentId], e.[Name], e.[Description]
FROM Establishment e
INNER JOIN EstablishmentFeature ef
ON e.[EstablishmentId] = ef.[EstablishmentId]
WHERE ef.[FeatureId] in ('20', '15', '72')
GROUP BY e.[EstablishmentId], e.[Name], e.[Description]
HAVING COUNT(*) = 3
I tried to use Linqer to convert the SQL but Linqer crashes when it
attempts the conversion. I tried reinstalling Linqer, but it crashes
without fail when trying to compile the LINQ syntax. (Simpler conversions
work though). Also tried to work out the LINQ equivalent using LinqPad,
but I just ended up chasing my tail...
Is this something I will have to use PredicateBuilder for? Somewhat
exhausted, I don't want to go through the PredicateBuilder learning curve
if there is a simple solution that is escaping me.

No comments:

Post a Comment