Possible to implement a conditional join clause - JOIN ON condition1 OR condition2?

c# entity-framework entity-framework-6 linq sql-server

Question

Is it possible to implement a conditional join clause in Entity Framework 6? Specifically, INNER JOIN ON (boolean condition1) OR (boolean condition2).

The code below works, but calls the database twice. Is it possible to consolidate it down into one call?

There is a foreign key relationship that ties FirmFeatures.FeatureId to Nullable 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()

The SQL call would look something like:

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

This leveraged database model first.

I'm new to the Entity Framework.

Edit2: I'm hoping to have both a features object and a parameters object loaded from the database as a result of this.

EDIT: As requested, here are the models:

{
    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; }
}
1
1
7/8/2015 7:46:39 PM

Popular Answer

try giving this a shot:

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();
1
7/8/2015 7:40:19 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow