Entity Framework 6 code-first stored procedure map to complex objects

c# ef-code-first entity-framework-6 stored-procedures

Question

How do I use a stored procedure to get a parent and its children?

A generic version of my code is this:

public class Parent
{
    public Int32 ParentId { get; set; }
    public Guid ParentGuid { get; set; }
    public String Name { get; set; }
    public virtual ICollection<Child> Children { get; set; }
}

public class Child
{
    public Int32 ChildId { get; set; }
    public String Name { get; set; }
    public Int32 ParentId { get; set; }
    public virtual Parent Parent { get; set; }
}

public class ParentMapping : EntityTypeConfiguration<Parent>
{
    public ParentMapping()
    {
        ToTable("Parents");
        HasKey(t => t.ParentId);
    }
}

public class ChildMapping : EntityTypeConfiguration<Child>
{
    public ChildMapping()
    {
        ToTable("Children");
        HasKey(t => t.ChildId);
        HasRequired(t => t.Parent).WithMany(t => t.Children);
    }
}

I am trying to call a stored procedure and get the parents children as well:

_context.Database.SqlQuery<Parent>("EXEC [dbo].[GetParent] @ParentGuid", new SqlParameter("@ParentGuid", parentGuid)).SingleOrDefault();

The stored procedure is a simple select:

SELECT 
    P.*,
    C.*
FROM 
    [dbo].[Parents] P
INNER JOIN 
    [dbo].[Children] C ON C.[ParentId] = P.[ParentId]
WHERE 
    P.[ParentGuid] = @ParentGuid

Right now, it is only mapping the Parent's data to the object. The Children collection is null. What do I need to do to get this populated?

1
0
3/12/2017 4:00:10 PM

Accepted Answer

If you use context.Database.SqlQuery the entities are untracked, just returned as pure data models. If you want to resolve the children (via lazy loading) then you need to query for them in a way that also adds the objects to EF object graph:

_context.Set<Parent>().SqlQuery(...)

By running the sql query against the DbSet it will track them by default and the lazy-loading should now work.

1
3/7/2017 2:13:39 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow