Returning data from multiple sql tables entity framework web api

Question

I have a DB stored locally with a number of different tables, all connected together like this:

I am trying to create a web API for these with entity framework on vs. My controller looks like this:

public class CourseController : ApiController
{
    QubDBEntities entities = new QubDBEntities();

    public IEnumerable<Course> Get()
    {
        using (QubDBEntities entities = new QubDBEntities())
        {
            return entities.Courses.ToList();
        }
    }

    public Course Get(Guid id)
    {
        using (QubDBEntities entities = new QubDBEntities())
        {
            return entities.Courses.FirstOrDefault(e => e.Id == id);
        }
    }

    public Course Get(string day, DateTime time, int duration,string moduleRef, string moduleName, string courseRef,
        string roomRef,string roomName, string roomFloor, int roomNumber, string buildingRef, string buildingName)
    {
        //how do I join tables inside here so that I can 
        //return the courses with this view?^
    }
}

I am not sure how to join these tables using entity framework? My SQL select statement here:

SELECT
    dbo.TimeTable.Day, dbo.TimeTable.StartTime, dbo.TimeTable.Duration, dbo.Module.ModuleRef
    , dbo.Module.ModuleName, dbo.Course.CourseRef, dbo.Room.RoomRef, dbo.Room.RoomName
    , dbo.Room.RoomFloor, dbo.Room.RoomNumber, dbo.Building.BuildingRef, dbo.Building.BuildingName

FROM dbo.Room INNER JOIN
         dbo.TimeTable INNER JOIN
         dbo.Module ON dbo.TimeTable.ModuleId = dbo.Module.Id ON dbo.Room.Id = dbo.TimeTable.RoomId INNER JOIN
         dbo.Building ON dbo.Room.BuildingId = dbo.Building.Id LEFT OUTER JOIN
         dbo.Course INNER JOIN
         dbo.CourseModule ON dbo.Course.Id = dbo.CourseModule.CourseId ON dbo.Module.Id = dbo.CourseModule.ModuleId

Any help / pointers would be appreciated!

Cyber Progs · Accepted Answer · 2017-10-30 16:27:00Z

1

There is three ways you can return results from joining tables in Entity Framework :
1- using Linq , please refer to link here
2- using include , please refer to link here
3- using raw sql like using generic function _context.Database.SqlQuery<type>(sqlString)

answered Oct 30, 2017 at 16:27

Cyber Progs

3,8833 gold badges35 silver badges39 bronze badges

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

Collectives™ on Stack Overflow

Returning data from multiple sql tables entity framework web api

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related