求助 linq not in 联表查询

[yutiansh2]

select * from CM_EduColumn a
where a.ID not in (select b.CourseEduColumn from CourseEduColum b where b.CourseID=47) 用linq 怎么实现呀

[luhuiming741cs]

var query=a.where(p=>!(b.where(q=>q.CourseID==47).select(q.CourseEduColumn ).Contains(p));

[天下如山]

Linq to Object(自己胡乱写的)

Parent p1 = new Parent() { ID = 1, ParentName = "父亲1", ParentTime = "2012-08-01" };
Parent p2 = new Parent() { ID = 2, ParentName = "父亲2", ParentTime = "2012-08-02" };
Parent p3 = new Parent() { ID = 3, ParentName = "父亲3", ParentTime = "2012-08-03" };
Parent p4 = new Parent() { ID = 4, ParentName = "父亲4", ParentTime = "2012-08-04" };
Parent p5 = new Parent() { ID = 5, ParentName = "父亲5", ParentTime = "2012-08-05" };
Child c1 = new Child() { ChildID = 1, ChildName="儿子1", ChildTime = "2012-08-01", ParentID=1 };
Child c2 = new Child() { ChildID = 2, ChildName = "儿子2", ChildTime = "2012-08-01", ParentID = 2 };
Child c3 = new Child() { ChildID = 3, ChildName = "儿子3", ChildTime = "2012-08-01", ParentID = 2 };
Child c4 = new Child() { ChildID = 4, ChildName = "儿子4", ChildTime = "2012-08-01", ParentID = 1 };
Child c5 = new Child() { ChildID = 5, ChildName = "儿子5", ChildTime = "2012-08-01", ParentID = 4 };
IList<Parent> parents = new List<Parent>() { p1,p2,p3,p4,p5 };
IList<Child> childs = new List<Child>() { c1,c2,c3,c4,c5 };
var query1 = (from p in parents
                select p).Where(x=>(from b in childs where x.ID==b.ParentID && b.ParentID==1 select b).Count()==0);
foreach(var item in query1)
{
    Console.WriteLine(item.ID+item.ParentName);
}

[yutiansh2]

自己 最终的 也贴上

var query=entity.CM_EduColumn.Where(p => !entity.CourseEduColum.Where(m => m.CourseID == courseid).Select(m => m.CourseEduColumn).Contains(p.ID));

[yutiansh2]

结贴

var query=entity.CM_EduColumn.Where(p => !entity.CourseEduColum.Where(m => m.CourseID == courseid).Select(m => m.CourseEduColumn).Contains(p.ID));

[天下如山]

以前抵触Linq to Sql 所以也就么有学习 现在是慢慢的来熟悉Linq 但是还是不接触Linq to Sql.
Linq 还是蛮优雅的.