[C#/.NET]使用Linq进行包含IN的子查询并按指定字段分组统计

刚才有个同学在一个交流群里提问，根据他的SQL查询语句转换成对就的Linq查询语句，其中SQL语句包含了一个子查询，并按一个指定的字段进行分组统计总数。SQL语句是这样的：

select count(*) num,QL_KnowledgeID from WebSite_questionlist 
where QL_KnowledgeID in(select id from WebSite_KnowledgeBase) group by QL_KnowledgeID

看了一下，没有什么特别的难点。写了一个小小的DEMO，来将他的SQL查询转换成对应的Linq查询，写在这里以方便需要了解的同学参考，如下：

using System;
using System.Collections.Generic;
using System.Linq;

namespace ConsoleApplication2
{

    public class Program
    {
        static void Main(string[] args)
        {
            var includeKnowledgeIds = new List<KnowledgeBase> {
                new KnowledgeBase { KnowledgeId=1},
                new KnowledgeBase { KnowledgeId=2},
                new KnowledgeBase { KnowledgeId=3},
                new KnowledgeBase { KnowledgeId=5}
            };
            var questions = new List<Question> {
                new Question { Id=1,KnowledgeId=1},
                new Question { Id=2,KnowledgeId=2},
                new Question { Id=3,KnowledgeId=3},
                new Question { Id=4,KnowledgeId=1},
                new Question { Id=5,KnowledgeId=2},
                new Question { Id=6,KnowledgeId=4}
            };
            var query = from q in questions
                        where (
                            from i in includeKnowledgeIds
                            select i.KnowledgeId
                        ).ToList().Contains(q.KnowledgeId)
                        group q by q.KnowledgeId into questionGroup
                        select new
                        {
                            Key = questionGroup.Key,
                            Count = questionGroup.Count()
                        };
            query.ToList().ForEach(x =>
            {
                Console.WriteLine("KnowledgeId:{0}==>>Count:{1}", x.Key, x.Count);
            });
            Console.ReadKey();
        }
    }

    public class Question
    {
        public int Id { get; set; }
        public int KnowledgeId { get; set; }
    }

    public class KnowledgeBase
    {
        public int KnowledgeId { get; set; }
    }
}

运行结果如图：