IEnumerable.Except wont work, so what do I do?

Question

I have a linq to sql database. Very simplified we have 3 tables, Projects and Users. There is a joining table called User_Projects which joins them together.

I already have a working method of getting IEnumberable<Project> for a given user.

from up in User_Projects
select up.Project;

Now I want to get the projects the user isn't involved with. I figured the except method of IEnumerable would be pretty good here:

return db.Projects.Except(GetProjects());

That compiles, however I get a runtime error: "Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator."

Is there any way to get around this?

Update:

A few views but no answers :p

I have tried this:

        IEnumerable<Project> allProjects = db.Projects;
        IEnumerable<Project> userProjects = GetProjects();
        return allProjects.Except(GetProjects());

I know it's essentially the same as the original statement - but now i dont get a runtime error. Unfortunately, it doesn't really do the except part and just returns all the projects, for some reason

I edited my answer to include a quick explanation of why you have to do it that way. — Bryan Watts, Dec 01 '08 at 17:09

Bryan Watts · Accepted Answer · 2008-12-01T17:08:07.793

6

Linq to Sql doesn't understand how to work with an arbitrary in-memory sequence of objects. You need to express this in relational terms, which works on IDs:

var userProjectIds =
    from project in GetProjects()
    select project.ProjectId;

var nonUserProjects =
    from project in db.Projects
    where !userProjectIds.Contains(project.ProjectId)
    select project;

edited Dec 01 '08 at 17:08

answered Dec 01 '08 at 17:02

Bryan Watts

44,911
16
83
88

Yup this did it, it's pretty lame that Except() didnt work though :( – Chris James Dec 01 '08 at 17:08
@Bryan Watts, you just changed my quality of life significantly with the statement "Linq to Sql doesn't undertand how to work with an arbitrary in-memory sequence of objects." Thank you – Rachael Mar 01 '13 at 22:35
@UB3571: That is quite a compliment. Thanks! – Bryan Watts Mar 01 '13 at 23:01

score 3 · Answer 2 · answered Dec 01 '08 at 17:06

3

You could try something simple like

User u = SomeUser;

from up in User_Projects
where up.User != u
select up.Project;

answered Dec 01 '08 at 17:06

Agies

1,105
8
12

FAR better, but it needs a distinct. – TheSoftwareJedi Dec 04 '08 at 04:17

score 0 · Answer 3 · answered Dec 01 '08 at 17:00

0

try this:

var userProjects = GetProjects();
return db.Projects.Except(userProjects.ToArray());

The ToArray should force evaluation of the sequence (if I'm understanding the issue right) and allow the operation to succeed.

answered Dec 01 '08 at 17:00

technophile

3,556
1
20
24

IEnumerable.Except wont work, so what do I do?

Update:

3 Answers3

Linked