Entity Framework Core Query Type not mapping results to object

c# entity-framework-core sql

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?

1
0
2/22/2019 7:08:46 PM

Accepted Answer

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;}
1
2/22/2019 7:15:05 PM

Popular Answer

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

https://docs.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();


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow