linq的多表查询

public class P_type
        {
            public int p_type_id { get; set; }
            public int parent_id { get; set; }
        }

        public class Products
        {
            public int pid { get; set; }
            public int p_type_id { get; set; }

        }

        static void Main(string[] args)
        {
            List<P_type> list_P_type = new List<P_type>()
            {
                new P_type(){p_type_id=1,parent_id=0},
                new P_type(){p_type_id=2,parent_id=1},
                new P_type(){p_type_id=3,parent_id=1},
            };
            List<Products> list_Products = new List<Products>()
            {
                new Products(){p_type_id=2,pid=1},
                new Products(){p_type_id=2,pid=2},
                new Products(){p_type_id=3,pid=3},
                new Products(){p_type_id=1,pid=4},
            };
            //如何查询list_Products中的pid     在list_P_type中parent_id为1的p_type_id
            //我都不知道怎么描述了..直接来个SQL..
            //select pid from list_Products where list_Products.p_type_id in 
            //(select p_type_id from list_P_type where parent_id=1)
            
            var one = from a in list_P_type where a.p_type_id in (from b in list_P_type where b.parent_id==1 select b.p_type_id) select a.pid;
            //不能这样写啊- -0
        }

 

var one = from a in list_Products  
          let t= list_P_type.Where(x=>x.parent_id == 1).Select(x=>x.p_type_id)
          where t.Contains(a.p_type_id)
          select a;

var one = list_Products.Where(a=>list_P_type.Where(x=>x.parent_id == 1).Select(x=>x.p_type_id).Contains(a.p_type_id));

  var two = list_P_type.Where(x => x.parent_id == 1).Join(list_Products, p => p.p_type_id, p => p.p_type_id, (p_type, p_product) => p_product);

 NHibernate各种查询