0

Let's say I have a Dependents table, like this

Id | Name | EmployeeId
1  | Luke | 1
2  | Leia | 1
3  | Tim  | 4

And then I have an Employees table, like this

Id | EmployeeName | JobTitle
1  | Anakin       | Enforcer
2  | Tarkin       | Senior Vice President
3  | Palpatine    | CEO

For dependents, EmployeeId is a foreign key connecting them to the Employees table. Finally, I have an EmployeeDbo class, like this:

public class EmployeeDbo
{
    public int Id { get; set; }
    public string EmployeeName { get; set; }
    public string JobTitle { get; set; }
    public IEnumerable<DependentDbo> Dependents { get; set; }
}

So as you can see, the EmployeeDbo includes the dependents as a collection. Using LINQ and DbContext, getting all employees with a populated list would just be var results = _dbContext.Employees.Include(x => x.Dependents).ToList()

Here's my problem. I'm using a stored procedure to select the data I want. The actual data is much more complicated, but here's what my code calling the stored proc looks like:

var employees = 
    _dbContext.Database.SqlQuery<EmployeeDbo>(@"[GetEmployees] @someParameter", sqlParam);

This returns an object of type DbRawSqlQuery<EmployeeDbo>. When I call ToList() the actual SQL call is performed, but it does NOT populate my dbo's IEnumerable<Dependent> collection, it just comes back as null. I can't do Include() on the DbRawSqlQuery.

What are my options for populating my Dbo's Dependent collection with this call to the stored proc?

Andrio
  • 1,852
  • 2
  • 25
  • 54
  • Does this answer your question? [Handle multiple result from a stored procedure with SqlQuery](https://stackoverflow.com/questions/25304791/handle-multiple-result-from-a-stored-procedure-with-sqlquery) – Charlieface Mar 25 '21 at 02:30
  • By the way, could be wrong but don't think that is the correct way to execute a stored procedure (I think it works just inefficient as it's not doing a direct RPC call on that proc, it's executing a separate batch that calls it) – Charlieface Mar 25 '21 at 02:31
  • @Charlieface The stored procedure in question does not return multiple result sets, but maybe I can modify it to return the data I'm asking for. I'll take a look at that, thanks. – Andrio Mar 25 '21 at 03:39
  • Attach output your stored proc – Zanyar Jalal Apr 04 '21 at 07:07

0 Answers0