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

发布于 2024-07-17 23:10:04 字数 819 浏览 8 评论 0原文

我在查询 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'

我环顾网络并没有真正找到任何合适的示例来说明如何执行此操作。 我得到的最接近的是:

List<Customer> _Customers = (from _LCustomers in _CRM.Customer.Include("CustomerInterest.Interest")
                                  where _LCustomers.CustomerInterest.Any(x => x.Interest.InterestName == "Football")
                                  select _LCustomers).ToList();

问题是,如果客户有多个兴趣,其中之一是“足球”,那么所有兴趣都会被退回。 我还查看了 All() ,它有相反的问题,即只有当他们有一种兴趣并且是足球时才会返回,如果他们有两种兴趣并且其中之一不是足球,则不会返回任何内容。

有人有什么想法吗?

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?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(5

财迷小姐 2024-07-24 23:10:04

试试这个,

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

希望这有帮助,

雷。

Try this,

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

Hope this helps,

Ray.

何必那么矫情 2024-07-24 23:10:04

我不确定你想获得什么。 具有客户兴趣和兴趣的客户列表? 只需根据客户的兴趣开始查询即可。

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

但这是非常多余的。 为什么不直接获取匹配的客户兴趣呢?

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

您仍然可以访问其他信息,而无需显式存储。

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

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);
}
山人契 2024-07-24 23:10:04
        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;
        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;
べ映画 2024-07-24 23:10:04

如果您想保持通用,更好的方法是使用实​​体 sql[Esql]。 因为 L2E 不支持 linq 查询中的 where 集合。

您不能使用

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

查询如下所示..

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

希望有帮助!

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!

夜无邪 2024-07-24 23:10:04

对于 LINQT 来说就这么多了。 尝试在数据库中使用视图或按照 Deepak N 所说的那样工作。
最好的

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

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文