SqlQuery into a [NotMapped] field?

Question

I have an entity that is working great, but then I have a requirement to tack on an additional property that comes from another table. I don't have the ability to make a View, so I want to just add a field that is [NotMapped] then use the Context.Database.SqlQuery to execute my custom statement and return all the normal fields and this new field.

In other words something like this:

public class Employee
{
    public int EmployeeId { get; set; }
    public string EmployeeName { get; set; }
    [NotMapped]
    public string CustomerName { get; set; }
}

public List<Employee> GetEmployees()
{
    using (MyContext context = new MyContext())
    {
        return context.Database.SqlQuery<Employee>("select E.EmployeeId, E.EmployeeName, C.CustomerName from Employee E left join Customer C on E.CustomerId = C.CustomerId").ToList();
    }
}

Yes yes, not the best example, but the simplest way to get the point across. From everything I've been reading, SqlQuery is supposed to ignore attribute, so this should work, but my CustomerName is always coming back null (I've run the SQL in Management Studio, it has a value there, just not after EF deserializes into my objects).

What do I need to do to get this to work? Or can I? Was EF changed to not allow this?

-shnar

Answer 1

I had the same trouble using stored procedures to do selects with calculated fields. I created a view model that looks exactly like my entity without the db annotations. Then after I call my stored procedure using the view model I select into my entity. So, using your example above:

public class EmployeeVM
{
    public int EmployeeId { get; set; }
    public string EmployeeName { get; set; }
    public string CustomerName { get; set; }
}

Then you can call:

public List<Employee> GetEmployees()
{
    using (MyContext context = new MyContext())
    {
        return context.Database.SqlQuery<EmployeeVM>("select E.EmployeeId, E.EmployeeName,
 C.CustomerName from Employee E left join Customer C on E.CustomerId = C.CustomerId")
    .Select(x=> new Employee(){
        EmployeeId = x.EmployeeId,
        EmployeeName = x.EmployeeName,
        CustomerName = x.CustomerName
        }).ToList();
    }
}

Hope this helps.

Answer 2

Add following to DbCotext.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Query<EmployeeExtension>();
}

And use following to execute query or stored procedure.

_context.Query<EmployeeExtension>().FromSql(query/storedProcedure, parameters)

Answer 3

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public string CustomerName { get; set; }

It appears that this annotation does the trick, in that the field gets filled by SQLQuery and there's no complaint during an update (DbContext.SaveChanges()), even if the column is not present in the Db table.

Whereas a similar annotation DatabaseGenerated(DatabaseGeneratedOption.Computed) expects to find such a column in the table.