Mapping a view and a table to the same model in EF?

Question

My Scenario

• I have a table of items, each of which has a priority and some other information.
• I also have a database view, which selects * from that table, but replaces the priorities based on some other attributes of the item
• Both the view and the table contain exactly the same fields, with only the content of the priorities changing.

In my code -- names changed to protect the innocent :) -- I have:

[Table("schema.Items")] //The table
public class Item
    {
      //...all of the fields that exist in both the table and the view.
    }

Question

• Given this scenario, is there a way for me to pull from the view instead of the table but map it to the same model naturally? If so, how do I do that?

Clarifications:

• The "view" I mention is a database view that reads from the table but interprets the priority differently.
  • So, in this situation, we'd like to just pull from the view.
• The database view and database table have the same fields.
• I'm trying to find a way to avoid having ClassA and ClassB, both with the same properties, just so I can pull one from the view and the other from the table.
• I am referring only to reading data. There is no expectation of an update here.

Answer 1

If I understand you correctly, you can use SqlQuery to load from a query that is generated from view.

var db = ...; // instance of context

var q = db.Set<ViewModel>().Where(...).ToString();
var result = db.Database.SqlQuery<TableModel>(q);

PS

If the view name has a pattern like view_TableName, you can just use TableModel to generate the query then replace the table name with view name.

Here is an extension method that could achieve that.

public static string GetViewSql<T>(this DbContext db, IQueryable<T> q)
    where T : class
{
    const string prefix = "view_";
    var tableName = Regex.Match(
        db.Set<T>().ToString(), 
        @"FROM (\[.*\]\.\[.*\]) AS \[Extent1\]").Groups[1].Value;
    var viewName = Regex.Replace(
        tableName, 
        @"\[.*\]\.\[(.*)\]", 
        m => m.Groups[0].Value.Replace(
            m.Groups[1].Value, prefix + m.Groups[1].Value));
    var sql = q.ToString().Replace(tableName, viewName);
    return sql;
}

Usage:

var query = db.Set<TableModel>().Where(...);
var sql = db.GetViewSql(query);
var result = db.Database.SqlQuery<TableModel>(sql);