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.
Translate does not know how to deal with navigation properties but if query attributes in a separate query and EF should fix up relationships.