DbContext.Set().SqlQuery Loading related entities

Question

I am running the below code:

var paramUserId = new SqlParameter
{
    ParameterName = "userId",
    Value = userId
};

string query = string.Format("{0} {1}",
              "usp_GetItems",
              "@userId");

var results = _context.Set<Item>().SqlQuery(query, paramUserId);

The usp_GetItems is a stored procedure I have. However my navigation properties are not being loaded. Is there anyway to acomplish this on Entity Framework?

Because according to this question Eager loading in EntityFramework with DbContext.Database.SqlQuery looks like it's possible.

Thanks

No, this [doesn't seem possible](http://stackoverflow.com/questions/7581352/ef-4-1-dbcontext-sqlquery-and-include) with `SqlQuery`. Can you unwrap your stored proc back into navigable DbSets? — StuartLC, Dec 16 '14 at 09:11
Have a read of http://stackoverflow.com/questions/1035677/using-a-stored-procedure-in-entity-framework-how-do-i-get-the-the-entity-to-hav — Paul Zahra, Dec 16 '14 at 09:16
@PaulZahra The information on that question is from 2009. Is the answer up to date? Microsoft already release several improvements of EF, and according to http://stackoverflow.com/questions/9136923/eager-loading-in-entityframework-with-dbcontext-database-sqlquery it's possible. — user3362714, Dec 16 '14 at 09:20
@user910683 It's working this way. I guess the issue is not there :) — user3362714, Dec 16 '14 at 09:25
So when you have the results populated can't you just do an include to get the navigation properties? — Paul Zahra, Dec 16 '14 at 09:36
Would that include the related entities of all records gathered from database on one shot? — user3362714, Dec 16 '14 at 10:41

score 1 · Answer 1 · answered Dec 16 '14 at 10:49

Assuming that the Stored Proc returns a denormalization of Items and Users, one idea comes to mind is to use a projection DTO which mimics the structure of the results of the stored procedure, and then to use the Context.Database.SqlQuery<T> flavour of SqlQuery to project into a flattened DTO.

You can then use LINQ to again re-normalize the result set back into your entity graph, which I guess was the original intention.

In Detail

Assuming the existing entities from your EF Model:

public class Item
{
    public int ItemId { get; set; }
    public string Name { get; set; }
}

public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }
    public ICollection<Item> Items { get; set; }
}

Create a transfer DTO which represents the flattened structure of your Stored Proc result set:

public class UserItemProcDto
{
    public int ItemId { get; set; }
    public string ItemName { get; set; }
    public int UserId { get; set; }
    public string UserName { get; set; }
}

Then project into the denormalized DTO, and finally renormalize using LINQ:

var results = _context.SqlQuery<UserItemProcDto>(query, paramUserId);

var usersWithItems = results.GroupBy(r => r.UserId)
    .Select(g => new User
    {
        UserId = g.Key,
        Name = g.First().UserName,
        Items = g.Select(i => new Item
        {
            ItemId = i.ItemId,
            Name = i.ItemName
        }).ToList()
    });

This is obviously not something you would want to do for many stored procedures which span multiple tables, of course :)

One thing to note is that if there is a reverse entity navigation of `Item.User`, that you would likely need to do this as a second discrete step - AFAIK there is no way to link both references during the creation projection. — StuartLC, Dec 16 '14 at 11:37

DbContext.Set().SqlQuery Loading related entities

1 Answers1