Stored procedure whose result set lacks a primary key won't execute with Entity Framework 6

c# entity-framework entity-framework-6 stored-procedures

Question

I'm calling a stored procedure whose result set has no primary key, and no candidate keys. I created an entity called Foo to represent a record in the results set, so the stored procedure will result in a List<Foo>. The entity looks something like this:

public class Foo {
    public Guid FooID { get; set; }
    public Guid AnotherFooID { get; set; }
    public string FooString { get; set; }
}

When I attempt to call the stored procedure as-is, it results in compile errors because a Foo has no primary key defined.

I tried defining a primary key for Foo in EF, but this value never gets returned from the stored proc, so I get this sort of error:

The data reader is incompatible with the specified 'Database.Foo'. A member of the type, 'FooPrimaryKey', does not have a corresponding column in the data reader with the same name.

I am unable to modify the stored procedure to return a "unique" column (which presumably would allow me to create an entity and define this column as the primary key). Further, Entity Framework does not allow me to set the imported function to return a complex type, because of an if() clause in the return statement of the stored procedure.

I'm not sure what to do to get Entity Framework to help me execute the stored procedure and return the correct results.


EDIT: I decided to define every column in the entity as one big compound key. Then calling the stored procedure (like this) returned the correct results set:

var fooList = db.GetFoo(param1, param2); // etc.

But later on, if I query the database again (in this case, using LINQ) an error is thrown:

An exception of type 'System.Data.Entity.Core.EntityCommandCompilationException' occurred in EntityFramework.SqlServer.dll but was not handled in user code. An error occurred while preparing the command definition.

1
3
5/23/2017 12:30:05 PM

Accepted Answer

The problem was solved in the following manner:

Foo was defined as an entity and not a complex type. There may have been a problem with the fact that there was no corresponding database table for Foo, and no way to create one. Instead, I added a new complex type for Foo.

Next, I created a second using() block for the subsequent LINQ query.

Lastly, the stored procedure GetFoo() was able to be modified to return a single predictable result set (the first SP returned one of two categorically different result sets, contingent on the results of an if statement). As some additional precautionary measures, I made sure that each resulting record had a unique GUID to use as a key (although I could not define a key for its corresponding complex type), and I aliased every column that the SP returned.

I can't nail down exactly which of these measures fixed my problem, but I hope that this serves useful to people who are experiencing similar issues.

1
3/23/2015 5:02:32 PM


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