Rewrite a recursive sql select with entity framework 5.0

Question

I want to get all modules whose parent is null that means all root modules.

Furthermore I want that every module knows whether it has children or not.

I need this feature because the root modules are displayed in a lazy loaded module tree.

That way it worked quickly for SQL:

SELECT 
   Id, 
   CASE WHEN EXISTS (SELECT NULL FROM Module m2 WHERE m2.ParentId = module.Id) THEN 1 
        ELSE 0 
   END AS HasChildren 
FROM Module 
WHERE ParentId IS NULL

How can I do the same using EF 5 ?

UPDATE

These are my classes which are my Poco`s using EF code first:

public class Module
    {
        public Module()
        {
            Children = new List<Module>();
        }

        // PK
        public int ModuleId { get; set; }
        public string Name { get; set; }
        public List<Module> Children { get; set; }

        // FK
        public int MachineId { get; set; }
        public Machine Machine { get; set; }
    }

    public class Machine
    {
        // PK
        public int MachineId { get; set; }
        public string Name { get; set; }
    }

UPDATE 2

@Gert

This is the sql generated by EF for you solution .Any() code:

{SELECT 
[Extent1].[ModuleId] AS [ModuleId], 
[Extent1].[Name] AS [Name], 
CASE WHEN ( EXISTS (SELECT 
    1 AS [C1]
    FROM [dbo].[Module] AS [Extent2]
    WHERE [Extent1].[ModuleId] = [Extent2].[Module_ModuleId]
)) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT 
    1 AS [C1]
    FROM [dbo].[Module] AS [Extent3]
    WHERE [Extent1].[ModuleId] = [Extent3].[Module_ModuleId]
)) THEN cast(0 as bit) END AS [C1]
FROM [dbo].[Module] AS [Extent1]
WHERE ([Extent1].[ParentId] IS NULL) AND ([Extent1].[MachineId] = @p__linq__0)}

Your linq query never returns TRUE for HasChildren property because my formery query looks different:

context.Modules.SqlQuery("SELECT Module.ModuleId, Module.Name, case when Exists(select null from Module m where m.ParentID = Module.ModuleId) " +
"then 1 else 0 end as HasChildren FROM Module WHERE Module.ParentId Is Null AND Module.MachineId = @MachineId ORDER BY HierarchyIndex",
new SqlParameter("MachineId",machineId)).ToList();

In "your" sql statement generated by EF I miss the important m.ParentId = Module.ModuleId compare. You do Extend1.ModuleId = Extend2.Module_ModuleId.

There seems something wrong.

Thanks for fixing the terrible sql formatting marc. I could not make it work... — Elisabeth, Jan 23 '13 at 22:12
Can you show a class model and maybe a first effort to achieve what you want? So we'll know where to start. — Gert Arnold, Jan 23 '13 at 22:18

Gert Arnold · Accepted Answer · 2013-01-24T20:45:56.400

1

The two answers so far do not account for the fact that calls to CLR methods in EF queries can't be translated to SQL and EF will throw exceptions.

So you have to solve it with methods that EF can translate to SQL. Now fortunately your query is not recursive (like fetching all levels of the hierarchy) but pretty straighforward:

from m in context.Modules
          where m.ParentId == null
          select new { m.Id, HasChildren = m.Children.Any() }

As you see, I have to make some assumptions about your model (that's why I asked). The main one is that Module has a navigation property Childeren (1:n). You can do without, but then you must (group) join with Modules:

from m in context.Modules.Where(x => x.ParentId == null)
          join child in context.Modules on m.Id equals child.ParentId
              into groupJoin
          select new Node { m.Id, HasChildren = groupJoin.Any() }

(with a Node class having the two get/set properties Id and HasChildren).

edited Jan 24 '13 at 20:45

answered Jan 24 '13 at 09:12

Gert Arnold

105,341
31
202
291

Agreed here that reading your answer and then re-reading the question, this is not as complicated as I first was treating it. – Matthew Jan 24 '13 at 14:43
@gert i have put a children property in the Module class but how can this work? From where does EF know to fill this property? – Elisabeth Jan 24 '13 at 17:28
@Gert I have updated my question with sample code now I am at home... :) – Elisabeth Jan 24 '13 at 18:43
@Elisa EF knows your model, so when you grab a module with `Id = 1` from the database (e.g. by `var module = context.Modules.Find(1)`) you can do `var children = module.Children` and EF will fill the collection by sending a query to the database (if the context is still alive!). – Gert Arnold Jan 24 '13 at 18:56
@Elisa Also, lazy loading should be enabled if you do it this way. This can be done by defining the children as `public virtual ICollection` (note the `virtual`). – Gert Arnold Jan 24 '13 at 18:59
Therefore it did not work. I have lazyloading disabled as I use the Load() explicit loading of data. But when I do what you say having 50 lazy loaded modules then EF will have to do 50 .Any requests which are 50 connections? – Elisabeth Jan 24 '13 at 19:36
How would your join approach look like? I would prefer not to use implicit lazy loading as I use Automapper and I want to avoid uncontrolable happenings... – Elisabeth Jan 24 '13 at 19:38
@Elisa Oh I meant that lazy loading is necessary when you address `module.Children` separately. In the query above the children are joined by EF because it is in one linq statement. Using navigation properties is really recommended for this scenario. – Gert Arnold Jan 24 '13 at 19:45
@Gert 1.) I updated my question 2.) I think you totally misunderstand my scenario. You speak of Children as a navigation property. But navigation properties are foreign keys to other tables/entites. I want to do a linq query within ONE table/entity so .Children.Any() is useless for me. – Elisabeth Jan 24 '13 at 20:02
Added a join query. Navigation properties can be self referencing. Your issue is now turning into a how-to-map question, because your mapping is probably not bi-directional. It would be better to start a new post for it. Of course, again I'll see what I can do then. – Gert Arnold Jan 24 '13 at 20:05
@Gert Thanks for the groupjoin sample. it works in my case 99%. 1% is I can not return an anonymous type and yes SO is full of questions like "convert anonymous type to list" in my sample a List. I am only firm with the extension method syntax not the query syntax. But I tried a select new Module{ HasChildren = groupJoin.Any()} and I got an EntityFrameworkNotSupportedException :P any clue how to solve that? Then I will mark your answer as solution and open a new question with the mapping which seems interesting :) – Elisabeth Jan 24 '13 at 20:32
@Gert ok from here http://stackoverflow.com/questions/5325797/the-entity-cannot-be-constructed-in-a-linq-to-entities-query I have seen I can only go with anonymous type for a partial return and I need it partial because of the HasChildren = groupjoin.Any(). The solutions seems to be to use a ModuleDTO because the Mapped entity can not be used... – Elisabeth Jan 24 '13 at 20:45
Ha, just did something like that! – Gert Arnold Jan 24 '13 at 20:46
yes I saw it hehe :P name it NodeDTO for better understanding ;-) – Elisabeth Jan 24 '13 at 20:47

Thura · Answer 2 · 2013-01-24T04:02:48.573

-2

public void ListModule()
        {
            var raw= from m1 in db.Module
                       where m1.ParentId == null
                       select new { m1.ParentId, wanted = this.test(m1.ParentId) };

        }

public int test(string para)
{
    var raw = from c in db.Module
              where c.ParentId == para
              select c;
    if (raw.Count() != 0)
        return 1;
    else
        return 0;
}

edited Jan 24 '13 at 04:02

answered Jan 24 '13 at 03:52

Thura

127
3
12

why did you not accept this as answer? http://stackoverflow.com/questions/12706310/linq-select-different-column-into-one Your sample code you have from Levis... and now you pass it to me in a terrible way... a void method return raw ? what the heck do you read what you write? – Elisabeth Jan 24 '13 at 18:46
I was misunderstand your question.I thought , you just want 0 and 1 and continue writing yourself. – Thura Jan 25 '13 at 04:00

Rewrite a recursive sql select with entity framework 5.0

2 Answers2