@bobnoordam

Distinct, join and outer Join with linq to sql

Posted on | By bobnoordam

While stored procedures will beat linq most of the time if you want top notch performance, there is a distinct advantage to LINQ when it comes to readability of your code, and keeping things a little more central within the application. These samples make use of the NorthWind database, and a dbml file called NorthWindContext.dbml which has been created with the sqlmetal utility that comes with Visual Studio. in these samples they are loaded with the next two lines: 

// database connection
string database = Properties.Settings.Default.constring;
var dc = new NorthwindDataContext(database);

Standard join

This sample performs a standard join with linq, and returns a new anonymous set with objects that contain the full customer and order objects from the database. 

// standard join returning complete customer and order objects
var joinset = from tabcust in dc.Customers
              join taborders in dc.Orders on tabcust.CustomerID equals taborders.CustomerID
              select new { Customer = tabcust, Order = taborders };
foreach (var joinresult in joinset)
{
    Console.WriteLine(joinresult.Customer.CustomerID + ", " + joinresult.Order.OrderID);
}
Console.WriteLine(joinset.Count() + " results in set.");
Console.ReadLine();

Outer join

Outer join resturning complete customer and order objects, including null objects for orders if the customer has no matching orders. 

// outer join resturning complete customer and order objects, including null objects for orders if the customer
// has no matching orders.
var outerset = from tabcust in dc.Customers
               from taborder in dc.Orders.Where(order => tabcust.CustomerID == order.CustomerID).DefaultIfEmpty()
               select new { Customer = tabcust, Order = taborder };
int nullcount = 0;
foreach (var outerresult in outerset)
{
    if (outerresult.Order == null)
    {
        Console.WriteLine(outerresult.Customer.CustomerID + ", null");
        nullcount++;
    }
}
Console.WriteLine(nullcount + " results in set");
Console.ReadLine();

Summing a distinct list

Count amount of orders each customer has put in. Uses the distinct function to create a unique list of customers. 

// Count amount of orders each customer has put in
var customers = (from tabcust in dc.Customers select tabcust).Distinct();     // .Distinct is useless here since its pk, but left in as demo
foreach (var customer in customers)
{
    int ordercount = (from taborders in dc.Orders where taborders.CustomerID == customer.CustomerID select taborders).Count();
    Console.WriteLine(customer.CustomerID + ", " + ordercount);
}
Console.Re
Category: C#