CODEWITHSUNDEEP
c#sqlentity-framework-core
James Hogle
James Hogle

Reputation: 3040

Entity Framework Core Query Type not mapping results to object

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?

Upvotes: 0

Views: 2854

Answers (2)

alans
alans

Reputation: 1040

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

Upvotes: 0

Erik Philips
Erik Philips

Reputation: 54618

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

Upvotes: 1

Related Questions