Joining Tutorial Using Linq Query

This is the example of console application:-

The following example compares the results of an inner join, a group join, and a left outer join on the same data sources by using the same matching keys. Some extra code is added to these examples to clarify the results in the console display.

C#

class JoinDemonstration
    {
        #region Data

        class Product
        {
            public string Name { get; set; }
            public int CategoryID { get; set; }
        }

        class Category
        {
            public string Name { get; set; }
            public int ID { get; set; }
        }

        // Specify the first data source.
        List<Category> categories = new List<Category>()
        { 
            new Category(){Name="Beverages", ID=001},
            new Category(){ Name="Condiments", ID=002},
            new Category(){ Name="Vegetables", ID=003},
            new Category() {  Name="Grains", ID=004},
            new Category() {  Name="Fruit", ID=005}            
        };

        // Specify the second data source.
        List<Product> products = new List<Product>()
       {
          new Product{Name="Cola",  CategoryID=001},
          new Product{Name="Tea",  CategoryID=001},
          new Product{Name="Mustard", CategoryID=002},
          new Product{Name="Pickles", CategoryID=002},
          new Product{Name="Carrots", CategoryID=003},
          new Product{Name="Bok Choy", CategoryID=003},
          new Product{Name="Peaches", CategoryID=005},
          new Product{Name="Melons", CategoryID=005},
        };
        #endregion


        static void Main(string[] args)
        {
            JoinDemonstration app = new JoinDemonstration();

            app.InnerJoin();
            app.GroupJoin();
            app.GroupInnerJoin();
            app.GroupJoin3();
            app.LeftOuterJoin();
            app.LeftOuterJoin2();

            // Keep the console window open in debug mode.
            Console.WriteLine("Press any key to exit.");
            Console.ReadKey();
        }

        void InnerJoin()
        {
            // Create the query that selects 
            // a property from each element.
            var innerJoinQuery =
               from category in categories
               join prod in products on category.ID equals prod.CategoryID
               select new { Category = category.ID, Product = prod.Name };

            Console.WriteLine("InnerJoin:");
            // Execute the query. Access results 
            // with a simple foreach statement.
            foreach (var item in innerJoinQuery)
            {
                Console.WriteLine("{0,-10}{1}", item.Product, item.Category);
            }
            Console.WriteLine("InnerJoin: {0} items in 1 group.", innerJoinQuery.Count());
            Console.WriteLine(System.Environment.NewLine);

        }

        void GroupJoin()
        {
            // This is a demonstration query to show the output
            // of a "raw" group join. A more typical group join
            // is shown in the GroupInnerJoin method.
            var groupJoinQuery =
               from category in categories
               join prod in products on category.ID equals prod.CategoryID into prodGroup
               select prodGroup;

            // Store the count of total items (for demonstration only).
            int totalItems = 0;

            Console.WriteLine("Simple GroupJoin:");

            // A nested foreach statement is required to access group items.
            foreach (var prodGrouping in groupJoinQuery)
            {
                Console.WriteLine("Group:");
                foreach (var item in prodGrouping)
                {
                    totalItems++;
                    Console.WriteLine("   {0,-10}{1}", item.Name, item.CategoryID);
                }
            }
            Console.WriteLine("Unshaped GroupJoin: {0} items in {1} unnamed groups", totalItems, groupJoinQuery.Count());
            Console.WriteLine(System.Environment.NewLine);
        }

        void GroupInnerJoin()
        {
            var groupJoinQuery2 =
                from category in categories
                orderby category.ID
                join prod in products on category.ID equals prod.CategoryID into prodGroup
                select new
                {
                    Category = category.Name,
                    Products = from prod2 in prodGroup
                               orderby prod2.Name
                               select prod2
                };

            //Console.WriteLine("GroupInnerJoin:");
            int totalItems = 0;

            Console.WriteLine("GroupInnerJoin:");
            foreach (var productGroup in groupJoinQuery2)
            {
                Console.WriteLine(productGroup.Category);
                foreach (var prodItem in productGroup.Products)
                {
                    totalItems++;
                    Console.WriteLine("  {0,-10} {1}", prodItem.Name, prodItem.CategoryID);
                }
            }
            Console.WriteLine("GroupInnerJoin: {0} items in {1} named groups", totalItems, groupJoinQuery2.Count());
            Console.WriteLine(System.Environment.NewLine);
        }

        void GroupJoin3()
        {

            var groupJoinQuery3 =
                from category in categories
                join product in products on category.ID equals product.CategoryID into prodGroup
                from prod in prodGroup
                orderby prod.CategoryID
                select new { Category = prod.CategoryID, ProductName = prod.Name };

            //Console.WriteLine("GroupInnerJoin:");
            int totalItems = 0;

            Console.WriteLine("GroupJoin3:");
            foreach (var item in groupJoinQuery3)
            {
                totalItems++;
                Console.WriteLine("   {0}:{1}", item.ProductName, item.Category);
            }

            Console.WriteLine("GroupJoin3: {0} items in 1 group", totalItems, groupJoinQuery3.Count());
            Console.WriteLine(System.Environment.NewLine);
        }

        void LeftOuterJoin()
        {
            // Create the query.
            var leftOuterQuery =
               from category in categories
               join prod in products on category.ID equals prod.CategoryID into prodGroup
               select prodGroup.DefaultIfEmpty(new Product() { Name = "Nothing!", CategoryID = category.ID });

            // Store the count of total items (for demonstration only).
            int totalItems = 0;

            Console.WriteLine("Left Outer Join:");

            // A nested foreach statement  is required to access group items
            foreach (var prodGrouping in leftOuterQuery)
            {
                Console.WriteLine("Group:", prodGrouping.Count());
                foreach (var item in prodGrouping)
                {
                    totalItems++;
                    Console.WriteLine("  {0,-10}{1}", item.Name, item.CategoryID);
                }
            }
            Console.WriteLine("LeftOuterJoin: {0} items in {1} groups", totalItems, leftOuterQuery.Count());
            Console.WriteLine(System.Environment.NewLine);
        }

        void LeftOuterJoin2()
        {
            // Create the query.
            var leftOuterQuery2 =
               from category in categories
               join prod in products on category.ID equals prod.CategoryID into prodGroup
               from item in prodGroup.DefaultIfEmpty()
               select new { Name = item == null ? "Nothing!" : item.Name, CategoryID = category.ID };

            Console.WriteLine("LeftOuterJoin2: {0} items in 1 group", leftOuterQuery2.Count());
            // Store the count of total items
            int totalItems = 0;

            Console.WriteLine("Left Outer Join 2:");

            // Groups have been flattened.
            foreach (var item in leftOuterQuery2)
            {
                totalItems++;
                Console.WriteLine("{0,-10}{1}", item.Name, item.CategoryID);
            }
            Console.WriteLine("LeftOuterJoin2: {0} items in 1 group", totalItems);

 output:-

/*Output:

InnerJoin:
Cola      1
Tea       1
Mustard   2
Pickles   2
Carrots   3
Bok Choy  3
Peaches   5
Melons    5
InnerJoin: 8 items in 1 group.


Unshaped GroupJoin:
Group:
   Cola      1
   Tea       1
Group:
   Mustard   2
   Pickles   2
Group:
   Carrots   3
   Bok Choy  3
Group:
Group:
   Peaches   5
   Melons    5
Unshaped GroupJoin: 8 items in 5 unnamed groups


GroupInnerJoin:
Beverages
  Cola       1
  Tea        1
Condiments
  Mustard    2
  Pickles    2
Vegetables
  Bok Choy   3
  Carrots    3
Grains
Fruit
  Melons     5
  Peaches    5
GroupInnerJoin: 8 items in 5 named groups


GroupJoin3:
   Cola:1
   Tea:1
   Mustard:2
   Pickles:2
   Carrots:3
   Bok Choy:3
   Peaches:5
   Melons:5
GroupJoin3: 8 items in 1 group


Left Outer Join:
Group:
  Cola      1
  Tea       1
Group:
  Mustard   2
  Pickles   2
Group:
  Carrots   3
  Bok Choy  3
Group:
  Nothing!  4
Group:
  Peaches   5
  Melons    5
LeftOuterJoin: 9 items in 5 groups


LeftOuterJoin2: 9 items in 1 group
Left Outer Join 2:
Cola      1
Tea       1
Mustard   2
Pickles   2
Carrots   3
Bok Choy  3
Nothing!  4
Peaches   5
Melons    5
LeftOuterJoin2: 9 items in 1 group
Press any key to exit.
*/

This is the code behind of my .aspx page:-(copy this code and paste in your aspx page code behind and observe the output):-
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Alljoins : System.Web.UI.Page
{
    public class Product
    {
        public string Name { get; set; }
        public int CategoryID { get; set; }
    }

    public class Category
    {
        public string Name { get; set; }
        public int ID { get; set; }
    }
    protected void Page_Load(object sender, EventArgs e)
    {
       InnerJoin();
        GroupJoin();
        GroupInnerJoin();
      GroupJoin3();
       LeftOuterJoin();
        LeftOuterJoin2(); 
    }
    #region Data



    // Specify the first data source.
    List<Category> categories = new List<Category>()
        {
            new Category(){Name="Beverages", ID=001},
            new Category(){ Name="Condiments", ID=002},
            new Category(){ Name="Vegetables", ID=003},
            new Category() {  Name="Grains", ID=004},
            new Category() {  Name="Fruit", ID=005}           
        };

    // Specify the second data source.
    List<Product> products = new List<Product>()
       {
          new Product{Name="Cola",  CategoryID=001},
          new Product{Name="Tea",  CategoryID=001},
          new Product{Name="Mustard", CategoryID=002},
          new Product{Name="Pickles", CategoryID=002},
          new Product{Name="Carrots", CategoryID=003},
          new Product{Name="Bok Choy", CategoryID=003},
          new Product{Name="Peaches", CategoryID=005},
          new Product{Name="Melons", CategoryID=005},
        };
    #endregion
    public void InnerJoin()
    {
        // Create the query that selects
        // a property from each element.
        var innerJoinQuery =
           from category in categories
           join prod in products on category.ID equals prod.CategoryID
           select new { Category = category.ID, Product = prod.Name };

        Response.Write("<br>" + "InnerJoin:");
        // Execute the query. Access results
        // with a simple foreach statement.
        foreach (var item in innerJoinQuery)
        {
            Response.Write("<br>" + item.Product + "  " + item.Category);
        }
        Response.Write("<br>" + "InnerJoin:" + innerJoinQuery.Count() + " items in 1 group." + " <br>");
     

    }

    public void GroupJoin()
    {
        // This is a demonstration query to show the output
        // of a "raw" group join. A more typical group join
        // is shown in the GroupInnerJoin method.
        var groupJoinQuery =
           from category in categories
           join prod in products on category.ID equals prod.CategoryID into prodGroup
           select prodGroup;

        // Store the count of total items (for demonstration only).
        int totalItems = 0;

        Response.Write("<br>" + "Simple GroupJoin:");

        // A nested foreach statement is required to access group items.
        foreach (var prodGrouping in groupJoinQuery)
        {
            Response.Write("<br>" + "Group:");
            foreach (var item in prodGrouping)
            {
                totalItems++;
                Response.Write("<br>" + item.Name + " " + item.CategoryID);
            }
        }
        Response.Write("<br>" + "Unshaped GroupJoin:" + totalItems + " items in unnamed groups" + "  " + groupJoinQuery.Count());
    
    }

    public void GroupInnerJoin()
    {
        var groupJoinQuery2 =
            from category in categories
            orderby category.ID
            join prod in products on category.ID equals prod.CategoryID into prodGroup
            select new
            {
                Category = category.Name,
                Products = from prod2 in prodGroup
                           orderby prod2.Name
                           select prod2
            };

      
        int totalItems = 0;

        Response.Write("<br>" + "GroupInnerJoin:");
        foreach (var productGroup in groupJoinQuery2)
        {
            Response.Write("<br>" + productGroup.Category);
            foreach (var prodItem in productGroup.Products)
            {
                totalItems++;
                Response.Write("<br>" + prodItem.Name + " " + prodItem.CategoryID);
            }
        }
        Response.Write("<br>" + "GroupInnerJoin:" + totalItems + "  items in named groups" + " " + groupJoinQuery2.Count());
      
    }

    public void GroupJoin3()
    {

        var groupJoinQuery3 =
            from category in categories
            join product in products on category.ID equals product.CategoryID into prodGroup
            from prod in prodGroup
            orderby prod.CategoryID
            select new { Category = prod.CategoryID, ProductName = prod.Name };

       
        int totalItems = 0;

        Response.Write("<br>" + "GroupJoin3:");
        foreach (var item in groupJoinQuery3)
        {
            totalItems++;
            Response.Write("<br>" + item.ProductName + " " + item.Category);
        }

        Response.Write("<br>" + "GroupJoin3:" + totalItems + "  items in 1 group" + "  " + groupJoinQuery3.Count());
       
    }

    public void LeftOuterJoin()
    {
        // Create the query.
        var leftOuterQuery =
           from category in categories
           join prod in products on category.ID equals prod.CategoryID into prodGroup
           select prodGroup.DefaultIfEmpty(new Product() { Name = "Nothing!", CategoryID = category.ID });

       
        int totalItems = 0;

        Response.Write("<br>" + "Left Outer Join:");

        // A nested foreach statement  is required to access group items
        foreach (var prodGrouping in leftOuterQuery)
        {
            Response.Write("<br>" + "Group:" + " " + prodGrouping.Count());
            foreach (var item in prodGrouping)
            {
                totalItems++;
                Response.Write("<br>" + item.Name + " " + item.CategoryID);
            }
        }
        Response.Write("<br>" + "LeftOuterJoin:" + totalItems + "  items in groups" + "  " + leftOuterQuery.Count());
       
    }

    public void LeftOuterJoin2()
    {
        // Create the query.
        var leftOuterQuery2 =
           from category in categories
           join prod in products on category.ID equals prod.CategoryID into prodGroup
           from item in prodGroup.DefaultIfEmpty()
           select new { Name = item == null ? "Nothing!" : item.Name, CategoryID = category.ID };

        Response.Write("<br>" + "LeftOuterJoin2:" + leftOuterQuery2.Count()+" items in 1 group"  );
        // Store the count of total items
        int totalItems = 0;

        Response.Write("<br>" + "Left Outer Join 2:");

        // Groups have been flattened.
        foreach (var item in leftOuterQuery2)
        {
            totalItems++;
            Response.Write("<br>" + item.Name + " " + item.CategoryID);
        }
        Response.Write("<br>" + "LeftOuterJoin2:" + totalItems+"  items in 1 group" );

    }

}
output:-
InnerJoin:
Cola 1
Tea 1
Mustard 2
Pickles 2
Carrots 3
Bok Choy 3
Peaches 5
Melons 5
InnerJoin:8 items in 1 group.

Simple GroupJoin:
Group:
Cola 1
Tea 1
Group:
Mustard 2
Pickles 2
Group:
Carrots 3
Bok Choy 3
Group:
Group:
Peaches 5
Melons 5
Unshaped GroupJoin:8 items in unnamed groups 5
GroupInnerJoin:
Beverages
Cola 1
Tea 1
Condiments
Mustard 2
Pickles 2
Vegetables
Bok Choy 3
Carrots 3
Grains
Fruit
Melons 5
Peaches 5
GroupInnerJoin:8 items in named groups 5
GroupJoin3:
Cola 1
Tea 1
Mustard 2
Pickles 2
Carrots 3
Bok Choy 3
Peaches 5
Melons 5
GroupJoin3:8 items in 1 group 8
Left Outer Join:
Group: 2
Cola 1
Tea 1
Group: 2
Mustard 2
Pickles 2
Group: 2
Carrots 3
Bok Choy 3
Group: 1
Nothing! 4
Group: 2
Peaches 5
Melons 5
LeftOuterJoin:9 items in groups 5
LeftOuterJoin2:9 items in 1 group
Left Outer Join 2:
Cola 1
Tea 1
Mustard 2
Pickles 2
Carrots 3
Bok Choy 3
Nothing! 4
Peaches 5
Melons 5
LeftOuterJoin2:9 items in 1 group

How to: Perform Left Outer Joins (Console application)

 

class Person
        {
            public string FirstName { get; set; }
            public string LastName { get; set; }
        }

        class Pet
        {
            public string Name { get; set; }
            public Person Owner { get; set; }
        }

        public static void LeftOuterJoinExample()
        {
            Person magnus = new Person { FirstName = "Magnus", LastName = "Hedlund" };
            Person terry = new Person { FirstName = "Terry", LastName = "Adams" };
            Person charlotte = new Person { FirstName = "Charlotte", LastName = "Weiss" };
            Person arlene = new Person { FirstName = "Arlene", LastName = "Huff" };

            Pet barley = new Pet { Name = "Barley", Owner = terry };
            Pet boots = new Pet { Name = "Boots", Owner = terry };
            Pet whiskers = new Pet { Name = "Whiskers", Owner = charlotte };
            Pet bluemoon = new Pet { Name = "Blue Moon", Owner = terry };
            Pet daisy = new Pet { Name = "Daisy", Owner = magnus };

            // Create two lists.
            List<Person> people = new List<Person> { magnus, terry, charlotte, arlene };
            List<Pet> pets = new List<Pet> { barley, boots, whiskers, bluemoon, daisy };

            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) };

            foreach (var v in query)
            {
                Console.WriteLine("{0,-15}{1}", v.FirstName + ":", v.PetName);
            }
        }

        // This code produces the following output:
        //
        // Magnus:         Daisy
        // Terry:          Barley
        // Terry:          Boots
        // Terry:          Blue Moon
        // Charlotte:      Whiskers
        // Arlene:

Simple Key Join Example

The following example creates two collections that contain objects of two user-defined types, Person and Pet. The query uses the join clause in C# to match Person objects withPet objects whose Owner is that Person. The select clause in C# defines how the resulting objects will look. In this example the resulting objects are anonymous types that consist of the owner's first name and the pet's name.

C#

class Person
        {
            public string FirstName { get; set; }
            public string LastName { get; set; }
        }

        class Pet
        {
            public string Name { get; set; }
            public Person Owner { get; set; }
        }

        /// <summary>
        /// Simple inner join.
        /// </summary>
        public static void InnerJoinExample()
        {
            Person magnus = new Person { FirstName = "Magnus", LastName = "Hedlund" };
            Person terry = new Person { FirstName = "Terry", LastName = "Adams" };
            Person charlotte = new Person { FirstName = "Charlotte", LastName = "Weiss" };
            Person arlene = new Person { FirstName = "Arlene", LastName = "Huff" };
            Person rui = new Person { FirstName = "Rui", LastName = "Raposo" };

            Pet barley = new Pet { Name = "Barley", Owner = terry };
            Pet boots = new Pet { Name = "Boots", Owner = terry };
            Pet whiskers = new Pet { Name = "Whiskers", Owner = charlotte };
            Pet bluemoon = new Pet { Name = "Blue Moon", Owner = rui };
            Pet daisy = new Pet { Name = "Daisy", Owner = magnus };

            // Create two lists.
            List<Person> people = new List<Person> { magnus, terry, charlotte, arlene, rui };
            List<Pet> pets = new List<Pet> { barley, boots, whiskers, bluemoon, daisy };

            // Create a collection of person-pet pairs. Each element in the collection
            // is an anonymous type containing both the person's name and their pet's name.
            var query = from person in people
                        join pet in pets on person equals pet.Owner
                        select new { OwnerName = person.FirstName, PetName = pet.Name };

            foreach (var ownerAndPet in query)
            {
                Console.WriteLine("\"{0}\" is owned by {1}", ownerAndPet.PetName, ownerAndPet.OwnerName);
            }
        }

        // This code produces the following output:
        //
        // "Daisy" is owned by Magnus
        // "Barley" is owned by Terry
        // "Boots" is owned by Terry
        // "Whiskers" is owned by Charlotte
        // "Blue Moon" is owned by Rui

Note that the Person object whose LastName is "Huff" does not appear in the result set because there is no Pet object that has Pet.Owner equal to that Person.

Composite Key Join Example

Instead of correlating elements based on just one property, you can use a composite key to compare elements based on multiple properties. To do this, specify the key selector function for each collection to return an anonymous type that consists of the properties you want to compare. If you label the properties, they must have the same label in each key's anonymous type. The properties must also appear in the same order.

The following example uses a list of Employee objects and a list of Student objects to determine which employees are also students. Both of these types have a FirstName and a LastName property of type String. The functions that create the join keys from each list's elements return an anonymous type that consists of the FirstName and LastNameproperties of each element. The join operation compares these composite keys for equality and returns pairs of objects from each list where both the first name and the last name match.

C#

class Employee
        {
            public string FirstName { get; set; }
            public string LastName { get; set; }
            public int EmployeeID { get; set; }
        }

        class Student
        {
            public string FirstName { get; set; }
            public string LastName { get; set; }
            public int StudentID { get; set; }
        }

        /// <summary>
        /// Performs a join operation using a composite key.
        /// </summary>
        public static void CompositeKeyJoinExample()
        {
            // Create a list of employees.
            List<Employee> employees = new List<Employee> {
                new Employee { FirstName = "Terry", LastName = "Adams", EmployeeID = 522459 },
                 new Employee { FirstName = "Charlotte", LastName = "Weiss", EmployeeID = 204467 },
                 new Employee { FirstName = "Magnus", LastName = "Hedland", EmployeeID = 866200 },
                 new Employee { FirstName = "Vernette", LastName = "Price", EmployeeID = 437139 } };

            // Create a list of students.
            List<Student> students = new List<Student> {
                new Student { FirstName = "Vernette", LastName = "Price", StudentID = 9562 },
                new Student { FirstName = "Terry", LastName = "Earls", StudentID = 9870 },
                new Student { FirstName = "Terry", LastName = "Adams", StudentID = 9913 } };

            // Join the two data sources based on a composite key consisting of first and last name,
            // to determine which employees are also students.
            IEnumerable<string> query = from employee in employees
                                        join student in students
                                        on new { employee.FirstName, employee.LastName }
                                        equals new { student.FirstName, student.LastName }
                                        select employee.FirstName + " " + employee.LastName;

            Console.WriteLine("The following people are both employees and students:");
            foreach (string name in query)
                Console.WriteLine(name);
        }

        // This code produces the following output:
        //
        // The following people are both employees and students:
        // Terry Adams
        // Vernette Price

Multiple Join Example

Any number of join operations can be appended to each other to perform a multiple join. Each join clause in C# correlates a specified data source with the results of the previous join.

The following example creates three collections: a list of Person objects, a list of Cat objects, and a list of Dog objects.

The first join clause in C# matches people and cats based on a Person object matching Cat.Owner. It returns a sequence of anonymous types that contain the Person object andCat.Name.

The second join clause in C# correlates the anonymous types returned by the first join with Dog objects in the supplied list of dogs, based on a composite key that consists of the Owner property of type Person, and the first letter of the animal's name. It returns a sequence of anonymous types that contain the Cat.Name and Dog.Name properties from each matching pair. Because this is an inner join, only those objects from the first data source that have a match in the second data source are returned.

C#

class Person
        {
            public string FirstName { get; set; }
            public string LastName { get; set; }
        }

        class Pet
        {
            public string Name { get; set; }
            public Person Owner { get; set; }
        }

        class Cat : Pet
        { }

        class Dog : Pet
        { }

        public static void MultipleJoinExample()
        {
            Person magnus = new Person { FirstName = "Magnus", LastName = "Hedlund" };
            Person terry = new Person { FirstName = "Terry", LastName = "Adams" };
            Person charlotte = new Person { FirstName = "Charlotte", LastName = "Weiss" };
            Person arlene = new Person { FirstName = "Arlene", LastName = "Huff" };
            Person rui = new Person { FirstName = "Rui", LastName = "Raposo" };
            Person phyllis = new Person { FirstName = "Phyllis", LastName = "Harris" };

            Cat barley = new Cat { Name = "Barley", Owner = terry };
            Cat boots = new Cat { Name = "Boots", Owner = terry };
            Cat whiskers = new Cat { Name = "Whiskers", Owner = charlotte };
            Cat bluemoon = new Cat { Name = "Blue Moon", Owner = rui };
            Cat daisy = new Cat { Name = "Daisy", Owner = magnus };

            Dog fourwheeldrive = new Dog { Name = "Four Wheel Drive", Owner = phyllis };
            Dog duke = new Dog { Name = "Duke", Owner = magnus };
            Dog denim = new Dog { Name = "Denim", Owner = terry };
            Dog wiley = new Dog { Name = "Wiley", Owner = charlotte };
            Dog snoopy = new Dog { Name = "Snoopy", Owner = rui };
            Dog snickers = new Dog { Name = "Snickers", Owner = arlene };

            // Create three lists.
            List<Person> people =
                new List<Person> { magnus, terry, charlotte, arlene, rui, phyllis };
            List<Cat> cats =
                new List<Cat> { barley, boots, whiskers, bluemoon, daisy };
            List<Dog> dogs =
                new List<Dog> { fourwheeldrive, duke, denim, wiley, snoopy, snickers };

            // The first join matches Person and Cat.Owner from the list of people and
            // cats, based on a common Person. The second join matches dogs whose names start
            // with the same letter as the cats that have the same owner.
            var query = from person in people
                        join cat in cats on person equals cat.Owner
                        join dog in dogs on 
                        new { Owner = person, Letter = cat.Name.Substring(0, 1) }
                        equals new { dog.Owner, Letter = dog.Name.Substring(0, 1) }
                        select new { CatName = cat.Name, DogName = dog.Name };

            foreach (var obj in query)
            {
                Console.WriteLine(
                    "The cat \"{0}\" shares a house, and the first letter of their name, with \"{1}\".", 
                    obj.CatName, obj.DogName);
            }
        }

        // This code produces the following output:
        //
        // The cat "Daisy" shares a house, and the first letter of their name, with "Duke".
        // The cat "Whiskers" shares a house, and the first letter of their name, with "Wiley".

Inner Join by using Grouped Join Example

The following example shows you how to implement an inner join by using a group join.

In query1, the list of Person objects is group-joined to the list of Pet objects based on the Person matching the Pet.Owner property. The group join creates a collection of intermediate groups, where each group consists of a Person object and a sequence of matching Pet objects.

By adding a second from clause to the query, this sequence of sequences is combined (or flattened) into one longer sequence. The type of the elements of the final sequence is specified by the select clause. In this example, that type is an anonymous type that consists of the Person.FirstName and Pet.Name properties for each matching pair.

The result of query1 is equivalent to the result set that would have been obtained by using the join clause without the into clause to perform an inner join. The query2 variable demonstrates this equivalent query.

C#

class Person
        {
            public string FirstName { get; set; }
            public string LastName { get; set; }
        }

        class Pet
        {
            public string Name { get; set; }
            public Person Owner { get; set; }
        }

        /// <summary>
        /// Performs an inner join by using GroupJoin().
        /// </summary>
        public static void InnerGroupJoinExample()
        {
            Person magnus = new Person { FirstName = "Magnus", LastName = "Hedlund" };
            Person terry = new Person { FirstName = "Terry", LastName = "Adams" };
            Person charlotte = new Person { FirstName = "Charlotte", LastName = "Weiss" };
            Person arlene = new Person { FirstName = "Arlene", LastName = "Huff" };

            Pet barley = new Pet { Name = "Barley", Owner = terry };
            Pet boots = new Pet { Name = "Boots", Owner = terry };
            Pet whiskers = new Pet { Name = "Whiskers", Owner = charlotte };
            Pet bluemoon = new Pet { Name = "Blue Moon", Owner = terry };
            Pet daisy = new Pet { Name = "Daisy", Owner = magnus };

            // Create two lists.
            List<Person> people = new List<Person> { magnus, terry, charlotte, arlene };
            List<Pet> pets = new List<Pet> { barley, boots, whiskers, bluemoon, daisy };

            var query1 = from person in people
                         join pet in pets on person equals pet.Owner into gj
                         from subpet in gj
                         select new { OwnerName = person.FirstName, PetName = subpet.Name };

            Console.WriteLine("Inner join using GroupJoin():");
            foreach (var v in query1)
            {
                Console.WriteLine("{0} - {1}", v.OwnerName, v.PetName);
            }

            var query2 = from person in people
                         join pet in pets on person equals pet.Owner
                         select new { OwnerName = person.FirstName, PetName = pet.Name };

            Console.WriteLine("\nThe equivalent operation using Join():");
            foreach (var v in query2)
                Console.WriteLine("{0} - {1}", v.OwnerName, v.PetName);
        }

        // This code produces the following output:
        //
        // Inner join using GroupJoin():
        // Magnus - Daisy
        // Terry - Barley
        // Terry - Boots
        // Terry - Blue Moon
        // Charlotte - Whiskers
        //
        // The equivalent operation using Join():
        // Magnus - Daisy
        // Terry - Barley
        // Terry - Boots
        // Terry - Blue Moon
        // Charlotte - Whiskers

Compiling the Code

---

• Create a new Console Application project in Visual Studio.
• Add a reference to System.Core.dll if it is not already referenced.
• Include the System.Linq namespace.
• Copy and paste the code from the example into the program.cs file, below the Main method. Add a line of code to the Main method to call the method you pasted in.
• Run the program.

 LINQ to SQL join 3 tables and select multiple columns:-

 

You can try below code

if you would like to have seperate record for each timesheet entry of same student, then you must include p.TimeSheetId

in the begining of group by parameter

 var query1 = from stud in Students join ts in TimeSheets on stud.StudentId equals ts.StudentId 
                         into studts from p in studts join o in TimeRecords on p.TimeSheetId equals o.TimeSheetId 
                         where p.IsActive && o.IsValid 
                         group o by new {o.CreationDate.Month , o.CreationDate.Year,stud.FirstName, stud.LastName }
                         into last
                         orderby last.Key.Month, last.Key.Year     
                         select new{    name= last.Key.FirstName+ ' ' + last.Key.LastName +" has " +
                                        last.Sum(m=>m.BonusHour ).ToString()  + "Hr for " +
                                        Numerictomonth(last.Key.Month)   + ' '+ last.Key.Year.ToString()
                                    };



  private String Numerictomonth(int month)
        {
           return  System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(month);      
        }

 

Left Join on multiple tables in Linq to SQL

Wow really? A blog post on Linq to SQL? Yep...I still use it. It's exactly what I need...a light-weight ORM. Anyways, here's an example of a LEFT OUTER JOIN on multiple tables in Linq to SQL:

view sourceprint?

01.from expense in expenseDataContext.ExpenseDtos

02.where expense.Id == expenseId //some expense id that was passed in

03.from category

04.// left join on categories table if exists

05.in expenseDataContext.CategoryDtos

06..Where(c => c.Id == expense.CategoryId)

07..DefaultIfEmpty()

08.// left join on expense type table if exists

09.from expenseType

10.in expenseDataContext.ExpenseTypeDtos

11..Where(e => e.Id == expense.ExpenseTypeId)

12..DefaultIfEmpty()

13.// left join on currency table if exists

14.from currency

15.in expenseDataContext.CurrencyDtos

16..Where(c => c.CurrencyID == expense.FKCurrencyID)

17..DefaultIfEmpty()

18.select new

19.{

20.Expense = expense,

21.// category will be null if join doesn't exist

22.Category = category,

23.// expensetype will be null if join doesn't exist

24.ExpenseType = expenseType,

25.// currency will be null if join doesn't exist

26.Currency = currency 

27.}

 

How to JOIN two tables using LINQ to SQL:-

Here are the tables from my schema

Here is a basic SQL statement I could fire to retrieve my user videos.

1 2 3 4

select  * from    video v, user_videos uv where   v.vid = uv.vid and     uv.uid = 2

Here is how you would run the same query using .net’s LINQ to SQL.

01 02 03 04 05 06 07 08 09 10

// create DB connection var db = new DBShibashishCONN(); // run query List<video> uvids = (     from c in db.video     join o in db.user_videos     on c.vid equals o.vid     where o.uid == 2     select c ).ToList();

This query differs slightly from the screenshot below because I used it in a WCF Service.(ShibashishCONN as DBCONN)

The variable ShibashishCONN is my database connection that I established when mapping my DB.  If you are not familiar with how to set this up, use the Visual Studio’s “Add the ADO.NET Entity Data Model” wizard.  With your .net project open, right click your project, left click on “Add the ADO.NET Entity Data Model”.  This wizard will walk you through setting up everything you need to setup your DB model file ( edmx ), as well as setting up your database connection and saving it in web.config.