Self join in Entity Framework

I want to have following type of query in entity frame work

SELECT  c2.* 
FROM    Category c1 INNER JOIN Category c2
ON      c1.CategoryID = c2.ParentCategoryID
WHERE   c1.P开发者_开发问答arentCategoryID is NULL

How to do the above work in Entity framework...

---

Well, I don't know much about EF, but that looks something like:

var query = from c1 in db.Category
            where c1.ParentCategoryId == null
            join c2 in db.Category on c1.CategoryId equals c2.ParentCategoryId
            select c2;

---

Just to tidy this up the following is a bit nicer and does the same thing:

var query = from c1 in db.Category
            from c2 in db.Category
            where c1.ParentCategoryId == null
            where c1.CategoryId == c2.ParentCategoryId
            select c2;

---

In EF 4.0+, LEFT JOIN syntax is a little different and presents a crazy quirk:

var query = from c1 in db.Category
        join c2 in db.Category on c1.CategoryID equals c2.ParentCategoryID 
        into ChildCategory
        from cc in ChildCategory.DefaultIfEmpty()
        select new CategoryObject 
        {
            CategoryID = c1.CategoryID, 
            ChildName = cc.CategoryName
        }

If you capture the execution of this query in SQL Server Profiler, you will see that it does indeed perform a LEFT OUTER JOIN. HOWEVER, if you have multiple LEFT JOIN ("Group Join") clauses in your Linq-to-Entity query, I have found that the self-join clause MAY actually execute as in INNER JOIN - EVEN IF THE ABOVE SYNTAX IS USED!

The resolution to that? As crazy and, according to MS, wrong as it sounds, I resolved this by changing the order of the join clauses. If the self-referencing LEFT JOIN clause was the 1st Linq Group Join, SQL Profiler reported an INNER JOIN. If the self-referencing LEFT JOIN clause was the LAST Linq Group Join, SQL Profiler reported an LEFT JOIN.