The data reader is incompatible with the specified Entity Framework
Solution 1
To me it seems that both branches of the IF
return different data, the first branch returns 9 columns where the second - only three. I believe the EF can't reflect the IFramingSystem
from the latter. Specifically, the column FrameType
(and 5 other columns) are obviously missing:
...
SELECT c.ID,c.Name,c.Descr <- where are the remaining columns
from Catelog.Component c
...
Solution 2
I understand this is an old post; but, I wanted to share what I learned tonight about this. What I found that the 'most relevant portion of the error message is stating' is this.
db.proc_GetFramingSystems(brandID, frameType, glazeMethod, getMin).ToList<IFramingSystem>();
is expecting a column to be returned from the stored procedure with the alias of 'FrameType'.
I ran into this when I created a POCO (plain old clr object) class of my table with more programmer friendly names. Instead of a strongly typed name of say 'email_address', I wanted 'EmailAddy' and so forth. I created a mapped class stating this among other mapped columns.
this.Property(t => t.EmailAddy).HasColumnName("email_address");
Although this is necessary for other parts of EF to work, the mapping class is not referenced when executing a db.SqlQuery. So, when the code below executes
var a = new SqlParameter("@fshipno", shipno);
return _context.db.SqlQuery<EmailList>("exec spGetEmailAddy @fshipno", a).ToList();
It generated the same error except instead of 'FrameType', it mentioned 'EmailAddy'. The fix... I had to alias the 'email_address' column in my stored procedure to 'EmailAddy' to get it to map the returned dataset to my POCO.
EDIT: I have found that this only works if your method is returning an IEnumberable
public IEnumberable<myPOCO> GetMyPoco()
You will get the same error message if you are attempting to return a single POCO object.
public myPOCO GetMyPoco()
Solution 3
If you are inserting/deleting/updating (these are considered by EF as 'non-query'), and can be called by our code using
MyDbContext.Database.ExecuteSqlCommand(insert into Table (Col1,Col2) values (1,2));
But if are doing select query for a raw SQL statement, then use
MyDbContext.DbSet<Table_name>.SqlQuery(select * from table_name).ToList();
or
MyDbContext.Database.SqlQuery(select * from table_name).ToList();
()
The SqlQuery()
function, in EF, for strange reasons, throw exception Insert/delete/update operation. (The exception thrown is "A member of the type, does not have a corresponding column in the data reader with the same name.") But it has actually performed operation if you open your Sql Management Studio and check for the entries.
FYI http://www.entityframeworktutorial.net/EntityFramework4.3/raw-sql-query-in-entity-framework.aspx
Related videos on Youtube
![Filling The Stack is What I DO](https://i.stack.imgur.com/JzRF3.jpg?s=256&g=1)
Filling The Stack is What I DO
I specialize in complex software solutions for the construction industry, specifically CAD drafting and automation architectural solutions. However, I do not limit myself to this one specific industry, I love coding and it is my passion thus I will take on any challenge. Erik Little Bits Builder [email protected] 469-540-8417
Updated on July 05, 2022Comments
-
Filling The Stack is What I DO almost 2 years
I have a method that will return the bare min results from a sproc to fill a select menu. When I want the bare min results I pass bool getMin = true to the sproc, and when I want the complete record I pass bool getMin = false.
This is causing the Entity FrameWork error of "The data reader is incompatible with the specified"
The most relevant portion of the error
{"Message":"An error has occurred.","ExceptionMessage":"The data reader is incompatible with the specified 'CatalogModel.proc_GetFramingSystems_Result'. A member of the type, 'FrameType', does not have a corresponding column in the data reader with the same name.","ExceptionType":"System.Data.EntityCommandExecutionException",
Obviously the error is telling me that when the data reader attempted to set the property 'FrameType' that is was not in the query results.
Now I understand the error, what I am wanting to know is that am I goning to have t split up this sql sproc into two sprocs or is there a work around for this?
My function below
public static IEnumerable<IFramingSystem> GetFramingSystems(int brandID, string frameType, string glazeMethod, bool getMin) { using (CatalogEntities db = new CatalogEntities()) { return db.proc_GetFramingSystems(brandID, frameType, glazeMethod, getMin).ToList<IFramingSystem>(); }; }
My TSQL below
ALTER proc [Catelog].[proc_GetFramingSystems] @BrandID INT, @FrameType VARCHAR(26), @GlazeMethod VARCHAR(7) ='Inside', @getMin BIT = 0 as BEGIN SET NOCOUNT ON; IF @getMin =0 BEGIN SELECT c.ID,c.Name,c.Descr,c.FrameType,c.isSubFrame, c.GlassThickness,c.GlassPosition,c.GlazingMethod,c.SillProfile from Catelog.Component c WHERE c.MyType ='Frame' AND c.FrameType = @FrameType AND c.GlazingMethod = @GlazeMethod AND c.ID IN( SELECT cp.ComponentID FROM Catelog.Part p JOIN Catelog.ComponentPart cp ON p.ID = cp.PartID WHERE p.BrandID = @BrandID ) ORDER BY c.Name END ELSE SELECT c.ID,c.Name,c.Descr from Catelog.Component c WHERE c.MyType ='Frame' AND c.FrameType = @FrameType AND c.GlazingMethod = @GlazeMethod AND c.ID IN( SELECT cp.ComponentID FROM Catelog.Part p JOIN Catelog.ComponentPart cp ON p.ID = cp.PartID WHERE p.BrandID = @BrandID ) ORDER BY c.Name SET NOCOUNT OFF; END;
-
Michael Freidgeim over 5 yearsPossible duplicate of Incompatible Data Reader Exception From EF Mapped Objects
-
-
Filling The Stack is What I DO almost 11 yearsThat is what I was thinking. Oh well, at least it was worth asking. Thank you for your help @Wiktor Zychla.