实体框架 - Linq To Entities - 多对多查询问题

I am having problems querying many-to-many relationships in Linq To Entities. I am basically trying to replicate this query using Linq:

我在查询Linq To Entities中的多对多关系时遇到问题。我基本上试图使用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.

这样做的问题是,如果客户有多个兴趣,其中一个是“足球”,则返回所有这些兴趣。我也看过All()有逆问题,即只有他们有一个兴趣而且只有足球,如果他们有两个,其中一个不是足球,则不会返回。

Anyone got any ideas?

有人有任何想法吗?

5 个解决方案

#1

Try this,

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

Hope this helps,

希望这可以帮助,

Ray.

#2

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 redundant. 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);
}

#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;

#4

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.

如果你试图保持通用,更好的方法是使用实体sql [Esql]。 Coz L2E不支持linq查询中集合的位置。

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");

context.CreateQuery(@“SELECT VALUE Customer FROM Customer WHERE EXISTS(从CustomerInterest WHERE CustomerInterest.Ineterest.Name ='FootBall'中选择值))。包括(”兴趣“);

hope it helps!

希望能帮助到你!

#5

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

这对LINQT来说很重要。尝试使用您的数据库中的视图或作为Deepak N说。最好

#1