I am having problems querying many-to-many relationships in Linq To Entities. I am basically trying to replicate this query using Linq:
Select * FROM Customer LEFT JOIN CustomerInterest ON Customer.CustomerID = CustomerInterest.CustomerID LEFT JOIN Interest ON CustomerInterest.InterestID = Interest.InterestID WHERE Interest.InterestName = 'Football'
I have looked around the net and not really found any suitable examples of how to do this. The closest I have got is:
List<Customer> _Customers = (from _LCustomers in _CRM.Customer.Include("CustomerInterest.Interest") where _LCustomers.CustomerInterest.Any(x => x.Interest.InterestName == "Football") select _LCustomers).ToList();
The problem with this is that if a customer has more than one interest and one of them is "Football" then all of them are returned. I have also looked at All() which has the inverse problem, i.e. will only return if they have one interest and it is football, if they have two and one of them isn't football nothing is returned.
Anyone got any ideas?
var result = from c in ctx.Customer from i in c.Interest where i.InterestName == "Football" select c;
Hope this helps,