Entity Framework SQL query not returning results

Question

Entity Framework SQL query not returning results

c# entity-framework sql

Question

I am using an Entity Framework Database first model and I am having problems querying my DB.

I'm trying to use the following code:

var ctx = new dbEntities();
var description = ctx.DEPARTMENTs.SqlQuery("SELECT DESCRIPTION FROM DEPARTMENT WHERE DEPARTMENT='FINANCE'");
System.Diagnostics.Debug.WriteLine(description);

I can see my table of results for the DEPARTMENTs table in the debug log, but for some reason the SQL query is just returning me this in the console rather than executing the query, anybody know why?

SELECT DESCRIPTION FROM DEPARTMENT WHERE DEPARTMENT='FINANCE'

MattTheHack

Popular Answer

Try:

var description = ctx.DEPARTMENTs.SqlQuery<string>("SELECT DESCRIPTION FROM DEPARTMENT WHERE DEPARTMENT='FINANCE'").ToList();

EDIT: Raw SQL Queries http://msdn.microsoft.com/en-us/data/jj592907.aspx

Note that, just as for LINQ queries, the query is not executed until the results are enumeratedâ€”in the example above this is done with the call to ToList.

System.Diagnostics.Debug.WriteLine(Object) writes the value of the object's ToString method to the trace listeners in the Listeners collection. Debug.WriteLine(Object)

Mihai Hantea

View more on Stack Overflow

Manish Mishra · Accepted Answer · 7/10/2015 8:33:56 PM

To make the query execute against the provider, you have to use any extension derived from IEnumerable i.e. ToList() or First() or FirstOrDefault()

Now you have to consider few things. What your query might possibly return? A List of data or a single data? Or even if a list of matches found, you want just the single one?

from the syntax of your query I assume you should be doing this:

var ctx = new dbEntities();
var dep = ctx.DEPARTMENTs
             .SqlQuery("SELECT * FROM DEPARTMENT WHERE DEPARTMENT='FINANCE'")
             .FirstOrDefault();

if(dep!=null)
{
    var description = department.Description;
}

Alternatively, you can also do this(I would prefer):

var description = ctx.DEPARTMENTs.Where(s=>s.Department.ToUpper() =="FINANCE")
                     .Select(s=>s.Description)
                     .FirstOrDefault();

Entity Framework SQL query not returning results

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related