LINQ query to obtain items that appear twice in a database table?

Question

Hello I have a table Artists and in that table there's the value BandID and I want to obtain BandIDs from the Artist list who occur twice in the Artist table. What's the correct LINQ query for this?

public class Artiest
    {
        public int ArtiestID { get; set; }
        public string Naam { get; set; } 

        public int InstrumentID { get; set; }
        public Instrument Instrument { get; set; }

        public int PopgroepID { get; set; }
        public Popgroep Popgroep { get; set; }

    }

I have tried to convert this to LINQ but I'm not able to translate it.

 var q =
        from g in arr.GroupBy(x => x)
        where g.Count() == 1
        select g.First();

`BandID` does not exist in the code you posted. What _exactly_ do you want to do? — maccettura, Nov 27 '17 at 17:50
So you want to get a collection of `int` (PopgroepID) from all `Artiest` objects that occur more than once in a list? — maccettura, Nov 27 '17 at 17:54

score 0 · Answer 1 · answered Nov 27 '17 at 18:50

You should specify your question. What exactly is a "duplicate value" to you? Determined by which value? Of these duplicate artists, which one's PopGroepId do you want? One possible solution is

context.Artiests
    .GroupBy(x=>x.Naam)
    .Where(x=>x.Count()==2)
    .Select(x=>x.FirstOrDefault().PopGroepId);

This will get the PopGroepId of the first Artiest of whose name are exactly two entries in the database table.

LINQ query to obtain items that appear twice in a database table?

1 Answers1