LINQ到SQL:左连接和联合之间更好

本文关键字：之间 更好 连接 SQL LINQ | 更新日期: 2023-09-27 18:17:15

我想搜索可能在其名称或产品名称中包含关键字的餐厅。我正在使用LINQ到SQL。代码是:

数据上下文:

protected MyDataContext database;

选项1: 

var restaurants = database.tblRestaurants.ByName(keyword).Union(database.tblRestaurants.ByFood(keyword));

ByName方法的签名:

public static IQueryable<tblRestaurant> ByName(this IQueryable<tblRestaurant> restaurants, string name)
{
    var result = from restaurant in restaurants
                 where restaurant.BusinessName.ToUpper().Contains(name.ToUpper())
                 select restaurant;
    return result;
}

ByFood方法的签名:

public static IQueryable<tblRestaurant> ByFood(this IQueryable<tblRestaurant> restaurants, string food)
{
    var result = from restaurant in restaurants
                 from product in restaurant.tblProducts
                 where product.Name.ToUpper().Contains(food.ToUpper())
                 select restaurant;
    return result;
}

很好。但我有另一个选择使用左连接。选项2: 

    var restaurants = from restaurant in database.tblRestaurants
                      join product in database.tblProducts
                          on restaurant.OId equals product.RestaurantOId into products
                      from p in products.DefaultIfEmpty()
                      where
                          p.Name.ToUpper().Contains(keyword.ToUpper()) |
                          restaurant.BusinessName.ToUpper().Contains(keyword.ToUpper())
                      select restaurant;

我的问题是，我应该用哪一个。考虑参数为

  • <
  • 可维护性/gh>OOP

总之，如果你有两个选项

LINQ到SQL:左连接和联合之间更好

你会怎么做?

将选项2中的查询改为如下

from restaurant in restaurants
where restaurant..BusinessName.ToUpper().Contains(keyword.ToUpper()) 
|| restaurant.tblProducts.FirstOrDefault(p=>p.Name.ToUpper().Contains(keyword.ToUpper()) ) != null
select restaurant;
相关文章：