Intro
In part 1 of our series on LINQ we discussed the very basics of LINQ, pulling a few choice bits of data out of an array based on a simple filtering clause. This week we'll dive a little deeper into LINQ, exploring how to sort the results, group results, join 2 sources together to get a single output, and how to manipulate the structure of the output using projections. Hold onto yer hats (or other similar headgear such as beanies, earmuffs, etc)!
Ordering (orderby)
Everybody needs to sort data at some point in their coding career. To help illustrate my point with many of these LINQ operations, I'm going to compare using T-SQL against a fictional database in a zoo, versus using LINQ queries to pull the same data from C# objects. For starters, let's see a sample T-SQL query for pulling a list of baboons, sorted by aggressiveness:select * from tblAnimals where AnimalType = 'baboon' order by Aggressiveness
Now let's see the same thing in LINQ using objects already loaded in memory:
private dynamic CreateAnimal(string animalType, string name, int aggressiveness)
{
dynamic animal = new ExpandoObject();
animal.AnimalType = animalType;
animal.Name = name;
animal.Aggressiveness = aggressiveness;
return animal;
}
private List<dynamic> PopulateAnimalsList()
{
var animals = new List<dynamic>();
animals.Add(CreateAnimal("lion", "Simba", 10));
animals.Add(CreateAnimal("baboon", "Bobby", 4));
animals.Add(CreateAnimal("baboon", "Bill", 1));
animals.Add(CreateAnimal("baboon", "Bjork", 7));
animals.Add(CreateAnimal("panda", "Pete", 2));
return animals;
}
protected void btnLinqOrdering_Click(object sender, EventArgs e)
{
var animals = PopulateAnimalsList();
var query = from animal in animals
where animal.AnimalType.Equals("baboon")
orderby animal.Aggressiveness
select animal;
foreach (var val in query)
Response.Write("<br />" + val.Name + " the " + val.AnimalType + " has aggro val of " + val.Aggressiveness);
}
The first 2 methods are just setup, putting a list of animals into memory for us to manipulate. The LINQ-ified portion of our code is the 3rd and final method btnLinqOrdering_Click which creates the following output:
Bill the baboon has aggro val of 1
Bobby the baboon has aggro val of 4
Bjork the baboon has aggro val of 7
The orderby clause, just under the filtering where clause, is the sauce which produces this little bit of magic. By default, just like T-SQL, the sort order is ascending as you can see in the above output. If you wanted to order descending, you would change the orderby line to "orderby animal.Aggressiveness descending".
(Hey, wondering about dynamic and ExpandoObject? Keep paying attention to my blog, it'll probably make an appearance in a month or so).
Grouping (group)
Let's say you wanted to return a list of the types of animals and the count of each type. This will involve grouping. As with the last section, we'll start things with a sample bit of T-SQL:select AnimalType, Count(1) as [NumAnimals] from tblAnimals group by AnimalType order by Aggressiveness
Here were are selecting a list of types of animals and the number of each type of animal. How would we accomplish the same thing in LINQ? Here's that bit o' sample code:
protected void btnLinqGrouping1_Click(object sender, EventArgs e)
{
var animals = PopulateAnimalsList();
var query = from animal in animals
group animal by animal.AnimalType into animalGroup
select animalGroup;
foreach (var val in query)
Response.Write("<br /> there are " + val.Count() + " " + val.Key + "ses");
}
This isn't the most grammatically correct output ever, but it's correct at least:
there are 1 lionses
there are 3 baboonses
there are 1 pandases
Notice how in our foreach loop where we display the output, we are accessing val.Count() and val.Key? This is because when you use the group clause in LINQ as denoted by group...by...into..., you actually create a list of lists. The outer list is a list of your groups which has a Key property that in this case AnimalType property (because it's the "by" in the group clause).
While we do get a nice count by group from the above LINQ, we can't tell which animals belong to each group. What could we do in our code to show the contents of the groups/inner lists? the query is already selecting them, so in this case it's just a matter of changing our output. Here's the updated code:
foreach (var val in query)
{
Response.Write("<br /> there are " + val.Count() + " " + val.Key + "ses");
foreach (var animal in val)
Response.Write("<br /> " + animal.Name + " the " + animal.AnimalType + " has aggro val of " + animal.Aggressiveness);
}
The output is pretty much what you'd expect:
there are 1 lionses
Simba the lion has aggro val of 10
there are 3 baboonses
Bobby the baboon has aggro val of 4
Bill the baboon has aggro val of 1
Bjork the baboon has aggro val of 7
there are 1 pandases
Pete the panda has aggro val of 2
As you can see, I didn't lie to you earlier when I said the result of a group'd LINQ statement is a list of lists. The above code loops through the outer and inner lists, producing a combined output of summary and detail data. Spifferiferous!
Joining (join), as well as Projections
Joining lets you combine 2 source data sets into a single output data set. Let's pretend for this example that we want to see a list of which animals are in which shows. The shows are stored in a different table for T-SQL and in a different list for LINQ. Here's what the query might look like in T-SQL:select tblShows.Name as [ShowName], tblAnimals.Name as [AnimalName] from tblShows join tblAnimals on tblShows.AnimalName = tblAnimals.Name
This is pretty simplified as it assumes that the primary key of the Animal (unique property) is it's name, and it assumes only a single animal can be in any show, but hey you gotta lose realism for simplicity sometimes.
Now let's see what that might look like in LINQ:
private dynamic CreateShow(string showName, string animalName)
{
dynamic show = new ExpandoObject();
show.Name = showName;
show.AnimalName = animalName;
return show;
}
private ListPopulateShows()
{
var shows = new List<dynamic>();
shows.Add(CreateShow("Early", "Simba"));
shows.Add(CreateShow("Lunch", "Bjork"));
shows.Add(CreateShow("Evening", "Pete"));
return shows;
}
protected void btnLinqJoin_Click(object sender, EventArgs e)
{
var animals = PopulateAnimalsList();
var shows = PopulateShows();
var query = from animal in animals
join show in shows on animal.Name equals show.AnimalName
select new { ShowName = show.Name, AnimalName = animal.Name };
foreach (var val in query)
Response.Write("<br />the " + val.ShowName + " show has " + val.AnimalName);
}
As in our first example, the first 2 methods are just setup. They create our lovely new list of shows. The 3rd and awesomerest method is our little bit o special LINQ. You can see we've joined the list "animals" to the list "shows", via the join clause. Notice the weird new "select new..." syntax? This is what's known as a projection. You can see the resource link at the bottom of this post for a full definition, but basically in this query our output is a list of a newly defined type of object that has 2 properties, ShowName and AnimalName. It's a pretty cool concept that you can just us a dynamic object like this for your LINQ output, it makes things very flexible. Other than that, we have what you'll recognize by now as some pretty standard output:
the Early show has Simba
the Lunch show has Bjork
the Evening show has Pete
