Linq-to-SQL help - selecting duplicate rows

Question

So here is what I am trying to do:

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

Simple in SQL. I cannot figure out how to write this in LINQ. Does anyone know how to do this?

The table has only three columns, identifier, subscriber and subscription. I am trying to find duplicate lines where the subscriber == is signed.

Answer 1

You can 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 can check LINQ samples at http://msdn.microsoft.com/en-us/vcsharp/aa336746.aspx

Answer 2

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

I hope for this help.

Answer 3

. . WHERE. , . Linq To SQL SQL, WHERE .

, , .

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

First, an example using extension methods. That is how 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, an example of the syntax of the request.

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

        }