LINQ多对多左联接分组 [英] LINQ many to many Left Join Grouping
问题描述
我有很多表关系:
CUS_Phone :拥有自己的唯一ID，其父表中的cus ID以及名称标题日期等. CUS_Phone_JCT :保存自己的唯一ID，即CUS_Phone的ID和CUS_Phone的ID CUS_Phone :拥有自己的唯一ID和电话号码
CUS_Phone: holds its own unique id, the cus id from its parent table as well as name title dates, etc... CUS_Phone_JCT: Holds its own unique id, the id from the CUS_Phone and the id from CUS_Phone CUS_Phone: Holds its own unique id, and the phone number
在这里，我有一个联接查询来检索所有客户名称和电话号码:
Here I have a join query to retrieve all the customer names and phone numbers:
var q = from c in CUS_Contact
join cp in CUS_Phone_JCT on c.Id equals cp.Contact_Id into cp2
from cp3 in cp2.DefaultIfEmpty()
join p in CUS_Phone on cp3.Phone_Id equals p.Id into p2
from p3 in p2.DefaultIfEmpty()
where c.Cus_Id == 9120
select new
{
c.Id,
c.Cus_Id,
c.Namefirst,
c.Namemiddle,
c.Namelast,
cp3.Phone.Phone,
c.Title,
c.Dept,
c.Des, c.Datecreate,
c.Dateupdate,
c.Usr_idcreate,
c.Usr_idupdate
};
foreach(var v in q){
Console.WriteLine(v.Id + "-" + v.Namefirst + "-" + v.Phone);
}
我该如何制定查询以将每个客户的电话号码分组?我想查看具有每个客户编号列表(IEnumerable List)的不同客户. LINQPad spanishOrders查询中有一个类似的示例，但是它们将每个订单的订单详细信息分组.我不确定如何使用我的架构执行此操作.谢谢！
How can I go about formulating the query to group the numbers per customer? I want to see distinct customers with a list of numbers per customer(IEnumerable List). Theres a similar example in the LINQPad spanishOrders query, but they group the order details per order. Im not sure how to do this with my schema. Thanks!
这是第一个答案输出的sql ...
Here's the sql outputted from the first answer...
-- Region Parameters
DECLARE @p0 Int SET @p0 = 4
-- EndRegion
SELECT (
SELECT [t8].[id]
FROM (
SELECT TOP (1) [t6].[id]
FROM [CUS_Contact] AS [t6]
LEFT OUTER JOIN [CUS_Phone_JCT] AS [t7] ON [t6].[id] = [t7].[Contact_Id]
WHERE [t2].[id] = [t6].[id]
) AS [t8]
) AS [Id], (
SELECT [t11].[Cus_Id]
FROM (
SELECT TOP (1) [t9].[Cus_Id]
FROM [CUS_Contact] AS [t9]
LEFT OUTER JOIN [CUS_Phone_JCT] AS [t10] ON [t9].[id] = [t10].[Contact_Id]
WHERE [t2].[id] = [t9].[id]
) AS [t11]
) AS [Cus_Id], (
SELECT [t14].[namefirst]
FROM (
SELECT TOP (1) [t12].[namefirst]
FROM [CUS_Contact] AS [t12]
LEFT OUTER JOIN [CUS_Phone_JCT] AS [t13] ON [t12].[id] = [t13].[Contact_Id]
WHERE [t2].[id] = [t12].[id]
) AS [t14]
) AS [Namefirst], (
SELECT [t17].[namemiddle]
FROM (
SELECT TOP (1) [t15].[namemiddle]
FROM [CUS_Contact] AS [t15]
LEFT OUTER JOIN [CUS_Phone_JCT] AS [t16] ON [t15].[id] = [t16].[Contact_Id]
WHERE [t2].[id] = [t15].[id]
) AS [t17]
) AS [Namemiddle], (
SELECT [t20].[namelast]
FROM (
SELECT TOP (1) [t18].[namelast]
FROM [CUS_Contact] AS [t18]
LEFT OUTER JOIN [CUS_Phone_JCT] AS [t19] ON [t18].[id] = [t19].[Contact_Id]
WHERE [t2].[id] = [t18].[id]
) AS [t20]
) AS [Namelast], (
SELECT [t23].[title]
FROM (
SELECT TOP (1) [t21].[title]
FROM [CUS_Contact] AS [t21]
LEFT OUTER JOIN [CUS_Phone_JCT] AS [t22] ON [t21].[id] = [t22].[Contact_Id]
WHERE [t2].[id] = [t21].[id]
) AS [t23]
) AS [Title], (
SELECT [t26].[dept]
FROM (
SELECT TOP (1) [t24].[dept]
FROM [CUS_Contact] AS [t24]
LEFT OUTER JOIN [CUS_Phone_JCT] AS [t25] ON [t24].[id] = [t25].[Contact_Id]
WHERE [t2].[id] = [t24].[id]
) AS [t26]
) AS [Dept], (
SELECT [t29].[des]
FROM (
SELECT TOP (1) [t27].[des]
FROM [CUS_Contact] AS [t27]
LEFT OUTER JOIN [CUS_Phone_JCT] AS [t28] ON [t27].[id] = [t28].[Contact_Id]
WHERE [t2].[id] = [t27].[id]
) AS [t29]
) AS [Des], (
SELECT [t32].[datecreate]
FROM (
SELECT TOP (1) [t30].[datecreate]
FROM [CUS_Contact] AS [t30]
LEFT OUTER JOIN [CUS_Phone_JCT] AS [t31] ON [t30].[id] = [t31].[Contact_Id]
WHERE [t2].[id] = [t30].[id]
) AS [t32]
) AS [Datecreate], (
SELECT [t35].[dateupdate]
FROM (
SELECT TOP (1) [t33].[dateupdate]
FROM [CUS_Contact] AS [t33]
LEFT OUTER JOIN [CUS_Phone_JCT] AS [t34] ON [t33].[id] = [t34].[Contact_Id]
WHERE [t2].[id] = [t33].[id]
) AS [t35]
) AS [Dateupdate], (
SELECT [t38].[usr_idcreate]
FROM (
SELECT TOP (1) [t36].[usr_idcreate]
FROM [CUS_Contact] AS [t36]
LEFT OUTER JOIN [CUS_Phone_JCT] AS [t37] ON [t36].[id] = [t37].[Contact_Id]
WHERE [t2].[id] = [t36].[id]
) AS [t38]
) AS [Usr_idcreate], (
SELECT [t41].[usr_idupdate]
FROM (
SELECT TOP (1) [t39].[usr_idupdate]
FROM [CUS_Contact] AS [t39]
LEFT OUTER JOIN [CUS_Phone_JCT] AS [t40] ON [t39].[id] = [t40].[Contact_Id]
WHERE [t2].[id] = [t39].[id]
) AS [t41]
) AS [Usr_idupdate], [t2].[id] AS [id2]
FROM (
SELECT [t0].[id]
FROM [CUS_Contact] AS [t0]
LEFT OUTER JOIN [CUS_Phone_JCT] AS [t1] ON [t0].[id] = [t1].[Contact_Id]
GROUP BY [t0].[id]
) AS [t2]
WHERE ((
SELECT [t5].[Cus_Id]
FROM (
SELECT TOP (1) [t3].[Cus_Id]
FROM [CUS_Contact] AS [t3]
LEFT OUTER JOIN [CUS_Phone_JCT] AS [t4] ON [t3].[id] = [t4].[Contact_Id]
WHERE [t2].[id] = [t3].[id]
) AS [t5]
)) = @p0
GO
-- Region Parameters
DECLARE @x1 Int SET @x1 = 9327
-- EndRegion
SELECT [t2].[phone] AS [value]
FROM [CUS_Contact] AS [t0]
LEFT OUTER JOIN [CUS_Phone_JCT] AS [t1] ON [t0].[id] = [t1].[Contact_Id]
LEFT OUTER JOIN [CUS_Phone] AS [t2] ON [t2].[id] = [t1].[Phone_Id]
WHERE @x1 = [t0].[id]
GO
-- Region Parameters
DECLARE @x1 Int SET @x1 = 9328
-- EndRegion
SELECT [t2].[phone] AS [value]
FROM [CUS_Contact] AS [t0]
LEFT OUTER JOIN [CUS_Phone_JCT] AS [t1] ON [t0].[id] = [t1].[Contact_Id]
LEFT OUTER JOIN [CUS_Phone] AS [t2] ON [t2].[id] = [t1].[Phone_Id]
WHERE @x1 = [t0].[id]
推荐答案
除了对CUS_Phone的join之外，您还对cp3.Phone.Phone的使用感到困惑，所以我假设前者意味着您不需要后者.否则，只需将join中的p3切换为p3，然后相应地调整g.Select().
I'm a bit confused by your use of cp3.Phone.Phone in addition to your join against CUS_Phone, so I'm going to assume the former means you don't need the latter. Otherwise, just switch in p3 for cp3 in the join and adjust the g.Select() accordingly.
也就是说，您应该能够简单地对联系人ID进行分组:
That said, you should be able to simply group on the contact ID:
var q = from c in CUS_Contact
join cp in CUS_Phone_JCT on c.Id equals cp.Contact_Id into cp2
from cp3 in cp2.DefaultIfEmpty()
group new { c, cp3.Phone.Phone } by c.Id into g
let c = g.First().c
select new {
c.Id,
c.Cus_Id,
c.Namefirst,
c.Namemiddle,
c.Namelast,
Phones = g.Select(x => x.Phone)
c.Title,
c.Dept,
c.Des, c.Datecreate,
c.Dateupdate,
c.Usr_idcreate,
c.Usr_idupdate
};
foreach(var v in q) {
Console.WriteLine(v.Id + "-" + v.Namefirst);
foreach(var p in v.Phones) {
Console.WriteLine(" -" + p);
}
}
在黑暗中拍摄一些照片以改善性能:
A few shots in the dark to improve performance:
var q = from c in CUS_Contact
join cp in CUS_Phone_JCT on c.Id equals cp.Contact_Id into cp2
from cp3 in cp2.DefaultIfEmpty()
group new { c, cp3.Phone.Phone } by c.Id into g
let c = g.First().c
select new {
c.Id,
c.Cus_Id,
c.Namefirst,
c.Namemiddle,
c.Namelast,
Phones = g.Select(x => x.Phone)
c.Title,
c.Dept,
c.Des, c.Datecreate,
c.Dateupdate,
c.Usr_idcreate,
c.Usr_idupdate
};
您也可以尝试使用组合键(包含所有c字段)而不是c.Id进行分组:
You might also try grouping by a composite key (with all your c fields) instead of c.Id:
group cp3.Phone.Phone
by new { c.Id, c.Cus_Id, c.Namefirst, ETC } into g
let c = g.Key
select new {
...
Phones = g.Select(p => p),
...
}
更新:调整了组合键示例，使其仅将Phone值分组，因为您需要的所有其他内容都应在键中.
Update: Tweaked the composite key example to only group the Phone value, since everything else you need should be in the key.
更新2:您可以通过嵌入子查询来简化很多事情:
Update 2: You might be able to simplify things quite a bit by embedding a subquery:
var q = from c in CUS_Contact
select new {
c.Id,
c.Cus_Id,
c.Namefirst,
c.Namemiddle,
c.Namelast,
Phones = (from cp in CUS_Phone_JCT
where c.Id == cp.Contact_Id
select cp.Phone.Phone),
c.Title,
c.Dept,
c.Des, c.Datecreate,
c.Dateupdate,
c.Usr_idcreate,
c.Usr_idupdate
};
这篇关于LINQ多对多左联接分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
