linq to sql complex query with group by

I have a table called "Articles".

it includes the following field:

ArticleIndex, ArticleLevel, ArticleParentIndex.

I made query which returns all the articles with ArticleLevel=1 - let's call it query1. The query which returns all the articles with ArticleLevel=2 - query2.

I would like to have a query that would return Articles of level=1 with at least one child article (the child articles have level=2), and also the number of child articles.

So far I have the following query:

var filteredItemsGrouped = from i in filteredItems
                           group i by i.ArticleParentIndex into g
                           select new { Node = g, NodeItemsCount = g.Count() };

and then, in order to get the actual articles with level=1 I do:

IList<ArticleNodeInfo> Nodes = new List<ArticleNodeInfo>();
        foreach (var node in filteredItemsGrouped)
        {
   开发者_如何转开发         Nodes.Add(new ArticleNodeInfo
            {
                Node = articlesService.GetArticleByIndex((int)(node.Node.FirstOrDefault().ArticleParentIndex)),
                NodeItemsCount = node.NodeItemsCount
            });
        }

This process is too expensive. Is it possible to acheive the same with one query (instead of retreiving by article index every time)?

Hope I'm clear enough...

this should do the trick:

    var articlesLevel1 = (
            from al1 in Articles
            join al2 in Articles on new
            {
                    al1.ArticleIndex,
                    ArticleLevel = 2
            } equals new
            {
                    ArticleIndex = al2.ArticleParentIndex,
                    al2.ArticleLevel
            } into g_al2
            where (al1.ArticleLevel == 1) && g_al2.Any()
            select new
            {
                    ArticlesLevel1 = al1,
                    ArticlesLevel2Count = g_al2.Count()
            });