DBContext Native SQL Queries

Question

How to use native queries with DBContext ? If I run the code, this give me exception. Why and what to do to run native query when using DBContext ?

AcademyEntities context = new AcademyEntities();

            string nativeSQLQuery =
                "SELECT * " +
                "FROM dbo.Employees " +
                "WHERE FirstName='{0}'";

            string name = "Guy";

            var emp = context.Departments.SqlQuery(nativeSQLQuery, name);

            foreach (var item in emp)
            {

            }

The exception is "The data reader is incompatible with the specified 'TelerikAcademyModel.Department'. A member of the type, 'Name', does not have a corresponding column in the data reader with the same name." — TheChampp, Jan 21 '13 at 18:16

score 1 · Accepted Answer · answered Jan 21 '13 at 18:18

1

You're querying the Employees table, but trying to materialize Department objects.

Change your call to:

var emp = context.Employees.SqlQuery(nativeSQLQuery, name);

( and remove the quotes round the {0} )

answered Jan 21 '13 at 18:18

Nick Butler

24,045
4
49
70

It worked.Thank you.I have one more question. With EF5 "AcademyEntities" inherit DBContext. In previous versions in EF (4.0) "AcademyEntities" inherit ObjectContext. Why in EF5.0 "AcademyEntities" interit DBContext ? Is it better ? What is the difference ? – TheChampp Jan 21 '13 at 18:23
@user1749358 excellent :) `DbContext` was introduced in EF 4.1 - most people use it now. – Nick Butler Jan 21 '13 at 18:26
Is this a good practice to use native sql queries in this way ? – TheChampp Jan 21 '13 at 18:28
@user1749358 No, it's not really good practice. You should use LINQ-to-Entities and let EF generate the SQL for you. It's also easier! You can download some samples from [`here`](http://weblogs.aspnet05.orcsweb.com/bschooley/archive/2007/10/25/msdn-s-101-linq-samples.aspx). Also, try out [`LINQPad`](http://www.linqpad.net/) – Nick Butler Jan 21 '13 at 18:32
What if I have more parametres? – Birhan Nega Nov 16 '20 at 09:02

score 1 · Answer 2 · answered Jan 21 '13 at 18:18

1

You are querying Employees, so you should use context.Employees:

var emp = context.Employees.SqlQuery(nativeSQLQuery, name);

answered Jan 21 '13 at 18:18

phnkha

7,782
2
24
31

DBContext Native SQL Queries

2 Answers2

Linked