EF & linq - many to many relationship

I have 3 tables with a many to many relationship between them

User: Id (PK), Name

UserCourses: UserId (PK), CourseId (PK)

Courses: Id (PK), Name

I need to write a linq query to select all the courses name of user X and return in a IEnumerable but I can't get it to开发者_运维百科 work.

EDIT:

public IEnumerable<Courses> GetCourses
        {
            get
            {
                return (from a in _entities.Users.Include("Courses")
                        where a.Id == this.Id
                        select a.Courses.AsEnumerable()
                        );
            }
        }

Any help much appreciated

Thanks

---

You're a little unclear about what's going wrong, but that looks like it would produce an IEnumerable<IEnumerable<Courses>> type. If you're looking for a flattened IEnumerable<Courses>, which I think you are, you need something like:

    public IEnumerable<Courses> GetCourses
    {
        get
        {
            var query = from a in _entities.Users.Include("Courses")
                        where a.Id == this.Id
                        select a;

            return query.FirstOrDefault().Courses;
        }
    }

---

EDIT: To avoid a NullReferenceException, try this instead:

    public IEnumerable<Courses> GetCourses
    {
        get
        {
            var query = from a in _entities.Users.Include("Courses")
                        where a.Id == this.Id
                        select a.Courses; //note the difference

            return query.SelectMany(i => i);
        }
    }

Note that there are more than one ways to do this; for example, you could also use:

            var query = from a in _entities.Users.Include("Courses")
                        where a.Id == this.Id
                        select a;

            var user = query.FirstOrDefault();

            return user == null 
                      ? user.Courses
                      : null;

If the SelectMany version still causes a NullReferenceException, use this instead. I don't think it should, but I haven't tested it. You indicated that Rup's solution had done that, and he's using SelectMany in much the same way I am, so this last version plays it safe.

---

That more or less looks OK to me. Doesn't that work if you just drop the .AsEnumerable()? I don't think you need that.

I'm more comfortable with the LINQ extension method call syntax; I think the way to do this like that would be

var courses = _entities.Users.Include("Courses")
                       .Where(a => a.Id == this.Id)
                       .SelectMany(a => a.Courses);

with an extra .Select(c => c.Name) for just the course names.

---

Try that

from b in _entities.Users.Include("UserCourses") on a.UserID equals this.ID
join c in _entities.Users.Include("Courses") on b.CourseID equals c.ID
select c.name

---

Query the junction table directly with joins:

            var courses = (from uc in _entities.UserCourses
                           inner join c in _entities.Courses on uc.CourseId equals c.Id
                           inner join u in _entities.Users on uc.UserId equals u.Id
                           where uc.UserId equals u.Id
                           select c).ToList();