Linq to SQL: DISTINCT with Anonymous Types

Question

Given this code:

dgIPs.DataSource = 
    from act in Master.dc.Activities
    where act.Session.UID == Master.u.ID
    select new
    {
      Address = act.Session.IP.Address,
      Domain = act.Session.IP.Domain,
      FirstAccess = act.Session.IP.FirstAccess,
      LastAccess = act.Session.IP.LastAccess,
      IsSpider = act.Session.IP.isSpider,
      NumberProblems = act.Session.IP.NumProblems,
      NumberSessions = act.Session.IP.Sessions.Count()
    };

How do I pull the Distinct() based on distinct Address only? That is, if I simply add Distinct(), it evaluates the whole row as being distinct and thusly fails to find any duplicates. I want to return exactly one row for each act.Session.IP object.

I've already found this answer, but it seems to be a different situation. Also, Distinct() works fine if I just select act.Session.IP, but it has a column I wish to avoid retrieving and I'd rather not have to do this by manually binding my datagrid columns.

score 3 · Accepted Answer · answered Apr 12 '09 at 05:39

dgIPs.DataSource = 
    from act in Master.dc.Activities
    where act.Session.UID == Master.u.ID
    group act by act.Session.IP.Address into g
    let ip = g.First().Session.IP
    select new
    {
      Address = ip.Address,
      Domain = ip.Domain,
      FirstAccess = ip.FirstAccess,
      LastAccess = ip.LastAccess,
      IsSpider = ip.isSpider,
      NumberProblems = ip.NumProblems,
      NumberSessions = ip.Sessions.Count()
    };

Or:

dgIPs.DataSource = 
    from act in Master.dc.Activities
    where act.Session.UID == Master.u.ID
    group act.Session.IP by act.Session.IP.Address into g
    let ip = g.First()
    select new
    {
      Address = ip.Address,
      Domain = ip.Domain,
      FirstAccess = ip.FirstAccess,
      LastAccess = ip.LastAccess,
      IsSpider = ip.isSpider,
      NumberProblems = ip.NumProblems,
      NumberSessions = ip.Sessions.Count()
    };

I tried this approach and on profiling the result found that it generated some pretty wack SQL — LaserJesus, May 01 '09 at 03:11
Brehtt, agreed. It seems to always query the db at least twice. — David Murdoch, Aug 12 '11 at 23:33

score 0 · Answer 2 · answered Apr 12 '09 at 05:23

0

One of the overloads of Enumerable.Distinct accepts an IEqualityComparer instance. Simply write a class that implements IEqualityComparer and which only compares the two Address properties.

Unfortunately, you'll have to give a name to the anonymous class you're using.

answered Apr 12 '09 at 05:23

John Saunders

160,644
26
247
397

1

If I remember correctly, that overload isn't supported in LINQ to SQL (since it can't transfer an IEqualityComparer to the server), only in LINQ to Objects. So the poster would also need to call ToList() on the result set before calling Distinct(). – itowlson Apr 12 '09 at 05:39

Linq to SQL: DISTINCT with Anonymous Types

2 Answers2

Linked