0

I have an application that is written on the top of ASP.NET MVC 5 framework along with Entity 6 Framework. I am using Database-first approach instead on code-first approach.

I am aware on how to build simple relation between my models using virtual navigations like public virtual RelationModel Relation { get; set } or public virtual ICollection<RelationModel> Relations { get; set }

However, the requirement is more tricky this time. I need to be able to build a relation using composite key not just a single column. I want to add a relation where the joiner/join-clause on one side should be a computed property called LocalDate and DateOf on the other side AND OwnerId column == UserId of the other side.

Here is an example of my models

My parent model looks like the following

public Entry 
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    [ForeignKey("Owner")]
    public int OwenerId { get; set; }
    public DateTime StartedAt { get; set; }
    public int UtcOffset { get; set; }

    public virtual User Owner { get; set; }

    // This puts the StartedAt in the correct date state which is needed for the relation
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public DateTime LocalDate 
    {
        get
        {
            return StartedAt.AddSeconds(UtcOffset * -1).Date;
        }
    }

    // This is the relation that needs a complex join clause
    public virtual ICollection<Bucket> Buckets { get; set }
}

Here is my child model looks like the following

public Bucket 
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }

    public int UserId { get; set; }
    public DateTime DateOf { get; set; }  //This is a date it or stored with 00:00:00.000 time in the database

    public virtual User Owner { get; set; }
}

From my Entry model, I want to be able to access my Buckets relations using the following logic Entry.LocalDate == Bucket.DateOf && Entry.UserId == Bucket.UserId

Note that the LocalDate property is NOT a database column, rather a computed property

Is it possible to construct this kind of relation between my model where I can use .Include(x => x.Buckets) to get the relations accordingly? If so, how? If it is not possible, what are other ideas that can be used to deliver the same results?

Junior
  • 11,602
  • 27
  • 106
  • 212
  • I'm a bit unsure about the `DatabaseGeneratedOption` part, but I'd imagine you'd need to create a [composite foreign key](https://stackoverflow.com/questions/14873169/creating-composite-key-entity-framework) and link properties this way. Since I'm not familiar with the `DatabaseGeneratedOption`, I'm hesitant to post an answer since I don't know if it will still work. If you don't get any good answers in the meantime, I'll make an attempt later. – C. Helling Aug 04 '17 at 21:39
  • Computed option just tells the EF not to update the column, because I will compute a value myself. In my case, it mat not be needed but never tested it without it. Entity should be smart enough not to update the value sure the `set` method does not exists. Regarding the composet foreign key I doubt that Linq will allow me to use this type of a property. I think Linq needs actual DB-columns and not a virtual one. Either way, I am willing to try it, but how I can tell entity what column to join on on the other side (aka on the `bucker` model??? – Junior Aug 04 '17 at 21:53
  • It will match up with the `[Key]` attributes on the target model. – C. Helling Aug 04 '17 at 21:56

0 Answers0