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

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

You need to show your entities. Also, there is no reason to add `AsQueryable()` after `dbContext.FirmParameters` which is already an `IQueryable`. — haim770, Jul 08 '15 at 18:56
Without it, compilation fails. Cannot implicitly convert type 'System.Linq.IQueryable' to 'System.Data.Entity.DbSet'. An explicit conversion exists (are you missing a cast?) @haim770 — Hoppe, Jul 08 '15 at 18:59
That's because later you're assigning the returned type of `Queryable.Where` to the same `parameters` variable. Although `AsQueryable()` solves the problem, it's intended for something else. A more readable approach would simply be `IQueryable parameters = dbContext.FirmParameters;` — haim770, Jul 08 '15 at 19:06
@Hoppe, Actually I was wrong assuming that this isn't a legitimate use of `AsQueryable()`, in fact, it's perfectly fine. — haim770, Jul 08 '15 at 19:15

score 1 · Answer 1 · answered Jul 08 '15 at 19:40

1

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();

answered Jul 08 '15 at 19:40

John

17,163
16
65
83

This looks promising but how do I get the firmFeature? I.e. The following is null: var list = parameters.ToList(); var feature = list[0].FirmFeature; – Hoppe Jul 08 '15 at 19:54
was the firmfeature you had marked as IsDbTable? if not I'd think the results would all have FirmFeature = null; you should be able to log the SQL that got generated and play around with it in sql manager to see what's going on – John Jul 08 '15 at 19:58
This still makes a second Db call to get the feature. Include didn't seem to work... Am I out of luck? @John – Hoppe Jul 09 '15 at 13:40
did you add a .tolist() or something else to execute the isDbTableQuery line early? I have a call very similar to this in an application, and it correctly generates that first query as a subquery in a single call – John Jul 09 '15 at 13:46

RMalke · Answer 2 · 2015-08-04T18:18:22.190

I cannot test it right now, but if your only problem is the two round trips you can acomplish that using two LEFT joins, and selecting the appropriate source.

Something like:

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();

Not sure how to get this to compile... 3 compilation errors. 2x: Expected contextual keyword 'equals'. 1x: semicolon expected — Hoppe, Aug 04 '15 at 16:44
@Hoppe I was without compile at the time, I corrected the stated errors — RMalke, Aug 04 '15 at 18:19

Gert Arnold · Answer 3 · 2015-08-04T19:30:50.717

You can put the condition in the join statement. I'll do this in query syntax because that always reads far easier with joins:

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

Or simply:

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

where you use Include to get FirmParameters having their FirmFeature references loaded (if there are any).

This only selects the parameters, but doesn't select the features — Hoppe, Aug 04 '15 at 19:12

czifro · Answer 4 · 2015-08-05T00:25:54.357

0

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

UPDATE

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();

edited Aug 05 '15 at 00:25

answered Jul 08 '15 at 20:46

czifro

784
7
24

This doesn't retrieve any data from the FirmFeatures table. So that's a second call to the DB – Hoppe Aug 04 '15 at 16:37
@Hoppe , I think you are trying to accomplish an outer join, http://stackoverflow.com/a/3413732/1998487. I'll update my answer with an example. – czifro Aug 05 '15 at 00:25

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

4 Answers4