Foreign key with constant in Fluent API

Question

I need to load a reqired:many relationship from database. Now my problem is, that the the key of the related table consists of three keys:

public partial class EnumValue
{
    [Key]
    [Column(Order = 0)]
    [StringLength(14)]
    public string EnumGroup { get; set; }

    [Key]
    [Column(Order = 1)]
    public byte EnumId { get; set; }

    [Key]
    [Column(Order = 2)]
    [StringLength(3)]
    public string Language { get; set; }

    [StringLength(50)]
    public string Description { get; set; }

}

In my other object I have only one property to fill the foreign key, the other parts are constants specific for this object.
I tried to build the relations in EF6, but can't get it to work with model builder and the Fluent API using constants instead of properties:

modelBuilder.Entity<SupplierCondition>()                
    .HasRequired(t => t.ConditionTypeLookupRef)
    .WithMany()
    .HasForeignKey(t => new { "PArt", t.ConditionType, "EN" });

How can I pass constants as a value for a foreign key in Fluent API?

If those constants are always the same so the primary key is just ConditionType property. — CodeNotFound, Jun 07 '18 at 12:12
for 'SupplierCondition' it's always the same, for another table it's different. — Obl Tobl, Jun 07 '18 at 12:22
What are you using for the structure of the anonymous type in your `HasForeignKey()` call? I'm trying to use that construct to specify the foreign key's name, as discussed [here](https://stackoverflow.com/q/59363602). — InteXX, Dec 16 '19 at 20:30

score 1 · Answer 1 · answered Jun 07 '18 at 13:16

1

You can't use there const values because there isn't assign any value, you inform fluent API, which property refers to another table, when you pass "text", or 2, or new List(), it won't work they aren't properties, i think you can divide it to three tables, and use it as tags. But I can't see the bigger picture.

answered Jun 07 '18 at 13:16

Kacper

451
6
17

1

thanks for your answer. I can't divide the tables, it's a big system where also other applications have access. So no changes are possible to the database. I think I have to find some other solution... – Obl Tobl Jun 07 '18 at 13:40
@OblTobl have you found any solutions to this? I've got the very same issue here and I'm starting to get a bit frustrated.. – krpec Mar 19 '19 at 11:00
@krpec No, unfortunately not. I now used a workaround and removed the according entites from modelbuilder and wrote methods in the according controller where I can build the joins manually. – Obl Tobl Mar 19 '19 at 11:04
@OblTobl Ok, so basically what I've got in store if there's no solution. Thanks for the reply! – krpec Mar 19 '19 at 11:20
@OblTobl Ugh my exact problem. I can't find where the foreign key table is being hit though. A breakpoint in its controller never gets hit so setting the logic in the controller isn't an option for me right now. – Newbie12345 Apr 23 '19 at 19:30

score 0 · Answer 2 · answered Jun 23 '21 at 18:16

One way to do this is to use a discriminator:

abstract class Condition {
  int condType;
  int condVal;
}
class PartCondition : Condtion {}

and in your context:

builder.Entity<Condition>(e => {
  // ...
  e.HasDiscriminator(e => e.condType)
    .HasValue<Condition>("")
    .HasValue<PartCondition>("Part")
});

I don't think this will let you use a composite (multi-column) discriminator, though.

score 0 · Answer 3 · edited Mar 13 '23 at 19:47

We had a similar case that we solved using a DbCommandTreeInterceptor. We are using soft deletes and there is no global filter on the value. Yes, that could be implemented but it is not, and going through the millions of lines of code where we do access deleted data would be finding a needle in a haystack.

So what we had was that navigation properties had to be set up with a foreign key to the soft-del column. Meaning if the row in the table you join is deleted it would not be a match. For this, we wanted our foreign keys and primary keys to consist of id + del where del was a constant of 0. Joining del from one table to another in some cases degraded performance.

So by implementing a Visitor for DbJoinExpression we could modify the expression to use a ConstantExpression instead of a DbPropertyExpression to use in the Left/Right of the DbComparisonExpression.

        private class JoinSetDelEqualsZeroExpressionVisitor : DefaultExpressionVisitor
    {
        private static readonly List<DbExpressionKind> SupportedJoins = new() { DbExpressionKind.FullOuterJoin, DbExpressionKind.InnerJoin, DbExpressionKind.LeftOuterJoin };

        public override DbExpression Visit(DbJoinExpression expression)
        {
            var joinType = expression.ExpressionKind;

            if (!SupportedJoins.Contains(joinType))
                return base.Visit(expression);

            if (expression.JoinCondition is not DbAndExpression oldJoinCondition)
                return base.Visit(expression);
            
            var newCompareLeft = CreateNewCompare(oldJoinCondition.Left);
            var newCompareRight = CreateNewCompare(oldJoinCondition.Right);
            var newJoinCondition = DbExpressionBuilder.And(newCompareLeft, newCompareRight);

            DbJoinExpression newExp;
            if (joinType == DbExpressionKind.LeftOuterJoin)
                newExp = DbExpressionBuilder.LeftOuterJoin(expression.Left, expression.Right, newJoinCondition);
            else if (joinType == DbExpressionKind.InnerJoin)
                newExp = DbExpressionBuilder.InnerJoin(expression.Left, expression.Right, newJoinCondition);
            else if (joinType == DbExpressionKind.FullOuterJoin)
                newExp = DbExpressionBuilder.FullOuterJoin(expression.Left, expression.Right, newJoinCondition);
            else
                return base.Visit(expression);

            return base.Visit(newExp);
        }

        private DbExpression CreateNewCompare(DbExpression exp)
        {
            if (exp is not DbComparisonExpression oldComp || 
                oldComp.Left is not DbPropertyExpression oldLeftPropExp || 
                oldComp.Right is not DbPropertyExpression oldRightPropExp)
                return exp;

            if (oldLeftPropExp.Property.Name == "del" || oldRightPropExp.Property.Name == "del")
            {
                DbExpression newLeft;
                if (oldLeftPropExp.Property.Name == "del")
                    newLeft = DbExpressionBuilder.Equal(oldLeftPropExp, DbExpressionBuilder.Constant(0));
                else
                    newLeft = oldLeftPropExp;

                DbExpression newRight;
                if (oldRightPropExp.Property.Name == "del")
                    newRight = DbExpressionBuilder.Equal(oldRightPropExp, DbExpressionBuilder.Constant(0));
                else
                    newRight = oldRightPropExp;

                return DbExpressionBuilder.And(newLeft, newRight);
            }

            return exp;
        }
    }

The result will be instead of joining table1.del with table2.del the join condition will be table1.del = 0 AND table2.del = 0.

It is not a beautiful solution but works for us.

Foreign key with constant in Fluent API

3 Answers3

Linked