Linq-to-SQL Help - Selecting Duplicate Rows

Question

So this is what I am trying to do:

select subscriber, count(subscriber)
from subscribers
where subscriber = subscribedTo
group by subscriber
having count(subscriber) > 1

Easy enough in SQL. I can't figure out how to write this in LINQ. Anyone know how to go about this?

The table is just three cols, ID, subscriber and subscribedTo. I am trying to find duplicate rows where the subscriber == subscribedTo.

Answer 1

Please see my answer:

public  class MyClass
    {

        public int ID
        {
            get; set;
        }

        public string Subscriber
        {
            get; set;
        }

        public string SubscriberTo
        {
            get; set;
        }
    }

And the code to enumerate the list of my class is:

   var myList = new List<MyClass>();

            var douplicateRows = from element in myList
                                              where
                                                  string.Compare(element.Subscriber, element.SubscriberTo,
                                                                 StringComparison.InvariantCultureIgnoreCase) == 0
                                              group element by element.Subscriber
                                              into mygroup
                                              where mygroup.Count() > 1
                                              select new
                                                         {
                                                             Subscriber = mygroup.Key,
                                                             Elements= mygroup
                                                         };

Hope this help.

Answer 2

You may try this

var duplicatedSubscribers= 
    from s in subscribers 
    where s.subscribedTo == s.subscriber 
    group s by s.subscriber into g 
    where g.Count() > 1 
    select new { subscriber = g.Key, Count = g.Count() };

You may want to check the LINQ Samples in http://msdn.microsoft.com/en-us/vcsharp/aa336746.aspx

Answer 3

You can either use query syntax, or you can use the extension methods. I will provide examples in both. You can move the WHERE filtering around usually. I chose to keep it separate so that you can play with the code a little and see the difference in grouping. In Linq To SQL, it will translate better to SQL if you move the WHERE conditions to the expression with the grouping.

Your entity probably looks like this.

    public class CustomRowType
    {
        public int ID { get; set;}
        public string Subscriber { get; set; }
        public int SubscribedTo { get; set; }
    }

First, an example using extension methods. This is the way I usually prefer to do it.

    public void GroupTest()
    {
        // Create some data to work with.
        List<CustomRowType> testData = new List<CustomRowType>();
        testData.Add(new CustomRowType() { ID = 1, Subscriber = "Subscriber 1", SubscribedTo = 40 });
        testData.Add(new CustomRowType() { ID = 2, Subscriber = "Subscriber 2", SubscribedTo = 1 });
        testData.Add(new CustomRowType() { ID = 3, Subscriber = "Subscriber 3", SubscribedTo = 2 });
        testData.Add(new CustomRowType() { ID = 4, Subscriber = "Subscriber 4", SubscribedTo = 2 });

        // Group data (equivalent to GROUP BY clause)
        var grp = testData.GroupBy(
            row => row.SubscribedTo,
            (key, row) => new
            {
                Data = row,
                // Here is the aggregate value
                KeyCount = testData.Count(i => i.SubscribedTo == key)   
            }
        );

        var output = grp
            // Filter group data (equivilent to HAVING clause)
            .Where(g => g.KeyCount > 1) // Remove this line to see all aggregate data.
            // Flatten group data (equivalent to SELECT clause)
            .SelectMany(g => g.Data
                .Select(d => d.ID.ToString() + "(" + d.Subscriber + ") -> " + d.SubscribedTo.ToString()+" with (" + g.KeyCount.ToString() + ") total subscriptions" ))
            .ToList();

        listBox1.Items.Clear();
        output.ForEach(s => listBox1.Items.Add(s));

    }

Next, a query syntax example.

public void GroupTest()
        {
            // Create some data to work with.
            List<CustomRowType> testData = new List<CustomRowType>();
            testData.Add(new CustomRowType() { ID = 1, Subscriber = "Subscriber 1", SubscribedTo = 40 });
            testData.Add(new CustomRowType() { ID = 2, Subscriber = "Subscriber 2", SubscribedTo = 1 });
            testData.Add(new CustomRowType() { ID = 3, Subscriber = "Subscriber 3", SubscribedTo = 2 });
            testData.Add(new CustomRowType() { ID = 4, Subscriber = "Subscriber 4", SubscribedTo = 2 });


            // Using query syntax
            var grp = (
                from d in testData
                group d by d.SubscribedTo into g
                select g
            );


            var output = grp
                // Filter
                .Where(g => g.Count() > 1)
                // Flatten group data (equivalent to SELECT clause)
                .SelectMany(g => g
                    .Select(d => d.ID.ToString() + "(" + d.Subscriber + ") -> " + d.SubscribedTo.ToString()+" with (" + g.Key.ToString() + ") total subscriptions" ))
                .ToList();

            listBox1.Items.Clear();
            output.ForEach(s => listBox1.Items.Add(s));

        }