linq compare 2 list

Question

var companytoexclude = from c in db.Companies
                   from p in c.Projects
                   where p.ProjectEndDate == null
                   select c;

var list = from m in db.Members.Include("Companies.Projects.Experiences")
                     where m.MemberId == id
                     select m;

how do I exclude companies that are in companytoexclude in list?

Member have many company
company have many project
project have many experiences

K I kind of solved it. So what I did is just add in

   foreach (Company c in companytoexclude )
            {
                list .First().Companies.Remove(c);
            }
            

so is there a easier to show this? I want the member with list of companies, not just companies.

some data

Member
MemberId    Name
2011        Jet

Company
CompanyId   MemberId    CompanyName
18          2011            Company1
29          2011            Company2

ProjectId   CompanyId   ProjectName ProjectEndDate
1           29          Project1    2008-08-01 00:00:00.000
2           29          Project2    2009-08-01 00:00:00.000
3           18          Project3        NULL

now I want it to return everything but company1 and project3

Answer 1

How about:

var list = from m in db.Members.Include("Companies.Projects.Experiences")
           where m.MemberId == id
           where !m.Companies.Intersect(companiesToExclude).Any()
           select m;

That will only return members which don't include any companies which you're trying to exclude.

Mind you, given that you're specifying a MemberId, I'd pretty much only expect one result anyway... it's unclear what you're trying to do.

If you want all the companies from that single member except the companies to exclude, it's easy:

var member = db.Members.Include("Companies.Projects.Experiences")
               .Single(m => m.MemberId == id);
var companies = member.Companies.Except(companiesToExclude);

If that's not what you're after, please clarify your question.

Also note that your companiesToExclude query may well not be right to start with. Are you trying to exclude all companies with any project with a null end date? If so, you want:

var companiesToExclude = db.Companies
                           .Where(c => c.Projects
                                        .Any(p => p.ProjectEndDate == null))