Efcore: Support "Include" when using stored procedure in FromSql

[dealdiane]

Suppose we have a DTO:

public class Person
{
    public string FirstName { get; set; }
    public int Id { get; set; }
    public string LastName { get; set; }
    public Pet Pet { get; set; }
    public int PetId { get; set; }
}

public class Pet
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Currently running the code below throws "The Include operation is not supported when calling a stored procedure."

db.Set<Person>().Include(p => p.Pet).FromSql("[dbo].[GetPersonsWithPets] @sortby = {0}", sortBy)

whereas if you use "SELECT Person.*, Pet.Name FROM Person INNER JOIN Pet on Person.PetId = Pet.Id", it works.

Can we get the same support for stored procedures? If they both return the same set of columns, it shouldn't be an issue, should it?

[divega]

@opiants something like this works:

``` C#
var q = db.Set().Include(p => p.Pet).FromSql("SELECT * FROM People");

But I don't think it does what you think it does. Here the `SELECT` statement only replaces the SQL that we would have generated for the `db.Set<Person>()` part of the query, not what we would generate for the whole of `db.Set<Person>().Include(p => p.Pet)`. 

We understand that this is counterintuitive and for this reason we have talked about constraining `FromSql()` so that you can only invoke it directly after the call to `Set()` instead of being able to chain it anywhere in the query (cc @anpete @mikary). E.g. you would be forced to write it like this, which is clearer:

``` C#
var q = db.Set<Person>().FromSql("SELECT * FROM People").Include(p => p.Pet); 

Besides that, we can make Include() work when composable SQL (i.e. SELECT) statements are passed to FromSql() by layering any necessary filters and JOINs on top of them, but that isn't true for stored procedures.

I would recommend that for this scenario you just execute two separate queries for pets and person and let EF fix up the navigation properties.

We don't actually have the ability to materialize nested object graphs from a single SQL statement(composable or not).

[dealdiane]

I would recommend that for this scenario you just execute two separate queries for pets and person and let EF fix up the collection.

How would you do this? Can you chain a stored procedure and a SELECT or through linq joins?

[maumar]

EF will automatically wire up the references between entities that have been queried for

[divega]

Assuming you have two stored procedures with the same filter criteria:

C# db.Set<Pets>().FromSql("[dbo].[GetPetsForPersons] @lastName = {0}", lastName).Load(); var persons = db.Set<Person>().FromSql("[dbo].[GetPersons] @lastName = {0}", lastName);

when iterating over persons you will see that the references on each person point to the right pets.

BTW, with SQL Server you should be able to write queries against Table-Valued Functions as well.

But fundamentally you cannot do this with a single query.

[newdygo]

@divega Thanks. this aproach worked for me.

[Grasher134]

Actually for me

var q = _context.People.FromSql("SELECT * FROM People").Include(p => p.Pet);

is not working. I only can get related property if I use Includes BEFORE FromSql(). EF Core 2.1.302