LINQ multi-table query - only 2 of 3 tables are returned

QUERY: I want to return all restaurants with a CUISINE__BANE = cusisineName regardless of the city in which they are located. My query returns the RESTAURANT and CITY tables but not the CUISINE table. Your help is greatly appreciated!

public class DLgetRestaurants
    {
        DL.FVRGDataContext db = new FVRGDataContext();

        public List<RESTAURANT> getRestaurants(string cuisineName)
        {
            var cuisineID = db.CUISINEs.First(s => s.CUISINE_NAME == cuisineName).CUISINE_ID;

            List<RESTAURANT> result = (from RESTAURANT in db.RESTAURANTs
                                       join CITY in db.CITies on RESTAURANT.CITY_ID equals CITY.CITY_ID
                                       join CUISINE in db.CUISINEs on RESTAURANT.CUISINE_ID equals CUISINE.CUISINE_ID
                                       where RESTAURANT.CUISINE_ID == cuisineID
                                       select RESTAURANT).ToList();

   开发者_StackOverflow中文版          return result;
        }
    }

I doubt that your query actually does return the City - you currently only select the restaurant in your query, instead you could project to a special type that has the info you want:

class RestaurantByCuisine
{
  public string RestaurantName {get;set;}
  public string CityName {get;set;}
  public string CuisineName {get;set;}
}

public List<RestaurantByCuisine> getRestaurants(string cuisineName)
{
    var cuisineID = db.CUISINEs.First(s => s.CUISINE_NAME == cuisineName).CUISINE_ID;

    List<RestaurantByCuisine> result = (from RESTAURANT in db.RESTAURANTs
                                join CITY in db.CITies on RESTAURANT.CITY_ID equals CITY.CITY_ID
                                join CUISINE in db.CUISINEs on RESTAURANT.CUISINE_ID equals CUISINE.CUISINE_ID
                                where RESTAURANT.CUISINE_ID == cuisineID
                                select new RestaurantByCuisine() 
                                { 
                                    RestaurantName = RESTAURANT.Name,
                                    CityName = CITY.Name,
                                    CuisineName = CUISINE.Name
                              ).ToList();

    return result;
}

This assumes you entities have a Name property, substitute with the right property name otherwise.

Note that your Restaurant entity already has CITY_ID, CUISINE_ID properties, so if this is a foreign key to the respective tables you also should have CITY and CUISINE properties. These will be populated if you use an Include() query on these properties, the query would then look like this:

List<RESTAURANT> result = (from RESTAURANT in db.RESTAURANTs.Include(x => x.CITY).Include(x => x.CUISINE)
                            where RESTAURANT.CUISINE_ID == cuisineID
                            select RESTAURANT
                            ).ToList();

Edit:

It sounds like you are using Linq to Sql, in this case you can specify the load options directly on your data context:

DL.FVRGDataContext db = new FVRGDataContext();
var dlo = new DataLoadOptions();
dlo.LoadWith<RESTAURANT>(r => r.CITY);
dlo.LoadWith<RESTAURANT>(r => r.CUISINE);
db.LoadOptions = dlo;

Then the following should work:

List<RESTAURANT> result = (from RESTAURANT in db.RESTAURANTs
                           where RESTAURANT.CUISINE_ID == cuisineID
                           select RESTAURANT
                           ).ToList();

You can also re-do your select like this:

        List<RESTAURANT> result = (from RESTAURANT in db.RESTAURANTs.Include("CITY").Include("CUISINE")
                                   where RESTAURANT.CUISINE_ID == cuisineID
                                   select RESTAURANT).ToList();

What is it you are trying to achieve here? If you simply need to access the Cuisine from each Restuarant you will be able to iterate through the List<Restuarant> like so...

List<Restuarant> restuarants = GetList(); // Get the list however you like.
foreach(var r in restuarants)
{
     // Now you can access Cuisine
     var cuisine = r.Cuisine;
}

You can also get a list of Cuisine like this...

var cuisines = restuarants.Where(c => c.CuisineName == "chips").ToList();

Or something similar.