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 with no candidate keys and no primary keys in the result set. I made a thing calledFoo the stored process will produce a record to represent a record in the results collection.List<Foo> . This is how the item appears:

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

Compile errors occur when I try to use the stored procedure in its current state because aFoo has no specified primary key.

I attempted to create a main key forFoo I receive the following problem since the stored proc never returns this value in EF:

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 can't alter the stored procedure to make it return a "unique" column (which presumably would allow me to create an entity and define this column as the primary key). Additionally, due to an error, Entity Framework does not allow me to set the imported function give back a complicated type.if() clause in the stored procedure's return statement.

I'm unsure of how to get Entity Framework to assist in running the stored procedure and returning the right results.


EDIT: I made the decision to define each entity column as a single large compound key. The correct results set was then returned by calling the stored procedure (in this manner):

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

But subsequently, if I use LINQ to query the database once more, an error is raised:

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 issue was resolved in the way as follows:

Foo was not a complicated type but rather defined as an entity. The absence of a corresponding database table for could have presented a challenge.Foo , and no means of making one. In its place, I developed a new complicated type forFoo .

I then made a second one.using() block for the next LINQ command.

The saved procedure is the last.GetFoo() was possible to be changed to only produce a single predictable result set (the first SP only produced one of two result sets that were categorically different depending on the outcome of anif statement). In addition, I aliased each column that the SP produced and made sure that each resulting record had a distinct GUID to use as a key (even though I was unable to define a key for each associated complex type).

I'm unable to pinpoint precisely which of these solutions resolved my issue, but I do hope that others who are having a similar difficulty would find this information helpful.

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