How can you add a navigation property on a view in EF 6.1 CodeFirst

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.

Bortz

This question is changing all the time. Not clear and not very stimulating to answer it. – Waxen

@GertArnold, The question didn't change if you ask me? i only tried to improve the exlanation. The question in once sentence: "How can you add a navigation property on a view in EF 6.1 CodeFirst". I improve my question to get anwsers.. And to show the things that i already tried to avoid anwser that tell things i already know.. – Bortz

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;}

Turbit

Thanks for your answer @Elio.Batista, but this is the part i already know. The hard thing is that when i make a navigation property in code, as i show in EDIT3 of my original post. EF thinks on a add-migration that it needs to make foreign keys on the view. But a view can't have foreign keys as far as i know? because it's not a schema no? – Bortz

Maybe you can indeed tell me how it works, but could you eleborate your anwser with information on how to map it.. It seems that, that is the part i'm missing. – Bortz

Well, you already know the hard part. After you add-migration just go and delete the EF generated code (CreateIndex and AddForeignKey) for those columns from Up and Down methods and then run update-database as usual. Those methods are there to be modified to fill our needs, they are not written in stone. – Turbit

I did what you've said and load my view, include the FKTable in my Linq to entities query.. but nothing happens.. the Navigation property stays empty. – Bortz

In your MyView1 entity, are you pointing to the id of table2?. I have edited the answer using annotations. – Turbit

I just fixed it while Switching my one-to-one or zero relation from side. And making the connection true fluent API. SO thanks a lot! And i'm sure that the anwser above also works.. So in combination with the comments, this anwser certainly is the one for me! Thanks!. – Bortz