SolvedPrivate

Linq: How to perform GroupBy when working with 3 or more columns

Posted on 2013-11-19
6
42 Views
Last Modified: 2016-02-10
I start off with the following list

    List items = new List();

    items.add(new Vehicle { MakeID = 1, MakeName = "Oldsmobile", ModelID = 2, ModelName =   "98 Regency" });

    items.add(new Vehicle { MakeID = 1, MakeName = "Oldsmobile", ModelID = 3, ModelName = "88 Delta" });

    items.add(new Vehicle { MakeID = 1, MakeName = "Oldsmobile", ModelID = 4, ModelName = "Alero" });

    items.add(new Vehicle { MakeID = 1, MakeName = "Oldsmobile", ModelID = 5, ModelName = "442" });

    items.add(new Vehicle { MakeID = 2, MakeName = "Buick", ModelID = 11, ModelName = "Skylark" });

    items.add(new Vehicle { MakeID = 2, MakeName = "Buick", ModelID = 13, ModelName = "Electra 225" });

    items.add(new Vehicle { MakeID = 2, MakeName = "Buick", ModelID = 14, ModelName = "Regal" });

    items.add(new Vehicle { MakeID = 2, MakeName = "Buick", ModelID = 16, ModelName = "Enclave" });

But would like it converted to the following format:

    List newList = new List();

    items.add(new VehicleWithModelList { MakeID = 1, MakeName = "Oldsmobile", new List<Model> () { This list would contain the 4 oldsmobile models from above}});

    items.add(new VehicleWithModelList { MakeID = 2, MakeName = "Buick", new List<Model> () { This list would contain the 4 buick models from above}});

I have the following linq to object query but I keep getting syntax errors stating it expects a semicolon before the last close parenthesis.

    var newList = items.GroupBy(x => new {x.MakeID, x.ModelID, x.MakeName, x.ModelName}, (key, group) => new { MakeID = key.MakeID, MakeName = key.MakeName, ModelList = group.Select(z => new Model { ModelID = z.ModelID, ModelName = z.ModelName })).Cast< VehicleWithModelList >();

What would be the correct way to accomplish this?
0
Comment
Question by:rawcoder
  • 4
  • 2
6 Comments
 
LVL 23

Expert Comment

by:Jens Fiederer
ID: 39659725
Do you have a custom List type?  I'd expect List<Vehicle> here...
0
 
LVL 23

Expert Comment

by:Jens Fiederer
ID: 39659779
Any way, I'd do it like this:

            List<Vehicle> olds = new List<Vehicle>();
            olds.Add(new Vehicle { MakeID = 1, MakeName = "Oldsmobile", ModelID = 2, ModelName = "98 Regency" });
            olds.Add(new Vehicle { MakeID = 1, MakeName = "Oldsmobile", ModelID = 3, ModelName = "88 Delta" });
            olds.Add(new Vehicle { MakeID = 1, MakeName = "Oldsmobile", ModelID = 4, ModelName = "Alero" });
            olds.Add(new Vehicle { MakeID = 1, MakeName = "Oldsmobile", ModelID = 5, ModelName = "442" });

            List<Vehicle> buicks = new List<Vehicle>();
            buicks.Add(new Vehicle { MakeID = 2, MakeName = "Buick", ModelID = 11, ModelName = "Skylark" });
            buicks.Add(new Vehicle { MakeID = 2, MakeName = "Buick", ModelID = 13, ModelName = "Electra 225" });
            buicks.Add(new Vehicle { MakeID = 2, MakeName = "Buick", ModelID = 14, ModelName = "Regal" });
            buicks.Add(new Vehicle { MakeID = 2, MakeName = "Buick", ModelID = 16, ModelName = "Enclave" });

            List<Vehicle> all = olds.Concat(buicks).ToList();

            List<VehicleWithModelList> byHand = new List<VehicleWithModelList>();
            byHand.Add(new VehicleWithModelList { MakeID = 1, MakeName = "Oldsmobile", ModelList = olds });
            byHand.Add(new VehicleWithModelList { MakeID = 2, MakeName = "Buick", ModelList = buicks });

            IEnumerable<VehicleWithModelList> byGrouping = from x in all group x by x.MakeID into g
                    select new VehicleWithModelList { MakeID = g.Key, MakeName = g.First().MakeName, ModelList = g.ToList()};

Open in new window

0
 
LVL 23

Expert Comment

by:Jens Fiederer
ID: 39659790
Note the byHand is just putting the list together manually, byGrouping uses the LINQ group construction.  This requires MakeID and MakeName to be in lockstep, since it just uses the first MakeName found for the group...true in your example data.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39659808
Hi jensfiederer;

See if this fits your needs.

// Class level variable with original list
private List<Vehicle> items = new List<Vehicle>();

// Created original list of data
items.Add(new Vehicle { MakeID = 1, MakeName = "Oldsmobile", ModelID = 2, ModelName = "98 Regency" });
items.Add(new Vehicle { MakeID = 1, MakeName = "Oldsmobile", ModelID = 3, ModelName = "88 Delta" });
items.Add(new Vehicle { MakeID = 1, MakeName = "Oldsmobile", ModelID = 4, ModelName = "Alero" });
items.Add(new Vehicle { MakeID = 1, MakeName = "Oldsmobile", ModelID = 5, ModelName = "442" });
items.Add(new Vehicle { MakeID = 2, MakeName = "Buick", ModelID = 11, ModelName = "Skylark" });
items.Add(new Vehicle { MakeID = 2, MakeName = "Buick", ModelID = 13, ModelName = "Electra 225" });
items.Add(new Vehicle { MakeID = 2, MakeName = "Buick", ModelID = 14, ModelName = "Regal" });
items.Add(new Vehicle { MakeID = 2, MakeName = "Buick", ModelID = 16, ModelName = "Enclave" });

// Using Linq to Object to covert to a new format
var newList = from v in items
              group v by new {v.MakeID, v.MakeName}
              into vehicleGroup
              select new VehicleWithModelList()
              {
                  MakeID = vehicleGroup.Key.MakeID,
                  MakeName = vehicleGroup.Key.MakeName,
                  VehicleList = vehicleGroup.ToList()
              };
                         


// Class object used in original list
public class Vehicle
{
    public int MakeID { get; set; }
    public string MakeName { get; set; }
    public int ModelID { get; set; }
    public string ModelName { get; set; }
}
// Class object used to covert from old to new format
public class VehicleWithModelList
{
    private List<Vehicle> _vehicleList = new List<Vehicle>();

    public int MakeID { get; set; }
    public string MakeName { get; set; }
    public List<Vehicle> VehicleList
    {
        get { return _vehicleList; }
        set { _vehicleList = value; }
    } 
}                                     

Open in new window

0
 
LVL 23

Accepted Solution

by:
Jens Fiederer earned 500 total points
ID: 39659825
Yup, was just about to post this as a suggested improvement on my first try (same idea):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace linqgroup {
    class Program {
        static void Main(string[] args) {
            List<Vehicle> olds = new List<Vehicle>();
            olds.Add(new Vehicle { MakeID = 1, MakeName = "Oldsmobile", ModelID = 2, ModelName = "98 Regency" });
            olds.Add(new Vehicle { MakeID = 1, MakeName = "Oldsmobile", ModelID = 3, ModelName = "88 Delta" });
            olds.Add(new Vehicle { MakeID = 1, MakeName = "Oldsie", ModelID = 4, ModelName = "Alero" });
            olds.Add(new Vehicle { MakeID = 1, MakeName = "Oldsie", ModelID = 5, ModelName = "442" });

            List<Vehicle> buicks = new List<Vehicle>();
            buicks.Add(new Vehicle { MakeID = 2, MakeName = "Buick", ModelID = 11, ModelName = "Skylark" });
            buicks.Add(new Vehicle { MakeID = 2, MakeName = "Buick", ModelID = 13, ModelName = "Electra 225" });
            buicks.Add(new Vehicle { MakeID = 2, MakeName = "Buick", ModelID = 14, ModelName = "Regal" });
            buicks.Add(new Vehicle { MakeID = 2, MakeName = "Buick", ModelID = 16, ModelName = "Enclave" });

            List<Vehicle> all = olds.Concat(buicks).ToList();

            List<VehicleWithModelList> byHand = new List<VehicleWithModelList>();
            byHand.Add(new VehicleWithModelList { MakeID = 1, MakeName = "Oldsmobile", ModelList = olds });
            byHand.Add(new VehicleWithModelList { MakeID = 2, MakeName = "Buick", ModelList = buicks });

            IEnumerable<VehicleWithModelList> byGrouping = from x in all group x by x.MakeID into g
                    select new VehicleWithModelList { MakeID = g.Key, MakeName = g.First().MakeName, ModelList = g.ToList()};
            foreach (var x in byGrouping) {
                Console.WriteLine("{0}-{1}-{2}", x.MakeID, x.MakeName, x.ModelList.Count());
            }

            IEnumerable<VehicleWithModelList> by2Grouping = from x in all
                                                            group x by new KeyValuePair<int, string>(x.MakeID, x.MakeName)into g
                                                            select new VehicleWithModelList { MakeID = g.Key.Key, MakeName = g.Key.Value, ModelList = g.ToList() };
            foreach (var x in by2Grouping) {
                Console.WriteLine("{0}-{1}-{2}", x.MakeID, x.MakeName, x.ModelList.Count());
            }
        }


    }
    public class Vehicle {
        public int MakeID { get; set; }
        public string MakeName { get; set; }
        public int ModelID { get; set; }
        public string ModelName { get; set; }
    }

    public class VehicleWithModelList {
        public int MakeID { get; set; }
        public string MakeName { get; set; }
        private List<Vehicle> modelList = null;
        public IEnumerable<Vehicle> ModelList {
            get { return modelList; }
            set { modelList = value.ToList(); }
        }
    }
}

Open in new window

0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39659841
Sorry my original post was to be directed to the question author rawcoder.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now