LINQ : Hierarchical Data

This post shows an example of hwo to handle hierarchical data in LINQ TO SQL.
The example has 3 tables and their hierarchies are as follows. Company has multiple Depts and each Dept can has multiple Emp reocrds. Tables are created in SQL Server and all tables have Primary Key and Foreign Key relationships as shown below.

Once SQL Tables are created, we can create a DBML (Database Markup Language) file in Visual Studio and generates the corresponding Entity Class for each table. After Entity Classes are generated, we can use those to select, insert, delete, update underlying SQL data. The following code snippet illustrates how to handle those hierarchical data in LINQ.

class Program
{
    static void Main(string[] args)
    {
        Program p = new Program();
        p.Insert();
        p.Select();
        p.Update();
        p.Delete();
    }

    public void Insert()
    {
        MyDBDataContext db = new MyDBDataContext();
        Company newComp = new Company { CompanyID = 1, Name = "TE System" };
        newComp.Depts.Add(new Dept
        {
            CompanyID = 1,
            DeptID = 100,
            Name = "Finance",
            Emps = { new Emp { CompanyID = 1, DeptID = 100, EmpID = 9, Name = "Tom", Phone="111" },
                    new Emp { CompanyID = 1, DeptID = 100, EmpID = 10, Name = "Alex", Phone="222" }}
        });

        db.Companies.InsertOnSubmit(newComp);
        db.SubmitChanges();   // use try...catch
        Console.WriteLine("End of Insert");
    }
    public void Select()
    {
        MyDBDataContext db = new MyDBDataContext();
        Emp emp9 = (from em in db.Emps
                    where em.EmpID == 9
                    select em).Single<Emp>();

        Console.WriteLine(emp9.EmpID);

        Dept dept = db.Companies.Single<Company>(c => c.CompanyID == 1).Depts.Single<Dept>(d => d.DeptID == 100);

        IQueryable<Emp> emps = db.Emps.Where(e => e.CompanyID == dept.CompanyID && e.DeptID == dept.DeptID);
        foreach (var e in emps)
        {
            Console.WriteLine(e.Name + " " + e.Phone);
        }
        IList myList = emps.ToList<object>();

        Console.WriteLine("End of Select");
    }

    public void Update()
    {
        MyDBDataContext db = new MyDBDataContext();

        Emp emp = (from e in db.Emps
                    where e.EmpID == 9
                    select e).SingleOrDefault();

        if (emp != null)  //check if emp exists
        {
            emp.Name = "Mr. " + emp.Name;
            emp.Phone = "223-4232";
        }

        ChangeSet cs = db.GetChangeSet();
        Console.WriteLine("ChangeSet : {0}", cs.Inserts.Count + cs.Updates.Count + cs.Deletes.Count);

        db.SubmitChanges();
    }

    public void Delete()
    {
        MyDBDataContext db = new MyDBDataContext();

        Company comp = db.Companies.Single( c => c.CompanyID == 1);

        db.Emps.DeleteAllOnSubmit(comp.Depts.SelectMany(d => d.Emps));
        db.Depts.DeleteAllOnSubmit(comp.Depts);
        db.Companies.DeleteOnSubmit(comp);

        db.SubmitChanges();

        Console.WriteLine("End of Delete");
    }
}