CODEWITHSUNDEEP
c#.netentity-framework
user3362714
user3362714

Reputation: 159

DbContext.Set<T>().SqlQuery Loading related entities

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

Upvotes: 2

Views: 1777

Answers (1)

StuartLC
StuartLC

Reputation: 107237

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 :)

Upvotes: 1

Related Questions