none
Entity Framework 6.x - Read with Stored Procedures RRS feed

  • General discussion

  • Hi,

    Is it possible to populate a object, which is a navigation property of another POCO, using a Stored Procedure.

    ex:  I have 2 entity/Poco classes as below.

    using System; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema;

    namespace CardSys.Entities.EntityDomain { public class Address:IEquatable<long> { [Key] [DatabaseGenerated(DatabaseGeneratedOption.Identity)] [Column("AddressId")] public long AddressId { get; set; } [MaxLength(100)] public string AddressLine1 { get; set; } [MaxLength(100)] public string AddressLine2 { get; set; } public bool Equals(long other) { return other == AddressId; } } }

    using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data.Entity.Infrastructure.Annotations; using System.ComponentModel.DataAnnotations.Schema;

    namespace CardSys.Entities.EntityDomain { public enum AddressType { Primary =1, Secondary =2 } public class EntityAddress : IEquatable<long> { public long EntityAddressId { get; set; } public AddressType AddressType { get; set; } public long EntityId { get; set; } public long AddressId { get; set; } [ForeignKey("AddressId")] public virtual Address Address { get; set; } public bool Equals(long other) { return other == EntityAddressId; } } }


    I have a Stored Procedure as follows which has an inner join query to populate the EntityAddress object's primitive properties and the primitive properties inside the Address property of that object.
    CREATE PROCEDURE EntityAddressGetByEntityId
    	@entityId int
    AS
    BEGIN
    	SELECT a.*,ea.* 
    	FROM EntityAddress ea inner join 
    	     [Address] a On ea.AddressId = a.AddressId
    	WHERE ea.EntityId = @entityId
    
    END
    GO

    I have a following code to populate the EntityAddress object calling the above SP.
       var entityAddressList = context.EntityAddress
                    .SqlQuery("EntityAddressGetByEntityId @entityId", new SqlParameter("Entityid", 2)).ToList();

    But it doesn't populate the Address object in the EntityAddress object. Is there any straightforward method to do this ?




      




    Tuesday, February 2, 2016 8:27 AM