LINQ多表查询——只返回3个表中的2个

Question

QUERY:我想返回所有带有CUISINE__BANE = cusisineName的餐厅，无论它们位于哪个城市。我的查询返回RESTAURANT和CITY表，但不返回CUISINE表。非常感谢您的帮助!

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();
             return result;
        }
    }

Answer 1

我怀疑您的查询实际上确实返回城市-您目前只在查询中选择餐厅，相反，您可以投影到具有您想要的信息的特殊类型:

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;
}

假设您的实体具有Name属性，否则请替换为正确的属性名称。

注意，你的餐厅实体已经有CITY_ID, CUISINE_ID属性，所以如果这是一个外键到各自的表，你也应该有CITY和CUISINE属性。如果您对这些属性使用Include()查询，这些属性将被填充，那么查询将看起来像这样:

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

编辑:

听起来你正在使用Linq to Sql，在这种情况下，你可以在你的数据上下文中直接指定加载选项:

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

那么下面的代码应该可以工作:

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

Answer 2

你也可以像这样重新选择:

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

Answer 3

你想达到什么目的?如果您只需要从每个Restuarant访问Cuisine，则可以像这样遍历List<Restuarant>…

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

你也可以像这样得到Cuisine的列表…

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

或者类似的