Entity Framework Core Query Type not mapping results to object

Question

I am trying to execute a sql query using the new DBQuery features in Entity Framework Core 2.1. I have an entity in my model that looks something like this:

public class Ord
{
    public Guid Id {get; set;}
    public Guid ColumnOne {get; set;}
    public Guid ColumnTwo {get; set;}
}

I have created an object called TestQuery which looks like this:

public class TestQuery
{
    public Ord PatientOrder {get; set;}
}

I have added a new DBQuery to my database context class like this:

public DbQuery<TestQuery> TestQuery { get; set; }

Then, I attempt to execute a query using FromSql like so:

var query = "select PatientOrder.Id as PatientOrderId,
                    PatientOrder.ColumnOne as PatientOrderColumnOne,
                    PatientOrder.ColumnTwo as PatientOrderColumnTwo
             from Ord PatientOrder"

var test = await _context.TestQuery.FromSql(query).ToListAsync();

The list test has the exact number of results that I would expect. However, each TestQuery object just has a null PatientOrder property. So it appears that the query is running, and returning results, but not mapping the results to the PatientOrder property.

Is there a step that I am missing in order to get this data to map to my object correctly?

I don't know enough to give as an answer, but shouldn't your query aliasing be PatientOrder.Id as Id, PatientOrder.ColumnOne as ColumnOne? It can't map because you're aliased names are not the same names as the properties. Plus it seems odd that you're trying to map to an object that contains the actual properties as a subobject. Shouldn't it be at the parent level? — gilliduck
If I alias PatientOrder.Id as Id, entity framework throws the error "The required column 'PatientOrderId' was not present in the results of a 'FromSql' operation." So I know EF is explicilty expecting that naming convention. — James Hogle
The question is, what's the purpose of TestQuery? i.e., why not DbQuery<Ord> with Ord columns inside SQL? — Ivan Stoev
Ok, so Ord is actually an entity. Hmm, interesting, I'm not sure this is supported. — Ivan Stoev
@gilliduck Dapper would have the same exact issue. The issue being you cannot map the class Ord to a class TestQuery. Meaning the query is returning a Ord class, but you're asking for a TestQuery, it makes no sense. — Erik Philips

Erik Philips Erik Philips · Accepted Answer · 2019-02-22T19:15:05

You cannot do exactly what you are doing per the documentation:

Excerpt:

The SQL query cannot contain related data. However, in many cases you can compose on top of the query using the Include operator to return related data (see Including related data).

The following is related data:

public Ord PatientOrder {get; set;}

Entity Framework Core Query Type not mapping results to object

2 Answers