SqlQuery in the [NotMapped] field?

Question

I have an entity that works fine, but then I have a requirement to stick to the extra property that comes from another table. I have no way to pretend, so I just want to add the [NotMapped] field and then use Context.Database.SqlQuery to execute my user statement and return all 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 easiest way to get the point. From everything I read, SqlQuery should ignore the attribute, so it should work, but my CustomerName always returns null (I ran SQL in Management Studio, it matters there, but not after EF deserializes into my objects).

, ? ? EF , ?

-shnar

Answer 1

, . , , db. , , , . , :

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

:

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

, .

Answer 2

DbCotext.

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

.

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

Answer 3

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

It seems that this annotation does its job in that the field is populated with SQLQuery and there are no complaints during update ( DbContext.SaveChanges()), even if the column is not in the Db table.

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