CODEWITHSUNDEEP
c#.netentity-framework
Umair
Umair

Reputation: 3243

Entity framework stored procedure - mapping complex properties

I have an edmx data store and I am trying to execute a stored procedure against it:

.
.
.
CustomerDb.ExecuteStoreQuery<Customer>("GetCustomers", parameters).ToList();

The customer class has the following structure

class Customer {
    public int Id { get; set; }
    .
    .
    .
    public Address Address { get; set; }
}

class Address {
    public int Id { get; set; }
    .
    .
    .
    public string PostCode { get; set; }
}

Now no matter what I do, the Address property is always null. I have tried to return the result set in different formats, but no matter what I do it is always null.

E.g.

SELECT c.Id, ..., a.PostCode
FROM Customer AS c
INNER JOIN Address AS a ON c.CustomerId = a.CustomerId
WHERE c.CustomerId = @CustomerId

or

SELECT c.Id, ..., a.PostCode AS 'Address.PostCode'
FROM Customer AS c
INNER JOIN Address AS a ON c.CustomerId = a.CustomerId
WHERE c.CustomerId = @CustomerId

or

SELECT c.Id, ..., a.PostCode AS 'Address_PostCode'
FROM Customer AS c
INNER JOIN Address AS a ON c.CustomerId = a.CustomerId
WHERE c.CustomerId = @CustomerId

But the columns are never picked up.

What am I doing wrong?

Thanks.

Upvotes: 2

Views: 2029

Answers (2)

granadaCoder
granadaCoder

Reputation: 27874

For future readers.

http://msdn.microsoft.com/en-US/data/jj691402

Multiple Results can populate this kind of object hierarchy.

Note:EF does not take any mapping into account when it creates entities using the Translate method. It will simply match column names in the result set with property names on your classes.

So it is very "picky" about the property names and the columns in the Select query(queries).

Here is an old school Northwind query to show how to get multiple objects using a single input parameter.

Use Northwind
GO

/*

Declare @OrderID int
select @OrderID = (select top 1 OrderID from dbo.[Order Details] )
EXEC dbo.uspOrderDetailsGetByKey @OrderID

*/


IF EXISTS 
    (
    SELECT * FROM INFORMATION_SCHEMA.ROUTINES  WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' and ROUTINE_NAME = N'uspOrderDetailsGetByKey'  
    )
BEGIN
    DROP PROCEDURE [dbo].[uspOrderDetailsGetByKey]
END


GO

CREATE Procedure dbo.uspOrderDetailsGetByKey (
@OrderID int
)
AS

BEGIN

    SET NOCOUNT ON

    /* Result #1 */
    SELECT 
        c.CustomerID, c.CompanyName,c.ContactName,c.ContactTitle,c.[Address],c.City,c.Region,c.PostalCode,c.Country ,c.Phone,c.Fax 
    FROM 
        dbo.Customers c 
        JOIN Orders o ON c.CustomerID = o.CustomerID 
    WHERE 
        o.OrderID = @OrderID

    /* Result #2 */ 
    SELECT o.OrderID,o.CustomerID,o.EmployeeID,o.OrderDate,o.RequiredDate,o.ShippedDate,o.ShipVia ,o.Freight,o.ShipName,o.ShipAddress,o.OrderID,o.CustomerID,o.EmployeeID,o.OrderDate 
    FROM 
        dbo.Orders o 
     WHERE 
        o.OrderID = @OrderID

    /* Result #3 */
     SELECT od.OrderID,od.ProductID,od.UnitPrice,od.Quantity,od.Discount 
     FROM 
        dbo.[Order Details] od 
     WHERE 
        exists (select null from dbo.Orders innerTable where innerTable.OrderID = od.OrderID and innerTable.OrderID = @OrderID )

    SET NOCOUNT OFF

END

GO

Upvotes: 0

Jurica Smircic
Jurica Smircic

Reputation: 6445

I don't think you can do it that way. According to MSDN

Each property of the type:

• Must have a setter.

• Must correspond to a primitive type in CSDL.

• Must correspond to a column name in the resulting DbDataReader (the provider implementation determines whether a column has the same name as the property). If the name of the type's property does not match a field of the DbDataReader, the Entity Framework materializes the default value of the property if it is defined in the conceptutal model.

Your Address is ComplexType in CSDL. I think you will have to construct your object with the type that can be materialized with ExecuteStoreQuery.

Upvotes: 2

Related Questions