Including navigation properties in EF Core when calling a stored procedure

I have a SQL Server stored procedure which is selecting entities from a table Records, and I have another table that has a many files for each Record entity in a one-to-many relationship.

I created the stored procedure, and it's selecting the data correctly, with a left join for the Files table. Now I want to fill the Files property for each record just like when I make the .Include(x => x.Files) in EF Core. It ends up being null all the time.

This is how I call the stored procedure:

const contentTypeParam = new SqlParameter("@ContentTypeId", contentTypeId);
const result = await _context.GetRecords
  .FromSqlRaw("EXEC GetListRecords @TypeId", contentTypeParam)
  .ToListAsync();

My stored procedure contains this SQL statement:

SELECT r.* 
FROM Records
LEFT JOIN Files f ON f.RecordId = r.Id;

Solution

Short Answer is "No".
An "entity framework---" .. (I'll use the term) "---work around" .. is that IF your stored procedure has MULTIPLE "SELECT" statements, you can begrudgingly map each ".Result" to some ef-entity.

I'll use a generic example.

stored procedure:

SELECT d.DepartmentKey, d.DepartmentName FROM dbo.Department d;

SELECT e.EmployeeKey, e.ParentDepartmentKey, e.LastName, e.FirstName FROM dbo.Employee e;

this returns TWO ".Results".

You can follow an article like this:

https://www.codemag.com/Article/2101031/Calling-Stored-Procedures-with-the-Entity-Framework-in-.NET-5

or you can internet search this phrase (below) and find multiple articles.

entity framework core "NextResult"

NOW, the difficult part with this is that.. your entity propery-name MUST MATCH EXACTLY, the column names(s) of your SELECT.

Aka, using the above example, your DepartmentEfEntity.cs .. MUST have property names called DepartmentKey, DepartmentName .. with no deviation.

This is why I don't like this work around.

Here is an additional link (albeit, not "core") about the object and column names .. "must be exact" : https://learn.microsoft.com/en-us/ef/ef6/modeling/designer/advanced/multiple-result-sets

If you have a SINGLE SELECT that has JOIN(s) clauses...you cannot do this with entity-framework (or at least to my knowledge).

AN OPTION is the Dapper "Split On"

https://www.learndapper.com/relationships#dapper-spliton

this will allow you to "map" 1:N "JOIN" statements into some entity-objects.

However, I think the same "ColumnName" to "PropertyName" .. exact match .. has to exist.

.......

Ultimately, you should ask yourself why you are using a stored procedure in the first place. "Because it already existed" is probably not a good reason IMHO.

What I do is .. "use EF . 'as it was intended'" (see "@Gert A" comment to your original question).......up to the point that I have a special case that I need PERFORMANCE. and then I put up with the obstacles/pains of calling a stored procedure directly.

I lean toward #2. I will have a stored procedure return multiple .Result(s) (and use the .NextResult). I've learned that this is more maintainable in the long run .. the issue with JOIN's is that

(1) you have repeated information (when you join department-table and employee-table into a single join'ed select, the departmentkey and departmentname will show up many times repeated.

and (2) when you need a new column..it requires changes to several places in the code.

I will go with #3 .. a handful of times. This is usually around a "specialized reporting situation"......

But it more maintenance with #2 and #3. I pay the maintenance price...when I need a specific situation .. usually around performance.

Hope that helps.

APPEND (to this answer):

Here is an excellent article that goes through many different options.

https://khalidabuhakmeh.com/multiple-result-sets-with-net-core-sql-server

Below is one paragraph from the above answer, in case the link above gets "reworked" over time...

In this post, we’ll be dealing with a simple database that consists of two tables: People and Food. There is a one-to-many relationship between people and food. The tables themselves are unremarkable, and for this post, I’m not even worried about the correctness of schema.