none
Groupby problem RRS feed

  • Question

  • Hi!

    I'm coding an app(vs c#) with a music database(sql server).
    I use many to many relationships.

    Here are my tables:

    Records
    Instruments
    Musicians
    MusicianToInstrumentToRecord



    One musician is able to play on many records, One musician is able to play many intruments on one record.

    I managed to get to the point where i can select a record and get a display of the musicians appearing on it. And also display a list of what instrument each of them played on that record.
    My problem is that the list displays the musicians multiple times depending on how many instruments that they played on the record.

    So i have to group the musicians in the linq query.

    This is how i did in SQL server

    select m.FirstName,m.LastName from Records r
    inner join RecordToMusican rm
    on rm.Rec_ID = r.Rec_ID
    inner join Musicans m
    on m.Musican_ID = rm.Musican_ID
    where r.Rec_ID = 1
    group by m.FirstName, m.LastName

    And this is how im trying to do it in vs c#.
    im adding an returning the musicians in a List, and theres where i get the problem i think.


    public List<Instrument> SelectInstrument(Record record, Musican musican)
            {
                List<Instrument> tempList = new List<Instrument>();

                var Instrument = from i in db.Instruments
                                 join rm in db.RecordToMusicans
                                 on i.Inst_ID equals rm.Inst_ID
                                 join m in db.Musicans
                                 on rm.Musican_ID equals m.Musican_ID
                                 join r in db.Records
                                 on rm.Rec_ID equals r.Rec_ID
                                 where r.Rec_ID == record.Rec_ID && m.Musican_ID == musican.Musican_ID
                                 select i;
                                 
                                 
                                 

                foreach(var i in Instrument)
                    tempList.Add(i);

                return tempList;
            
            }


    this is the error message:

    Error    1    The best overloaded method match for 'System.Collections.Generic.List<MyMusic1._0.Musican>.Add(MyMusic1._0.Musican)' has some invalid arguments    C:\Documents and Settings\oscar\Desktop\Prog08\Databas\MyMusic1.0\MyMusic1.0\SQLManager.cs    35    17    MyMusic1.0

    Monday, November 3, 2008 5:01 PM

Answers

  • Hi Oscar,

    The problem is your select clause of your linq query. You're not selecting objects of the type Musician, thus you can't have a list of this kind of object. To make things easier for you, try something like...

    Code Snippet


            private IEnumerable<Musician> GetMusicians(Record record)
            {
                var listMusicians = new List<Musician>();

                var musicians = (from m in db.Musicians
                                 join rm in db.MusicianToInstrumentToRecords
                                   on m.MusicianID equals rm.MusicianID
                                 join r in db.Records on rm.RecID equals r.RecID
                                 where rm.RecID == record.RecID
                                 select m).Distinct();

                foreach (var m in musicians)
                {
                    listMusicians.Add(m);
                }

                return listMusicians;
            }


    or simply...

    Code Snippet

            private List<Musician> GetMusicians(Record record)
            {
                var musicians = (from m in db.Musicians
                                 join rm in db.MusicianToInstrumentToRecords

                                   on m.MusicianID equals rm.MusicianID
                                 join r in db.Records on rm.RecID equals r.RecID
                                 where rm.RecID == record.RecID
                                 select m).Distinct();

                return musicians.ToList();
            }










    Tuesday, November 4, 2008 2:43 AM

All replies

  • Hi Oscar,

    I can't see why you're using group by to eliminate duplicate names. Why don't you use distinct?
    Example in SQL:

    Code Snippet

    select distinct m.FirstName,m.LastName
    from Record r
    inner join MusicianToInstrumentToRecord rm on rm.RecID = r.RecID
    inner join Instrument i on rm.InstID = i.InstID
    inner join Musician m on m.MusicianID = rm.MusicianID
    where r.RecID = 1



    Using Linq...
    Code Snippet

    (from r in Record
    join rm in MusicianToInstrumentToRecord on r.RecID equals rm.RecID
    join i in Instrument on rm.InstID equals i.InstID
    join m in Musician on rm.MusicianID equals m.MusicianID
    where r.RecID == 1
    select new
    {
        m.FirstName,
        m.LastName
    }).Distinct()



    But... if you want to use information about the group, for example, count the number of instruments of a Musician, you can do something like...
    In SQL
    Code Snippet

    select m.MusicianID, m.FirstName, m.LastName, COUNT(i.InstID) as NumberOfInstruments
    from Record r
    inner join MusicianToInstrumentToRecord rm on rm.RecID = r.RecID
    inner join Instrument i on rm.InstID = i.InstID
    inner join Musician m on m.MusicianID = rm.MusicianID
    where r.RecID = 1
    group by m.MusicianID, m.FirstName, m.LastName


    Using Linq...
    Code Snippet

    from r in Record
    join rm in MusicianToInstrumentToRecord on r.RecID equals rm.RecID
    join i in Instrument on rm.InstID equals i.InstID
    join m in Musician on rm.MusicianID equals m.MusicianID
    where r.RecID == 1
    group m by new {m.MusicianID, m.FirstName, m.LastName} into g1
    select new
    {
        Musician = g1.Key.MusicianID,
        FirstName = g1.Key.FirstName,
        LastName = g1.Key.LastName,
        NumberOfInstruments = g1.Count()
    }   


    Monday, November 3, 2008 8:10 PM
  • I think i forgot to write that one of the big problems is when i'm adding the musicians to my List<>.
    i tried both of your examples, i didn't think of the distinct() solution, thank you very much for both anyway.
    but i still get an error on the tempList.Add() function.



    Error    1    The best overloaded method match for 'System.Collections.Generic.List<MyMusic1._0.Musican>.Add(MyMusic1._0.Musican)' has some invalid arguments    C:\Documents and Settings\oscar\Desktop\Prog08\Databas\MyMusic1.0\MyMusic1.0\SQLManager.cs    35    17    MyMusic1.0



    ***, i see now that i posted the wrong linq query. oups...
    this is the one for the musician:



    public List<Musican> SelectMusican(Record record)
            {
                List<Musican> tempList = new List<Musican>();

                var Musican = from m in db.Musicans
                              join rm in db.RecordToMusicans
                              on m.Musican_ID equals rm.Musican_ID
                              join r in db.Records
                              on rm.Rec_ID equals r.Rec_ID
                              where rm.Rec_ID == record.Rec_ID
                              group m by new { m.Musican_ID, m.FirstName, m.LastName } into g1
                              select new
                              {
                                  Musician = g1.Key.Musican_ID,
                                  FirstName = g1.Key.FirstName,
                                  LastName = g1.Key.LastName,
                                  NumberOfInstruments = g1.Count()
                              };







                foreach (var m in Musican)
                {
                    tempList.Add(m);
                }
               
               
                return tempList;
            }
     
    Monday, November 3, 2008 8:35 PM
  • Hi Oscar,

    The problem is your select clause of your linq query. You're not selecting objects of the type Musician, thus you can't have a list of this kind of object. To make things easier for you, try something like...

    Code Snippet


            private IEnumerable<Musician> GetMusicians(Record record)
            {
                var listMusicians = new List<Musician>();

                var musicians = (from m in db.Musicians
                                 join rm in db.MusicianToInstrumentToRecords
                                   on m.MusicianID equals rm.MusicianID
                                 join r in db.Records on rm.RecID equals r.RecID
                                 where rm.RecID == record.RecID
                                 select m).Distinct();

                foreach (var m in musicians)
                {
                    listMusicians.Add(m);
                }

                return listMusicians;
            }


    or simply...

    Code Snippet

            private List<Musician> GetMusicians(Record record)
            {
                var musicians = (from m in db.Musicians
                                 join rm in db.MusicianToInstrumentToRecords

                                   on m.MusicianID equals rm.MusicianID
                                 join r in db.Records on rm.RecID equals r.RecID
                                 where rm.RecID == record.RecID
                                 select m).Distinct();

                return musicians.ToList();
            }










    Tuesday, November 4, 2008 2:43 AM
  • your a genius!
    thank you so much!!




    Tuesday, November 4, 2008 6:47 AM