Linq to SQL with Group by

问题:

I am trying to convert this T-SQL to a Linq To SQL but can't work out the group by aggregate functions. Any help welcome.

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

回答1:

To group by a composite key, you typically use an anonymous type:

    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) };

回答2:

The exact answer I came up with was

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)
                    });
        }