Defining a Left Join with LINQ Queries (
Page 3 of 4 )
Listing 2 contains the Main function that reads song information exported from my iPod as a comma-delimited text file. The first twenty-five lines reads the Musc.csv file and initializes the Band and Music objects as appropriate. Again, this is pretty typical code except for the Func generic delegate. Func permits assigning a Lambda Expression to a member variable and in turn that variable can be used just like a function. In the example Split accepts a string and returns an array of strings. The Left Join is defined after Tito Puente is added to the list of bands.
Listing 2: Boilerplate code to initialize objects from a comma-delimited text file and a LINQ query the demonstrates a left join.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
namespace LeftJoin
{
class Program
{
static void Main(string[] args)
{
const string file = "..\\..\\..\\..\\Data\\Music.csv";
if (!File.Exists(file))
throw new FileNotFoundException("File not found", file); string[] lines = File.ReadAllLines(file);
// sanity check
//Array.ForEach(lines, line => Console.WriteLine(line)); Func<string, string[]> Split = str => str.Split(
new char[] { ',' },
StringSplitOptions.RemoveEmptyEntries); List<Band> bands = new List<Band>();
List<Music> music = new List<Music>(); for (int i = 1; i < lines.Length; i++)
{
string[] fields = Split(lines[i]);
string name = fields[0];
string artist = fields[1];
string composer = fields[2];
string album = fields[3];
string genre = fields[5]; Band band = bands.Add(artist, genre);
music.Add(band.ID, name, composer, album);
} bands.Add("Tito Puente", "Mambo"); // inner join
var group = from band in bands
join song in music on band.ID equals song.BandID
into catalog
from cat in catalog.DefaultIfEmpty(new Music()) where band.Artist == "Tito Puente"
orderby band.Artist
select new { Band = band.Artist, Song = cat.Name };
Array.ForEach(group.ToArray(), g => Console.WriteLine(g));
Console.ReadLine();
}
}
//... the code from listing 1
The LINQ left join starts with var group. The clause from band in bands defines the range variable band which is like the iterator variable in a for loop. Bands is joined to Musc using an equijoin on Band.ID and Music.BandID. A grouping is created with the into catalog clause. The clause from cat in catalog.DefaultIfEmpty makes this a left join instead of a group join.
ADVERTISEMENT
The range variable cat defines each of the elements from music. The predicate catalog.DefaultIfEmpty(new Music()) means that if no Music object exists for a specific band then create an empty one. (We could also create an initialized object here too.) The where-clause intentionally selects Tito Puente because the code shows that Puente was added manually (right before the LINQ query and I hadn’t copied my Tito Puente CDs to my iPod yet). If this were a left join then the result groupwould be empty. With a let join we get Tito Puente just without any songs.
The query is completed with a sort on Artist and a projection. The projection is a new anonymous type derived from Band.Artist and cat.Name, the song name. Finally an Array.ForEach and a Lambda Expression are used to write the result set to the console.
