Raw Sql - System.Data.Entity.Core.EntityCommandExecutionException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

Question

I did raw SQL query below to select only certain fields from a table.

var qry = "SELECT ptitle, pname, paid, pidno, pdob, pgender, ptelh FROM patient where ";
            if (strPatientName != "")
            {
                qry += "name like @name ";
                if (strMRN != "")
                    qry += "&& id like @id ";
            }

var query = db.Database.SqlQuery<patient>
                ((qry), new SqlParameter("@name", "%" + strPatientName + "%"), new SqlParameter("@id", "%" + strMRN + "%")).ToList();

But when i run, it gives out an error with the column that i didn't include in the query.

'System.Data.Entity.Core.EntityCommandExecutionException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

Additional information: The data reader is incompatible with the specified 'project.Models.patient'. A member of the type, 'nbid', does not have a corresponding column in the data reader with the same name.

Seems that the `nbid` column is required by EF as part of query result set but not returned after query execution. Try adding that column into `qry` query string. — Tetsuya Yamamoto, Mar 22 '17 at 00:57
@TetsuyaYamamoto now its the next column that i didn't include in the query that comes up with the same error. So in other words, does that mean that i have to include all columns in the query for the error to disappear? The thing is, i just want to call several fields, not all. Is this possible? — Nurul, Mar 22 '17 at 01:38
I see that you have using `patient` entity as result set with `SqlQuery`, hence it must be return all column property names present on that entity (try `SELECT *` to verify). To call several fields only afterwards, you can make another select with LINQ. — Tetsuya Yamamoto, Mar 22 '17 at 02:57
@TetsuyaYamamoto Thanks for your explanation. Could you please have a look at my other question [here](http://stackoverflow.com/questions/42882670/project-models-tablenamethe-field-pvid-must-be-a-string-or-array-type-with-a-ma)? — Nurul, Mar 22 '17 at 03:54

score -1 · Answer 1 · edited May 23 '17 at 12:00

-1

You are looking for partial loading of data in to EF. You need to load data manually in to EF. You will find a similar question answered here.

edited May 23 '17 at 12:00

Community

1
1

answered Mar 22 '17 at 03:12

udaya kumar

169
8

Thanks. I'll have a try at it :) – Nurul Mar 22 '17 at 03:46

Raw Sql - System.Data.Entity.Core.EntityCommandExecutionException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

1 Answers1