Friday, 3 August 2012

LINQ Outer Joins

image
using System;
using System.Linq;

namespace LinqPlay
{
class Program
{
static void Main(string[] args)
{
// REQUIREMENT: Using linq show all beverages for all days
// including days where beverages have zero count

// Enum beverages: Coke, Fanta, Lilt, Sprite
// Date range: yesterday, today and tomorrow
// Stats data: 3 cokes, 1 sprite yesterday
// 1 sprite, 4 Fanta today

// SETUP THE COLLECTIONS OF DATA

string[] beverages = Enum.GetNames(typeof(Beverages));

DateTime yesterday = DateTime.Today.AddDays(-1);
DateTime today = DateTime.Today;
DateTime tomorrow = DateTime.Today.AddDays(1);

DateTime[] dates = new[] { yesterday, today, tomorrow };

var stats = new []
{
new ChartData() {Text = "Coke", Timestamp = yesterday, Value = 3},
new ChartData() {Text = "Sprite", Timestamp = yesterday, Value = 1},
new ChartData() {Text = "Sprite", Timestamp = today, Value = 1},
new ChartData() {Text = "Fanta", Timestamp = today, Value = 4}
};

// JOIN THE DATA

var qry = (from x in (from b in beverages
from d in dates
select new ChartData() {Text = b, Timestamp = d.Date})
join s in stats on new { x.Text, x.Timestamp} equals new { s.Text, s.Timestamp }
into temp1 from temp2 in temp1.DefaultIfEmpty()
select new ChartData()
{
Text = x.Text,
Timestamp = x.Timestamp,
Value = (temp2 == null ? 0 : temp2.Value)
})
.OrderBy(x => x.Timestamp).ThenBy(x => x.Text);

foreach (var chartData in qry)
{
Console.WriteLine(chartData.ToString());
}
}

enum Beverages
{
Coke,
Lilt,
Fanta,
Sprite
}

class ChartData
{
public string Text { get; set; }
public DateTime Timestamp { get; set; }
public int Value { get; set; }

public override string ToString()
{
return string.Format("{0}\t{1}\t{2}", Timestamp.ToString("dd/MM/yyyy"), Text, Value);
}
}
}
}

1 comment: