Key properties of all entity types must be mapped to the same non-nullable columns returned by the storage function

Question

I've just created a table-valued function as described here http://www.entityframeworktutorial.net/EntityFramework5/table-valued-function-in-entity-framework5.aspx (using EF6) and imported it into my edmx. After this I also changed the Returns a collection of to my entity MyTable (which is selected inside the function).

However, I always get the exception

The key properties of all entity types returned by the function import must be mapped to the same non-nullable columns returned by the storage function.

When I look into the generated complex result-object all properties are nullable but in my entity MyTable I want to map to they are not.

This is my function (to get all child-relations from a given entity except the entity itself):

CREATE FUNCTION [dbo].[fnGetChildren]
(   
    -- Add the parameters for the function here
    @Id uniqueidentifier
)
RETURNS TABLE 
AS
RETURN 
(
    -- Add the SELECT statement with parameter references here
    WITH childs  AS
    (
       SELECT * FROM MyTable WHERE Id = @Id
       UNION ALL
       SELECT MyTable.* FROM MyTable JOIN childs ON MyTable.ParentId = childs.Id
    )

    SELECT * FROM  childs WHERE Id <> @Id
)

The function works as expected when running inside sql-server. The edmx-generation also runs through. But, when trying to execute a query with the DbContext I got this error (no matter if I call the function-import or not).

Is this an issue with the function itself, EF or is there something other wrong with that?

Answer 1

You can use ISNULL for the key columns in your SELECT list to force EF to recognize the value as non-nullable:

WITH childs  AS
(
   SELECT Id FROM MyTable WHERE Id = @Id
   UNION ALL
   SELECT MyTable.Id FROM MyTable JOIN childs ON MyTable.ParentId = childs.Id
)

SELECT ISNULL(Id, 0) FROM  childs WHERE Id <> @Id

You won't be able to do this with SELECT *, but you shouldn't do that anyway.