CODEWITHSUNDEEP
entity-framework
user1603794
user1603794

Reputation: 121

Map sql view to existing entity in EF 6?

I have a table dbo.Tasks, class for Task, a TaskMap, and add it to the DB context no problem. I also have a database view dbo.vwComplexTaskQuery that returns Tasks. How do I map this view to my task class? I would like to be able to do:

List<Task> Tasks = db.vwComplexTaskQuery.ToList();

Without having to create a vwComplextTaskQuery class that has the same properties and same data annotations.

Simplified Task Class

public class Task
{
    public int TaskId { get; set; }
    public string Title { get; set; }
}

Simplified Task Map Class

        public TaskMap()
    {
        // Primary Key
        this.HasKey(t => t.TaskId);
        this.Property(t => t.Title)
            .IsRequired()
            .HasMaxLength(50);

        // Table & Column Mappings
        this.ToTable("Tasks");
        this.Property(t => t.TaskId).HasColumnName("TaskId");
        this.Property(t => t.Title).HasColumnName("Title");

    }

Is this possible, if so an example or link to documentation would be great.

Upvotes: 2

Views: 1112

Answers (2)

Dai
Dai

Reputation: 155443

Almost 4 years later... (EF6 has been out that long already? wow...)

EF does not handle VIEWs very well by default: both the EDMX ("Database first") and "Code first" reverse-engineering stuff makes some silly assumptions about views (e.g. that a View can't have the same type as an Entity, a View's Entity doesn't have relationships with other Entities, all of a View's NOT NULL columns must be a composite primary key, etc...

...but if you manually override whatever EF generates such that it thinks a VIEW is a table then it works perfectly correctly (navigation properties, etc). But doing this is kinda difficult and needs to be re-done whenever you update your Model from the database.

Fortunately there's a solution - if you don't mind getting your hands just a bit dirty: There's a third-party, open-source, Entity Framework model generator T4 template (no VS Extensions required: just include 3 files in your project (also available as a NuGet package) called ReversePOCO: https://github.com/sjh37/EntityFramework-Reverse-POCO-Code-First-Generator (disclaimer: I contributed some patches a few months ago specifically to support VIEW handling).

By default, the T4 template in ReversePOCO generates code similar to what EF6's Code First generator creates, but handling VIEWs is straightforward:

  1. In your Database.tt file (where you configure ReversePOCO's Settings object) ensure that Settings.IncludeViews = true.
  2. Look for the Settings.ViewProcessing callback (around like 310 in version 2.37.1).
    • Uncomment the existing code and modify it to suit your preferences. You will need to list which columns in the view are primary keys.
  3. Look for the Settings.AddForeignKeys callback (around line 326 in version 2.37.1).
    • Uncomment the existing code and modify it to match the foreign-key relationships you'd like. This will ensure the Navigation properties are created correctly (composite foreign-keys are fully supported).

Upvotes: 1

user1603794
user1603794

Reputation: 121

I found one solution:

List<Task> tasks = db.Tasks.SqlQuery("Select * from vw_AllTasks").ToList<Task>();

Upvotes: 4

Related Questions