Getting rows with repeated data in multiple columns

I have the following table:

----------------------------------------------
ID|Name    |City      |Phone       |IsWorking
----------------------------------------------
1 |Joe     |New York  |111-111-1111|No
----------------------------------------------
2 |Helen   |LA        |123-456-7890|No
----------------------------------------------
3 |Mary    |Chicago   |373-737-7373|Yes
----------------------------------------------
4 |Joe     |New York  |999-999-9999|Yes
----------------------------------------------

I need to find rows with matching Name and City that occur more than once. I do this by grouping the rows by Name and City, checking the counts and getting the ID and IsWorking columns.

How can I write this LINQ query?

Here is the corresponding T-SQL That accomplishes this:

select a.ID, a.Name, a.City, a.Phone, a.IsWorking
    from tb as a
    right outer join (select Name,开发者_运维百科 City
                      from tb
                      group by Name, City
                      having count(*) > 1) as b
        on a.Name = b.Name and a.City = b.City

I'm not big on VB, I'm afraid, but in C# you could do something like this:

var items = new List<Item>()
            {
                new Item() { Id = 0, Name = "Joe", City = "New York", Phone = "111-111-1111", IsWorking = false },
                new Item() { Id = 1, Name = "Helen", City = "LA", Phone = "222-222-2222", IsWorking = false },
                new Item() { Id = 2, Name = "Mary", City = "Chicago", Phone = "333-333-3333", IsWorking = true },
                new Item() { Id = 3, Name = "Joe", City = "New York", Phone = "444-444-4444", IsWorking = true }
            };

var results = from i in items
              group i by new { i.Name, i.City }
              into x
              select new { Key = x.Key, Values = from a in x select new { ID = a.Id, a.IsWorking }};

This provides the output:

Key:    { Name = Joe, City = New York }
Values: { ID = 0, IsWorking = false }
        { ID = 3, IsWorking = true }

Key:    { Name = Helen, City = LA } 
Values: { ID = 1, IsWorking = false }

Key:    { Name = Mary, City = Chicago } 
Values: { ID = 0, IsWorking = true }

I'll try and provide a VB translation later, unless someone else gets there first!

I think I got it.

From d In (all.GroupBy(Function(a) New With {Key a.Name, Key a.City}) _
              .Where(Function(b) b.Count > 1) _
              .Select(Function(c) New With {Key c.Key.Name, Key c.Key.City}))
From a In all.Where(Function(e) e.Name = d.Name AndAlso e.City = d.City).DefaultIfEmpty
Select New With {a.ID, a.Name, a.City, a.Phone, a.IsWorking}

Thanks Tim for the approach.

I believe this query should work out for you.

Dim query = From row In db.Customers                           _
            Group 1 By row.ContactName, row.Phone Into Count() _
            Where Count > 1                                    _
            Join row2 In db.Customers                          _
                On New With {ContactName, Phone}               _
                Equals New With {row2.ContactName, row2.Phone} _
            Select row2

Or alternatively:

Dim query = From row In db.Customers                               _
            Where (From r In db.Customers                          _
                   Group 1 By r.ContactName, r.Phone Into Count()  _
                   Where Count > 1                                 _
                   Select ContactName, Phone)                      _
                  .Contains(New With {row.ContactName, row.Phone}) _
            Select row

Select the appropriate fields as needed.