How can I map my LINQ to SQL grouped result to this existing interface?

Question

I have a LINQ to SQL query that returns a grouped collection of Sponsor objects like so:

var result = ( from s in db.Sponsors
               join sl in sb.SponsorLevels on s.SponsorLevelId equals sl.SponsorLevelId
               select new Sponsor 
               {
                 Name = s.Name,
                 Level = sl.LevelName
               }
             ).GroupBy(s => s.LevelName);

My application already uses an interface defined as...

public interface ISponsorLevelGroup
{
    string Level { get; set; }
    IList<Sponsor> Sponsors { get; set; }
}

...where the Level string property is the grouping key from the LINQ result. I ultimately want to get the LINQ result into an IList<ISponsorLevelGroup>.

Logically my LINQ to SQL result is a collection of ISponsorLevelGroup objects, but I am not sure on how to most efficiently map one to the other. I seem to be relegated to manually traversing the groups and the collections within them at this point and copying the data into objects which implement my specific interface. Is there a better way? How can I most effectively push my LINQ result into an IList<ISponsorLevelGroup>?

Answer 1

Have you tried:

private class SponsorLevelGroup : ISponsorLevelGroup
{
   public string Level { get; set; }
   public IList<Sponsor> Sponsors { get; set; }
}

var result = ( from s in db.Sponsors
               join sl in sb.SponsorLevels on s.SponsorLevelId equals sl.SponsorLevelId
               select new Sponsor 
               {
                 Name = s.Name,
                 Level = sl.LevelName
               }
             ).GroupBy(s => s.LevelName)
              .Select(g => new SponsorLevelGroup
                               {
                                   Level = g.Key,
                                   Sponsors = g.ToList()
                                }) ;

This should return you a collection ISponsorLevelGroup objects

Answer 2

Is this what you need? I didn't create a new Sponsor class like you have. See if this makes sense.

    public class Sponsor
    {
        public int SponsorLevelId { get; set; }
        public string Name { get; set; }

        public override string ToString()
        {
            return string.Format("Name: {0}", Name);
        }
    }

    public class SponsorLevel
    {
        public int SponsorLevelId { get; set; }
        public string LevelName { get; set; }
    }

    public class SponsorLevelGroup
    {
        public string Level { get; set; }
        public IList<Sponsor> Sponsors { get; set; }

        public override string ToString()
        {
            return string.Format("Level: {0} Sponsors: {1}", Level, Sponsors.Count);
        }
    }

    static void Main(string[] args)
    {
        List<Sponsor> sponsors = new List<Sponsor>()
        {
            new Sponsor { SponsorLevelId = 1, Name = "A" },
            new Sponsor { SponsorLevelId = 2, Name = "B" },
            new Sponsor { SponsorLevelId = 1, Name = "C" },
            new Sponsor { SponsorLevelId = 3, Name = "D" }
        };

        List<SponsorLevel> sponsorLevels = new List<SponsorLevel>()
        {
            new SponsorLevel { SponsorLevelId = 1, LevelName = "L1" },
            new SponsorLevel { SponsorLevelId = 2, LevelName = "L2" },
            new SponsorLevel { SponsorLevelId = 3, LevelName = "L3" }
        };

        var result = (from s in sponsors
                      join sl in sponsorLevels on s.SponsorLevelId equals sl.SponsorLevelId
                      group s by sl.LevelName into g
                      select new SponsorLevelGroup
                      {
                          Level = g.Key,
                          Sponsors = g.ToList()
                      }
         );

        foreach (var r in result)
        {
            Console.WriteLine(r);
        }
     }

Prints:

Level: L1 Sponsors: 2
Level: L2 Sponsors: 1
Level: L3 Sponsors: 1