Entity Framework - Linq To Entities - Problems With Many Problems

Question

I'm 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 looked through the network and did not find suitable examples of how to do this. The closest I have:

 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 is that if the client has more than one interest, and one of them is “Football”, then they all return. I also looked at All (), which has the opposite problem, that is, it will only return if they have interest, and this is football, if they have two, and one of them is not football, nothing returns .

Does anyone have any idea?

Answer 1

Try it,

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

Hope this helps,

Ray

Answer 2

I'm not sure what you want to receive. List of customers with interest and customer interest? Just run a customer interest request.

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

But this is very redundant. Why not just get relevant customer interests?

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

You can access other information without having to explicitly store it.

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

Answer 3

  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; 

Answer 4

If you are trying to keep it universal, it is best to go with the sql [Esql] entity. Coz L2E does not support where for collections in the linq request.

You cannot use

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

The request will look like this.

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

hope this helps!

Answer 5

This is for LINQT. Try using the view in your database or work as Deepak N. said. Best