实体框架：多个来自正在生成交叉连接 [英] Entity Framework: multiple from is generating cross join

问题描述

我是Entity Framework的新手，我正在研究一个查询并找到一个解决方案，但是经过LINQPad的一些调查之后，我不喜欢所得到的SQL。

I'm new to Entity Framework and I was working on a query and found a solution, but after some investigation with LINQPad, I don't like the resulting SQL.

Linq查询是：

from p in Person
from pcc in p.PersonCreditCard
group p by new { pcc.CreditCard.CardType } into g
select new
{
     cname = g.Key.CardType,
     cptPerson = g.Count()
}

生成的sql查询是

The resulting sql query is

SELECT COUNT(*) AS [cptPerson], [t2].[CardType] AS [cname]
FROM [Person].[Person] AS [t0]
CROSS JOIN [Sales].[PersonCreditCard] AS [t1]
INNER JOIN [Sales].[CreditCard] AS [t2] ON [t2].[CreditCardID] = [t1].[CreditCardID]
WHERE [t1].[BusinessEntityID] = [t0].[BusinessEntityID]
GROUP BY [t2].[CardType

注意CROSS JOIN。
从我所知道的，使用CROSS JOIN不是一个好主意。
为什么会这样做？

Notice the CROSS JOIN. From what I know, it is not a good idea to use a CROSS JOIN. Why is it doing so?

还有另一种语法可以用来获得相同的结果（按照信用卡类型计算人数） / p>

And is there another syntax I could use to get the same result (counting the number of person by credit card type)

推荐答案

尝试通过 PersonCreditCard 开始查询：

var query= context.PersonCreditCard.GroupBy(c=>c.CreditCard.CardType)
                                   .Select(g=>new {
                                                    cname = g.Key,
                                                    cptPerson = g.Count()
                                                  }
                                          );

更新

我猜在code> PersonCreditCard 实体你也有一个 Person 的引用，所以你可以添加一个 Where 调用您的查询，如下所示：

Update

I guess in PersonCreditCard entity you also have a reference to a Person, so you can add a Where call to your query like this:

var query= context.PersonCreditCard.Where(pc=>pc.Person.Name=="John")
                                   .GroupBy(c=>c.CreditCard.CardType)
                                   .Select(g=>new {
                                                    cname = g.Key,
                                                    cptPerson = g.Count()
                                                  }
                                          );

更新2

我想我得到您的观点，如果要在结果中包含属于每个组的人员，可以执行以下操作：

Update 2

I think I get your point, if you want to include in the result the people that belong to each group, you can do the following:

var query= context.PersonCreditCard.Where(pc=>pc.Person.Name=="John")
                                   .GroupBy(c=>c.CreditCard.CardType)
                                   .Select(g=>new {
                                                    cname = g.Key,
                                                    cptPerson = g.Count()
                                                    people=g.Select(pc=>pc.Person);
                                                  }
                                          );

这篇关于实体框架：多个来自正在生成交叉连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！