是否可以实现条件联接子句-join ON condition1 OR condition2

Question

是否可以在实体框架6中实现条件联接子句？具体来说，INNER JOIN ON(布尔条件1(或(布尔条件2(。

下面的代码可以工作，但会调用数据库两次。有可能把它合并成一个电话吗？

存在将FirmFeatures.FeatureId与可为空的FirmParameters.FeatureId 联系在一起的外键关系

var dbContext = new MyEntities();
var feature = dbContext.FirmFeatures
    .Where(f => f.FeatureId == featureId)
    .First();
var parameters = dbContext.FirmParameters.AsQueryable();
parameters = feature.IsDbTable 
    ? parameters.Where(p => p.FeatureId == null) 
    : parameters.Where(p => p.FeatureId == featureId);
var list = parameters.ToList()

SQL调用看起来像：

SELECT feature.*, parameter.*
FROM [FirmFeature] AS feature
INNER JOIN [FirmParameter] AS parameter 
    ON (feature.IsDbTable = 0 AND feature.FeatureId = parameter.FeatureId) OR (feature.IsDbTable = 1 AND parameter.FeatureId IS NULL)
WHERE feature.[FeatureId] = 3

这首先利用了数据库模型。

我是实体框架的新手。

第2版：我希望从数据库中加载一个features对象和一个parameters对象。

编辑：根据要求，以下是型号：

{
    public FirmFeature()
    { this.FirmParameters = new HashSet<FirmParameter>(); }
    public byte FeatureId { get; set; }
    public bool IsDbTable { get; set; }
    ...
    public virtual ICollection<FirmParameter> FirmParameters { get; set; }
}
public partial class FirmParameter
{
    public byte ParameterId { get; set; }
    public Nullable<byte> FeatureId { get; set; }
    ...    
    public virtual FirmFeature FirmFeature { get; set; }
    public virtual FirmParameter FirmParameter1 { get; set; }
    public virtual FirmParameter FirmParameter2 { get; set; }
}

Answer 1

尝试尝试一下：

var isDbTableQuery = dbContext.FirmFeatures.Where(f => f.FeatureId == featureId && f.IsDbTable);
var parameters = dbContext.FirmParameters.Where(p => isDbTableQuery.Any() ? p.FeatureId == null : p.FeatureId == featureId);
var list = parameters.ToList();

Answer 2

我现在无法测试它，但如果你唯一的问题是两次往返，你可以使用两个LEFT联接并选择合适的源来实现这一点。

类似于：

var query = from feature in dbContext.FirmFeatures
            join parameter0 in dbContext.FirmParameters
              on new { IsDbTable = feature.IsDbTable, FeatureId = feature.FeatureId } equals new { IsDbTable = false, FeatureId = parameter0.FeatureId ?? 0 }
              into left_parameter_0
              from parameter_0 in left_parameter_0.DefaultIfEmpty()
            join parameter1 in dbContext.FirmParameters
              on new { IsDbTable = feature.IsDbTable, FeatureId = (byte?)null } equals new { IsDbTable = true, FeatureId = parameter1.FeatureId }
              into left_parameter_1
              from parameter_1 in left_parameter_1.DefaultIfEmpty()
select new { Feature = feature, Parameter = parameter_0 != null ? parameter_0 : parameter_1 };
var list = query.ToList();

Answer 3

您可以将条件放入联接语句中。我将在查询语法中这样做，因为使用联接总是容易得多：

var q = from f in dbContext.FirmFeatures
        where f.FeatureId == featureId
        join p in dbContext.FirmParameters on 
            (f.IsDbTable ? null : f.FeatureId) equals p.FeatureId
        select new { p, f };

或者简单地说：

var q2 = from p in dbContext.FirmParameters.Include(p => p.FirmFeature)
         where (p.FirmFeature.FeatureId == featureId && p.FirmFeature.IsDbTable)
            || p.Feature == null
         select p;

其中使用Include来获得加载了FirmFeature引用的FirmParameters(如果有的话(。

Answer 4

var list = dbContext.FirmParameters
     .Where(p => (p.FirmFeature.FeatureId == featureId && p.FirmFeature.IsDbTable) ? 
            p.FeatureId == null : p.FeatureId == featureId)
     .ToList();

更新

var list = dbContext.FirmParameters
           .Join(dbContext.FirmFeature, p.FeatureId, f.FeatureId, (p, f) => new { Parameter = p, Feature = f})
           .Where(@f => @f.Feature.FeatureId == featureId)
           .Where(@p => (@p.Feature.IsDbTable ? @p.Parameter.FeatureId == null : @p.Parameter.FeatureId == featureId))
           .Select(@x => new { Feature = @x.Feature, Parameter = @x.Parameter })
           .DefaultIfEmpty()
           .ToList();