c# - linq to sql 在最后进行简单的分组

Question

我有这个 linq

(from a in Customers
join b in CustomerContacts.Where(p => p.Contact.ContactType.Code == "phone") on a.Id equals b.CustomerId into bb
from b in bb.DefaultIfEmpty()
join e in CustomerContacts.Where(p => p.Contact.ContactType.Code == "email") on a.Id equals e.CustomerId into ee
from e in ee.DefaultIfEmpty()
join f in Bookings.Where(p => p.EntityId == 4) on a.Id equals f.CustomerId into ff
from f in ff.DefaultIfEmpty()
join h in CustomerAddresses on a.Id equals h.CustomerId into hh
from h in hh.DefaultIfEmpty()
where
    (b.Contact.Value.Contains("123") || e.Contact.Value.Contains("123"))
    && (a.EntityId == 4 || f != null)
select new {
    a.Id,
    phone = b.Contact.Value,
    email = e.Contact.Value,
    count = Vehicles.Where(p => p.CustomerId == a.Id).Count(),
    h.Address.State,
    h.Address.Suburb
}
)

它翻译成sql(mysql)

SELECT `a`.`Id`, `b.Contact`.`Value` AS `phone`, `e.Contact`.`Value` AS `email`, (
    SELECT COUNT(*)
    FROM `Vehicle` AS `p2`
    WHERE `p2`.`CustomerId` = `a`.`Id`
) AS `count`, `h.Address`.`State`, `h.Address`.`Suburb`
FROM `Customer` AS `a`
LEFT JOIN (
    SELECT `p`.*
    FROM `CustomerContact` AS `p`
    INNER JOIN `Contact` AS `p.Contact` ON `p`.`ContactId` = `p.Contact`.`Id`
    INNER JOIN `ContactType` AS `p.Contact.ContactType` ON `p.Contact`.`ContactTypeId` = `p.Contact.ContactType`.`Id`
    WHERE `p.Contact.ContactType`.`Code` = 'phone'
) AS `t` ON `a`.`Id` = `t`.`CustomerId`
LEFT JOIN `Contact` AS `b.Contact` ON `t`.`ContactId` = `b.Contact`.`Id`
LEFT JOIN (
    SELECT `p0`.*
    FROM `CustomerContact` AS `p0`
    INNER JOIN `Contact` AS `p.Contact0` ON `p0`.`ContactId` = `p.Contact0`.`Id`
    INNER JOIN `ContactType` AS `p.Contact.ContactType0` ON `p.Contact0`.`ContactTypeId` = `p.Contact.ContactType0`.`Id`
    WHERE `p.Contact.ContactType0`.`Code` = 'email'
) AS `t0` ON `a`.`Id` = `t0`.`CustomerId`
LEFT JOIN `Contact` AS `e.Contact` ON `t0`.`ContactId` = `e.Contact`.`Id`
LEFT JOIN (
    SELECT `p1`.*
    FROM `Booking` AS `p1`
    WHERE `p1`.`EntityId` = 4
) AS `t1` ON `a`.`Id` = `t1`.`CustomerId`
LEFT JOIN `CustomerAddress` AS `h` ON `a`.`Id` = `h`.`CustomerId`
LEFT JOIN `Address` AS `h.Address` ON `h`.`AddressId` = `h.Address`.`Id`
WHERE ((LOCATE('123', `b.Contact`.`Value`) > 0) OR (LOCATE('123', `e.Contact`.`Value`) > 0)) AND ((`a`.`EntityId` = 4) OR `t1`.`Id` IS NOT NULL)

和结果

但是它包含重复的 Id，我想在末尾添加 group by a.Id 以删除重复的 Id。尝试使用 group by 但它无法实现这样的事情

SELECT `a`.`Id`, `b.Contact`.`Value` AS `phone`, `e.Contact`.`Value` AS `email`, (
    SELECT COUNT(*)
    FROM `Vehicle` AS `p2`
    WHERE `p2`.`CustomerId` = `a`.`Id`
) AS `count`, `h.Address`.`State`, `h.Address`.`Suburb`
FROM `Customer` AS `a`
LEFT JOIN (
    SELECT `p`.*
    FROM `CustomerContact` AS `p`
    INNER JOIN `Contact` AS `p.Contact` ON `p`.`ContactId` = `p.Contact`.`Id`
    INNER JOIN `ContactType` AS `p.Contact.ContactType` ON `p.Contact`.`ContactTypeId` = `p.Contact.ContactType`.`Id`
    WHERE `p.Contact.ContactType`.`Code` = 'phone'
) AS `t` ON `a`.`Id` = `t`.`CustomerId`
LEFT JOIN `Contact` AS `b.Contact` ON `t`.`ContactId` = `b.Contact`.`Id`
LEFT JOIN (
    SELECT `p0`.*
    FROM `CustomerContact` AS `p0`
    INNER JOIN `Contact` AS `p.Contact0` ON `p0`.`ContactId` = `p.Contact0`.`Id`
    INNER JOIN `ContactType` AS `p.Contact.ContactType0` ON `p.Contact0`.`ContactTypeId` = `p.Contact.ContactType0`.`Id`
    WHERE `p.Contact.ContactType0`.`Code` = 'email'
) AS `t0` ON `a`.`Id` = `t0`.`CustomerId`
LEFT JOIN `Contact` AS `e.Contact` ON `t0`.`ContactId` = `e.Contact`.`Id`
LEFT JOIN (
    SELECT `p1`.*
    FROM `Booking` AS `p1`
    WHERE `p1`.`EntityId` = 4
) AS `t1` ON `a`.`Id` = `t1`.`CustomerId`
LEFT JOIN `CustomerAddress` AS `h` ON `a`.`Id` = `h`.`CustomerId`
LEFT JOIN `Address` AS `h.Address` ON `h`.`AddressId` = `h.Address`.`Id`
WHERE ((LOCATE('123', `b.Contact`.`Value`) > 0) OR (LOCATE('123', `e.Contact`.`Value`) > 0)) AND ((`a`.`EntityId` = 4) OR `t1`.`Id` IS NOT NULL)
Group by `a`.`Id`

Answer 1

你尝试过这样的事情吗？

(from a in Customers
join b in CustomerContacts.Where(p => p.Contact.ContactType.Code == "phone") on a.Id equals b.CustomerId into bb
from b in bb.DefaultIfEmpty()
join e in CustomerContacts.Where(p => p.Contact.ContactType.Code == "email") on a.Id equals e.CustomerId into ee
from e in ee.DefaultIfEmpty()
join f in Bookings.Where(p => p.EntityId == 4) on a.Id equals f.CustomerId into ff
from f in ff.DefaultIfEmpty()
join h in CustomerAddresses on a.Id equals h.CustomerId into hh
from h in hh.DefaultIfEmpty()
where
    (b.Contact.Value.Contains("123") || e.Contact.Value.Contains("123"))
    && (a.EntityId == 4 || f != null)
select new {
    a.Id,
    phone = b.Contact.Value,
    email = e.Contact.Value,
    count = Vehicles.Where(p => p.CustomerId == a.Id).Count(),
    h.Address.State,
    h.Address.Suburb
}).GroupBy(i => i.Id).ToList();