How can you add a navigation property on a view in

Question

Let's make a case to explain my problem.

---

MyTable1

+id

+myTable2Id

---

MyTable2

+id

---

MyView1

+id

+myTable2Id

---

MyView1 exists in the case, from data from the MyTable1. Now i want to create a Navigation property from my EF6.1 Code first approach in my View to MyTable2.

I know that it was possible from the database first approach, but is it also possible from the code-first approach and how?

EDIT:

I search some on internet, but due many meanings of the word View, it's very hard to find information on it.

Also with the approaches in codes that i tried, i always get an error that the migration can't be completed. Because the Migration tries to add an foreign key to the view, which isn't possible.

EDIT2:

To elaborate a bit more on my explanation. I want to be able to approach it in code the following way:

Guid table2Id = context.MyView1.FirstOrDefault().MyTable2.id;

EDIT3:

I will eleborate a bit more, to see if i can get my problem better explained.

When i added the following to my view Entity:

public virtual MyTable2 Table2 { get; set;}

EF will automaticly generate the following migration:

public override void Up() {
    CreateIndex("MyView1", "MyTable2Id");
    AddForeignKey("MyView1", "MyTable2Id", "MyTable2", "id")
}

Which on running update-database gives the following error :

"Cannot create index on view 'MyView1' because the view is not schema bound"

EDIT4:

With help of the comment that the migration aren't of stone.. and are changeable i made it.

I used the following fluentAPI:

    // Map one-to-zero or one relationship 
    modelBuilder.Entity<MyTable2>()
        .HasRequired(t => t.MyTable1)
        .WithOptional(t => t.MyTable2);

    modelBuilder.Entity<MyTable1>()
        .HasOptional(t => t.MyTable2);

And changing my tables to this: (The FK to the MyTable2 and removed from the view)

---

MyTable1

+id

---

MyTable2

+id +myTable1

---

MyView1

+id

---

Which in the end is better because this way i have less Null values in my model.

Answer 1

In EF you can use a database views and map it to an entity and reference it just as you do with tables. For code first process you have to create the View in Up and drop it in Down methods from migration class:

public partial class AddView : DbMigration
  {
    public override void Up()
    {
      this.Sql(@"CREATE VIEW MyView1 AS ....");
    }
    public override void Down()
    {
        this.Sql(@"DROP VIEW MyView1....");
    }
  }

EDIT:

public long myTable2Id { get; set; }

[ForeignKey( "myTable2Id" )]
public virtual MyTable2 Table2 {get;set;}