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
– gilliduck, Commented Feb 22, 2019 at 18:55
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
– James Hogle, Commented Feb 22, 2019 at 19:00
The question is, what's the purpose of TestQuery? i.e., why not DbQuery<Ord> with Ord columns inside SQL? — Ivan Stoev
– Ivan Stoev, Commented Feb 22, 2019 at 19:03
Ok, so Ord is actually an entity. Hmm, interesting, I'm not sure this is supported. — Ivan Stoev
– Ivan Stoev, Commented Feb 22, 2019 at 19:07
@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, Commented Feb 22, 2019 at 19:10

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

1

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

answered Feb 22, 2019 at 19:15

Erik Philips

54.8k11 gold badges133 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

alans · Accepted Answer · 2019-02-22 19:33:07Z

0

If you're doing a query on TestQuery which has related data PatientOrder, you should use Include.

https://learn.microsoft.com/en-us/ef/core/querying/raw-sql#including-related-data

var query = "/* do your query, but include the PatientOrderId */";
var test = await _context.TestQuery.FromSql(query).Include(t=>t.PatientOrder).ToListAsync();

edited Feb 22, 2019 at 19:33

answered Feb 22, 2019 at 19:11

alans

1,0509 silver badges17 bronze badges

Collectives™ on Stack Overflow

Entity Framework Core Query Type not mapping results to object

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related