LINQ Examples

LINQ to SQL¶

Inner Join¶

var txns = from hdr in MyDataContext.Def.Headers
             join det in MyDataContext.Def.Detail
             on hdr.HeaderId equals det.HeaderId
             where hdr.CustomerId == customerId
             orderby hdr.OrderDate descending, det.LineNum
             select det;

Left Join¶

var query = from person in people
            join pet in pets on person equals pet.Owner into gj
            from subpet in gj.DefaultIfEmpty()
            select new { 
                person.FirstName,  
                PetName = (subpet == null ? String.Empty : subpet.Name)  
                };

return 
    from q in questions

    join mcq2 in multipleChoiceQuestions on q.Id equals mcq2.Id into mcq3
    from mcq in mcq3.DefaultIfEmpty()

    select new {
        . . .
    }

Left Join Filtering the Right Table¶

var a = from row in db.AccountRevenueTypes
          where row.AccountID == accountId
          select row;

var result = from g in db.RevenueTypes
             from p in a.Where(p => p.RevenueTypeID == g.RevenueTypeID).DefaultIfEmpty()
             select new
             {
                 RevenueType = g.RevenueTypeName,
                 Active = (p == null ? false : p.Allow),
                 . . .
             };

Grouping¶

var numberGroups =
    from n in numbers
    group n by n % 5 into g
    select new { Remainder = g.Key, Numbers = g };

Grouping by Multiple Columns¶

var numberGroups =
    from n in numbers
    group n by new {n.Value, n.Sign} into g
    select new { Remainder = g.Key, Numbers = g };

Aggregate¶

var categories =
    from p in products
    group p by p.Category into g
    select new { Category = g.Key, TotalUnitsInStock = g.Sum(p => p.UnitsInStock) };

Custom Aggregate¶

var q = from p in dc.Persons
        where p.LastName == "Jasinski"
        group p by p.HouseholdId into g
        select new Household { 
            HouseholdId = g.Key, 
            People = (from p2 in dc.Persons
                          where p2.HouseholdId == g.Key
                          select p2).PeopleInHousehold()
            };

. . .
public static class Extender
{
    public static string PeopleInHousehold(this IEnumerable<Person> people)
    {
        string result = "";

        foreach (Person p in people)
        {
            result += ", ";
            result += p.FirstName;
        }
        if (result.Length >= 2)
            result = result.Substring(2);

        return result;
    }
}

LINQ to XML¶

With Anonymous Types¶

string inputFile = @"C:\Data\Source Code\C# 2008\LinqToXmlTest\LinqToXmlTest.xml";
XDocument xdoc = XDocument.Load(inputFile);

var envs = from env in xdoc.Descendants("environment")
		   where (bool)env.Attribute("active")
		  select new { 
			  Name = env.Attribute("name").Value ,
			  Active = bool.Parse(env.Attribute("active").Value),
			  Server = env.Attribute("server").Value,
			  Trusted = bool.Parse(env.Attribute("trusted").Value)
		  };

foreach (var env in envs)
	Debug.Print(env.Name + ":" + env.Server);

With Non-anonymous Types¶

string inputFile = @"C:\Data\Source Code\C# 2008\LinqToXmlTest\LinqToXmlTest.xml";
XDocument xdoc = XDocument.Load(inputFile);

List<Environment> envs = (
			from env in xdoc.Descendants("environment")
			select new Environment { 
				Name = env.Attribute("name").Value ,
				Active = bool.Parse(env.Attribute("active").Value),
				Server = env.Attribute("server").Value,
				Trusted = bool.Parse(env.Attribute("trusted").Value)
			}).ToList<Environment>();	

foreach (Environment env in envs)
	Debug.Print(env.Name);

. . .

class Environment
{
    public string Name {get; set; }
    public bool Active { get; set; }
    public string Server { get; set; }
    public bool Trusted { get; set; }
}