How to do join in ADO.NET Entity Framework

Question

I am new to Entity Framework. I have a model as given below.

enter image description here

Since I need to access this from Silverlight I have added a DomainService

[EnableClientAccess()]
    public class DomainService1 : LinqToEntitiesDomainService<TESTDBEntities>
    {
        public IQueryable<Lecture> GetLectures()
        {
            return this.ObjectContext.Lectures;
        }      
        public IQueryable<ProjectGroup> GetProjectGroups()
        {
            return this.ObjectContext.ProjectGroups;
        }
        public IQueryable<Student> GetStudents()
        {
            return this.ObjectContext.Students;
        }
    }

I need to add a method to the domain service that returns List<Student> for a given project group id.

List<Student> GetStudentsByProject(int pgid)

OR

IQueryable<Student> GetStudentsByProject(int pgid)

Since this involves a join, I guess I have to change the Model.edmx as well as DomainService.cs file manually. How can I do this?

Nils Magne Lunde · Accepted Answer · 2012-01-04 14:38:55Z

2

You shouldn't have to change the .edmx file.

public List<Student> GetStudentsByProject(int pgid)
{
    return this.ObjectContext.ProjectGroup.Where(pg => pg.pgid == pgid)
               .SelectMany(pg => pg.Students).ToList();
}

edited Jan 4, 2012 at 14:38

answered Jan 4, 2012 at 12:10

Nils Magne Lunde

1,8241 gold badge15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dennis Traub Over a year ago

Selecting the students through a query to ProjectGroup like in your example will probably result in a very complicated SQL statement containing sub-selects an union statements. Compared to a simple ObjectContext.Students.Where(x => x.ProjectGroup.pgid == projectGroupId) it produces ca. 7 times(!) as much SQL code.

Nils Magne Lunde Over a year ago

I have changed the query to use SelectMany instead of Select, which will produce a much less complex SQL.

Dennis Traub Over a year ago

Why not just get the Students with the given Projectgroup Id directly?

Nils Magne Lunde Over a year ago

@DennisTraub Doing what you suggest is of course perfectly good. I prefer to start at the top and find that more intuitive. At least for more complex queries. For this you could also write ObjectContext.ProjectGroup.FirstOrDefault(pg => pg.pgid == pgid).Students which I find equally intuitive, but it is a matter of taste I guess.

Dennis Traub · Accepted Answer · 2012-01-04 12:15:40Z

1

Just add another method to your domain service and you should be fine:

public IQueryable<Student> GetStudentsByGroup(int projectGroupId)
{
    return this.ObjectContext.Students
               .Where(x => x.ProjectGroup.pgid == projectGroupId);
}

answered Jan 4, 2012 at 12:15

Dennis Traub

51.9k7 gold badges100 silver badges113 bronze badges

Collectives™ on Stack Overflow

How to do join in ADO.NET Entity Framework

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related