Entity Framework - One to Many relation - mapping to only one element

.net c# entity-framework entity-framework-6 sql

Question

My example is like the following: I've a [Table1] which has one-to-many relation to [Table2].

Let say it looks like:

[Table1]:
    [Id] int

[Table2]:
    [Table1Id] int (foreign key to [Table1])
    [UniqueColumnAtTable2ForGivenTable1Id] int

[Table2] has indeed foreign key to [Table1], and it's possible to have multiple elements in [Table2], that has relation to [Table1].

I know how to define mapping between [Table1] and [Table2] from code, means:

public class Table1
{
    public int Id { get; set; }
    public virtual ICollection<Table2> Table2Objects { get; set; }
}
public class Table1_Mapping : EntityTypeConfiguration<Table1>
{
    public class Table1()
    {
        this.HasKey(x => x.Id);
        this.HasMany(x => x.Table2Objects).WithOptional().HasForeignKey(x => x.Table1);
    }
}

But is there a way to define Entity framework mapping, where I would specify One-to-many mapping, but only with mapping to one specific row from [Table2]? I do have [UniqueColumnAtTable2ForGivenTable1Id] column at [Table2] which is always unique for given [Table1] - therefore I know, that running something like:

SELECT * FROM [Table2] WHERE [Table1Id] = 123 AND [UniqueColumnAtTable2ForGivenTable1Id] = 456

will always result with one row queried.

From C# code what I need, is something like:

public class Table1
{
    public int Id { get; set; }
    public virtual ICollection<Table2> Table2Objects { get; set; }
    public virtual Table2 Table2Object456 { get; set; }
}
public class Table1_Mapping : EntityTypeConfiguration<Table1>
{
    public class Table1()
    {
        this.HasKey(x => x.Id);
        this.HasMany(x => x.Table2Objects).WithOptional().HasForeignKey(x => x.Table1);
        this.MagicMethod(x => x.Table2Object456).WithOptional().HasForeignKey(x => x.Table1).OtherMagicMethodWhichAllowsToPutFilter(x => x.UniqueColumnAtTable2ForGivenTable1Id == 456);
    }
}

Of course this fragment is what I need:

this.MagicMethod(x => x.Table2Object456).WithOptional().HasForeignKey(x => x.Table1).OtherMagicMethodWhichAllowsToPutFilter(x => x.UniqueColumnAtTable2ForGivenTable1Id == 456);
1
0
2/8/2019 2:22:37 PM

Popular Answer

In my opinion, a good option is not to do this, but just create a method in a repository which handles this special case:

public class YourRepository
{
    public Table2 Table2WithUniqueColumn456(Table1 table1)
    {
        using var context = new Table1Context();
        return context.Entry(table1)
                      .Collection(t1 => t1.Table2Objects)
                      .Query()
                      .FirstOrDefault(t2 => t2.UniqueColumnAtTable2ForGivenTable1Id == 456);
    }
}

Now, you can explicitly call this method every time you need to get a Table2 with this value being equal to 456.

If you are asking for curiousity, then one bad option would be to utilize inheritance:

public class Table1
{
    public int Id { get; set; }
    public virtual ICollection<Table2> Table2Objects { get; set; }
    public virtual Table2WithUniqueColumn456 Table2Object456 { get; set; }
}

[Table("Table2")]
public class Table2
{
    public int Id { get; set; }
    public int Table1Id { get; set; }
    public int UniqueColumnAtTable2ForGivenTable1Id { get; set; }
}

[Table("Table2")] // this will tell EF to use table-per-hierarchy
public class Table2WithUniqueColumn456 : Table2
{
}

public class Table1_Mapping : EntityTypeConfiguration<Table1>
{
    public class Table1()
    {
        this.HasKey(x => x.Id);
        this.HasMany(x => x.Table2Objects).WithOptional().HasForeignKey(x => x.Table1);
        this.HasOne(x => x.Table2Object456).WithOptional().HasForeignKey(x => x.Table1);
    }
}

As you can see:

  • You need to handle the creation logic and make sure that all objects with UniqueColumnAtTable2ForGivenTable1Id 456 are created as Table2WithUniqueColumn456
  • UniqueColumnAtTable2ForGivenTable1Id should be immutable or it may bring much more complex logic of editing
  • It creates additional column in Table2

Perhaps, if there are not so many Table2 objects per Table1, this could be even better way than inheritance, even though it is not that effective:

public class Table1
{
    public int Id { get; set; }

    public virtual ICollection<Table2> Table2Objects { get; set; }

    [NotMapped]
    public virtual Table2WithUniqueColumn456 Table2Object456 
    {
        get { return Table2Objects.FirstOrDefault(x => x.UniqueColumnAtTable2ForGivenTable1Id == 456; }
    }
}
0
2/8/2019 2:39:06 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