CODEWITHSUNDEEP
c#sqlasp.net-mvc
Austin
Austin

Reputation: 3080

Query from three tables in C#

I've been trying to pull out a combined query of three tables to get some information, but I cannot seem to get it down.

The Query I am looking for would be similar too this

SELECT DISTINCT e.Name AS Actor
    FROM Movie AS m
    INNER JOIN MovieEmployees AS me
    ON m.ID = me.movieID
    INNER JOIN Employees AS e
    ON me.employeeID = e.ID

My silly coding attempt so far looks like this and it throws errors about conversion types, so I think I am using the wrong variable names.

error:

Cannot implicitly convert type 'System.Linq.IQueryable<AnonymousType#1>' to 'System.Linq.IQueryable<WebApplication2.Entities.Movie>'. An explicit conversion exists (are you missing a cast?) C:\Users\andersona\Documents\Visual Studio 2013\Projects\WebApplication2\WebApplication2\Controllers\MoviesController.cs 32 17 WebApplication2

IQueryable<Movie> movQuery2 =
     from m in db.Movies
     join me in db.MovieEmployees on m.ID equals me.movieID
     join e in db.Employees on me.employeeID equals e.ID
     select new {Name = e.Name };

My Context looks like

public virtual DbSet<Employee> Employees { get; set; }
public virtual DbSet<Location> Locations { get; set; }
public virtual DbSet<Movie> Movies { get; set; }
public virtual DbSet<MovieEmployee> MovieEmployees { get; set; }
public virtual DbSet<Role> Roles { get; set; }
public virtual DbSet<Show> Shows { get; set; }
public virtual DbSet<sysdiagram> sysdiagrams { get; set; }

And the relationship looks like this

enter image description here

All I am trying to do is return a query with all three of those tables' data so that I can extract certain items for Views.

i.e. return View(queryresults);

Any help would be greatly appreciated, and if you could briefly explain why your way works or mine doesn't I would be quite thankful as I want to be able to replicate this method for other tables and Views.

Upvotes: 3

Views: 426

Answers (1)

D Stanley
D Stanley

Reputation: 152521

So either you want a collection of movies or a collection of names. If you want a collection of Movies:

IQueryable<Movie> movQuery2 =
     from m in db.Movies
     join me in db.MovieEmployees on m.ID equals me.movieID
     join e in db.Employees on me.employeeID equals e.ID
     select m;

or possible just

IQueryable<Movie> movQuery2 =
     from m in db.Movies
     select m;

if you want a collection of employee names you could do:

IQueryable<string> query = 
     from m in db.Movies
     join me in db.MovieEmployees on m.ID equals me.movieID
     join e in db.Employees on me.employeeID equals e.ID
     select e.Name;

or possible just

IQueryable<string> query = 
     from me in db.MovieEmployees
     join e in db.Employees on me.employeeID equals e.ID
     select e.Name;

To select multiple items you can create an anonymous type:

var query = 
     from m in db.Movies
     join me in db.MovieEmployees on m.ID equals me.movieID
     join e in db.Employees on me.employeeID equals e.ID
     select new {e.Name, m.ID};

Upvotes: 3

Related Questions