Converting complex result set from stored procedure into entities and their children

entity-framework entity-framework-6

Question

I have a stored procedure that returns a series of joined table results as one of several result sets, ala:

SELECT 
    a.*,
    at.*
FROM
    dbo.UserProfile up
JOIN
    dbo.Players p ON up.UserId = p.UserId
JOIN
    dbo.PlayerAbilities pa ON p.PlayerId = pa.PlayerId
JOIN
    dbo.Abilities a ON pa.AbilityId = a.AbilityId
JOIN
    dbo.Attributes at ON a.AttributeId = at.AttributeId
WHERE
    up.UserName = @userName

My entity class structure for the Ability class is as follows:

class Ability
{
    /* Standard Properties */

    public Attribute Attribute { get; set; }
}

When I call this stored procedure with code:

//Establish that var reader = (stored procedure).ExecuteReader()
var abilities = ((IObjectContextAdapter)context)
                        .ObjectContext
                        .Translate<Ability>(reader);

I get a collection of Ability objects but their Attribute property is not populated. How can I modify my stored procedure or instruct entity framework to allow for this property to be populated?

EDIT: Attribute and Ability Table schemas as requested:

TABLE Attributes
{
    AttributeId INT PKEY
    AttributeName VARCHAR(50)
}
TABLE Abilities
{
    AbilityId INT PKEY
    AbilityName VARCHAR(50)
    AbilityDescription VARCHAR(500)
    AttributeId INT FKEY => Attributes.AttributeId
    ClassificationId INT FKEY => Classifications.ClassificationId
    BaseDamage INT
    LevelRequired INT
    PriceId INT FKEY => Prices.PriceId
    RecordStatusId INT FKEY => RecordStatuses.RecordStatusId
}

Forgot to add this in the original edit:

class Attribute
{
    public int AttributeId { get; set; }
    public string AttributeName { get; set; }
    public ICollection<Ability> Abilities { get; set; }

    //Never used, doesn't matter for lazy loading though, never got around to deleting
    public virtual ICollection<Monster> Monsters { get; set; }
    public virtual ICollection<Monster> Monsters1 { get; set; }
}

I didn't originally include classification/price/recordstatus column information in the above properties problem because I though it would be redundant, but I've included them here in case it's relevant.

1
1
5/10/2014 7:21:04 PM

Popular Answer

Translate does not know how to deal with navigation properties but if query attributes in a separate query and EF should fix up relationships.

0
5/11/2014 3:32:39 AM


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