Entity Framework - Linq To Entities - Many-To-Many Query Problems

Question

Hi, 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?

Answer 1

I am not sure what you want to obtain. A list of customers with a customer interest and a interest? Just start the query at customer interest.

context.CustomerInterest.
   Where(ci => ci.Interest.InterestName == "Football").
   Select(ci => new
   {
      Customer = ci.Customer,
      CustomerInterest = ci,
      Interest = ci.Interest
   });

But this is highly redudant. Why not just get the matching customer interests?

IEnumerable<CustomerInterest> customerInterests = context.CustomerInterest.
   Where(ci => ci.Interest.InterestName == "Football");

You can still access the other information without needing to store it explicitly.

foreach (CustomerInterest customerInterest in customerInterests)
{
   DoSomething(customerInterest);
   DoSomething(customerInterest.Customer);
   DoSomething(customerInterest.Interest);
}

Answer 2

If you trying to keep it generic, better way is to go with entity sql[Esql]. Coz L2E doesn't support where for collections in linq query.

You cannot use

customer.Interests.Where(interest => interest.Name =='FootBall')

The query would look like this..

context.CreateQuery(@"SELECT VALUE Customer FROM Customer WHERE EXISTS( SELECT VALUE FROM CustomerInterest WHERE CustomerInterest.Ineterest.Name = 'FootBall')).Include("Interest");

hope it helps!

Answer 3

That's to much for LINQT. Try use a view at your database or work as Deepak N said. Best

Answer 4

Try this,

var result = from c in ctx.Customer
             from i in c.Interest
             where i.InterestName == "Football"
             select c;

Hope this helps,

Ray.

Answer 5

        var results = from c in _CRM.Customer
                      from ci in c.Interests
                      join i in _CRM.Interests
                      on ci.ID equals i.ID
                      where i.Interest = "Football"
                      select c;