Join Entity Framework

Question

I want to have the following type of request in the operation of the entity frame

SELECT  c2.* 
FROM    Category c1 INNER JOIN Category c2
ON      c1.CategoryID = c2.ParentCategoryID
WHERE   c1.ParentCategoryID is NULL

How to perform the above work in the Entity infrastructure ...

Answer 1

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

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

Answer 2

Just to clean it up, the following is slightly better and does the same:

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

Answer 3

EF 4.0+ LEFT JOIN :

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
        }

SQL Server Profiler, , LEFT OUTER JOIN. , LEFT JOIN ( "Group Join" ) Linq-to-Entity, , self-join INNER JOIN - !

Permission for it? Like crazy, and according to MS it sounds wrong, I solved it by changing the order of the connection sentences. If the self-regulatory LEFT JOIN clause was the first Linq Group, SQL Profiler reported an INNER JOIN. If the LEFT JOIN suggestion for self-regulation was LAST Linq Group Join, SQL Profiler reported LEFT JOIN.