Sunday, March 6, 2011

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

    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.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>();


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


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


        Console.WriteLine("End of Delete");

Tuesday, March 1, 2011

Code Coverage Analysis exception: The process cannot access the file because it is being used by another process

Sometimes when I open code coverage window from Test->Windows->Code Coverage Results, I couldn't open the code coverage data and got the following exceptions.

"Code Coverage Analysis engine threw exception(s):
 The process cannot access the file 'data.coverage' because it is being used by another process."
First of all, we need to check who is holding the file. Go to the file path and try to delete it.

So it turns out that data.coverage file is in use in VSPerfMon.exe process.
Basically I think this is a Visual Studio bug. I spent some time to dig into it and here is my understanding.

When you run tests, VS creates a child process called QTAgent32.exe (test runner)
and QTAgent32.exe, in turn, creates another child process ‘VSPerfMon.exe’ which is a code coverage process.
If there is a unhandled exception, QTAgent32.exe can crash and it terminates the test runner process but does not have a chance to terminate its child process. So VSPerfMon.exe survives and holding the coverage data file (data.coverage).
Now when you open Code Coverage window, it tries to read coverage data file while VSPerfMon process still hold the file in write mode. Hence the exception.

Sure, Microsoft should fix it. Until then,
1. Short-term
If you kill the remaning VSPerfmon process, you can open the code coverage.
After kill the process, close existing Code Coverage window & reopen it.

C > kill -f VSPerfMon.exe
process VSPerfMon.exe (6856) - 'OleMainThreadWndName' killed

2. Long-term
Fix the test code so that it does not threw any unhandled exception that crashes test runner.
Regardless of MS's fix, you'd always better fix your test codes :-)