使用Linq to SQL进行Group by操作

Question

我正在尝试将这个T-SQL转换为Linq To SQL，但无法解决按组聚合函数的问题。欢迎任何帮助。

select c.ClientID, GivenName, Surname, max(a.Address), max(t.Value)
from Client c
left join ClientAddress a on c.ClientID = a.ClientID
left join ClientContact t on c.ClientID = t.ClientID
group by c.ClientID, GivenName, Surname

- Craig

Answer 1

如果要按组合键进行分组，通常使用匿名类型：

    var qry = from x in someSource
              group x by new { x.ClientID, x.GivenName, x.Surname } into grp
              select new { grp.Key, Address = grp.Max(x => x.Address),
                  Value = grp.Max(x => x.Value) };

- Marc Gravell

Answer 2

我得出的确切答案是

public IQueryable<ClientSearchDTO> GetClientsDTO()
        {
            return (from client in this.Context.Clients
                   join address in this.Context.ClientAddresses on client.ClientID equals address.ClientID
                   join contact in this.Context.ClientContacts on client.ClientID equals contact.ClientID                   
                   where contact.ContactType == "Phone"
                   group client by new { client.ClientID, client.Surname, client.GivenName } into clientGroup
                   select new ClientSearchDTO()
                    {
                        ClientID = clientGroup.Key.ClientID,
                        Surname = clientGroup.Key.Surname,
                        GivenName = clientGroup.Key.GivenName,
                        Address = clientGroup.Max(x => x.ClientAddresses.FirstOrDefault().Address),
                        PhoneNumber = clientGroup.Max(x => x.ClientContacts.FirstOrDefault().Value)
                    });
        }

- Craig