EF self-referencing with constraints Many to Many

Question

I have a class with a self-referencing relation. The class is as follows:

public class TableActivity
{

    public TableActivity()
    {
    }

    public int TableActivityID {get;set;}
    public string Table {get;set;}      
    public Activity Activity {get;set;}

    public virtual ObservableCollection<TableActivity> RelatedActivities {get;set;}
}

in my context class

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<TableActivity>() 
        .HasMany<TableActivity>(t => t.TableActivityID) 
        .WithMany() 
        .Map(m => 
        { 
             m.ToTable("RelatedActivities"); 
             m.MapLeftKey("TableActivityID"); 
             m.MapRightKey("RelatedTableActivityID"); 
        });

    }

I want to add 2 spesific constraints:

A TableActivity Object can't have itself listed in the RelatedActivities
For example if we have 2 TableActivity objects o1 and o2 if o1.relatedActivities.contains(o2) then o2.relatedActivities can't have o1 listed

How to achieve this two constraints ?

What I have Done :

I created a helper class that inherits ObservableCollection and I managed the add methode.

public class RelatedTableActivityCollection :ObservableCollection<TableActivity>
{


    private TableActivity m_Owner;
    public RelatedTableActivityCollection(TableActivity owner)
    {
        this.m_Owner = owner;
    }

    public new void Add(TableActivity item)
    {
        if (m_Owner.Equals(item)) {
            return;
        } else {
            base.Add(item);
        }
    }

}

The problem with this solution is that it works fine on the application but nothing is done in the database side. Is there a way to make the database take consideration of this changes ?

score 0 · Accepted Answer · edited May 23 '17 at 11:45

On the database side you can enforce your restrictions with check constraints. You have not specified which DBMS you are using, but in SQL Server, the first check constraint is simple:

alter table RelatedActivities add constraint CK_RelatedActivity check (TableActivityId <> RelatedTableActivityId)

The second condition is more complex because you have to inspect other entries in the table. That is possible, but you need a scalar function that does that (with schemabinding). You can call this function from a second check constraint.

Your problem is that with code first you can not easily add check constraint check this link to see how to make this work.

Thanks for your time and ur guidlines. – Ismail Jan 16 '17 at 11:31 — Ismail, Jan 16 '17 at 11:31

score 0 · Answer 2 · answered Jan 16 '17 at 11:30

On the database side I enforced two constraints (to answear the requirements), the first one is as @Sefe answered, the second one is by using a scalar function as @Sefe suggested

alter table RelatedActivities add constraint CK_RelatedActivity check (TableActivityID <> RelatedTableActivitiyID)

alter table RelatedActivities add constraint CK_RelatedActivityRelation check (dbo.CheckRelatedActivities(TableActivityID,RelatedTableActivitiyID)<1)

Definition of the Scalar function

CREATE FUNCTION [dbo].[CheckRelatedActivities](@TableActivityId int,@RelatedTableActivityId int)

RETURNS int
AS
BEGIN

    -- Declare the return variable here 
    DECLARE @Result int 
    -- Add the T-SQL statements to compute the return value here

    select @Result = count(1)
where @TableActivityId in (select RelatedTableActivitiyID from RelatedActivities where TableActivityID = @RelatedTableActivityId)


    RETURN @Result

END

EF self-referencing with constraints Many to Many

2 Answers2