Entity Framework SQL query not returning results

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'

You're basically doing description.ToString(), which returns the string representation of the query. — Gert Arnold
– Gert Arnold, Commented Feb 26, 2014 at 9:57
If someone's answer solved your problem, you might want to accept it as the answer. — Mihai Hantea
– Mihai Hantea, Commented Feb 26, 2014 at 10:09

zacran · Accepted Answer · 2015-07-10 20:33:56Z

8

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();

edited Jul 10, 2015 at 20:33

zacran

8656 silver badges18 bronze badges

answered Feb 26, 2014 at 9:32

Manish Mishra

12.4k5 gold badges40 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MattTheHack Over a year ago

This is good - I like this answer, how can I choose extra parameters in the select statement though, say I want to choose Description and ID

Manish Mishra Over a year ago

ohh, very simple, do this .Select(s=>new{ s.Description, s.ID, s.WhateverElseAvailable})

Mihai Hantea · Accepted Answer · 2014-02-26 10:33:26Z

4

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)

edited Feb 26, 2014 at 10:33

answered Feb 26, 2014 at 9:25

Mihai Hantea

1,74113 silver badges18 bronze badges

1 Comment

MattTheHack Over a year ago

Thanks for your response - I am getting the following error though : "The non generic method '...' .SQLQuery(string, params object[]) cannot be used with type arguments

Hossain Muctadir · Accepted Answer · 2014-02-26 09:38:10Z

SqlQuery returns DbSqlQuery<TEntity> object. In your case it is probably DbSqlQuery<DEPARTMENTs>. As the documentation says

Represents a SQL query for entities that is created from a System.Data.Entity.DbContext and is executed using the connection from that context. Instances of this class are obtained from the System.Data.Entity.DbSet instance for the entity type. The query is not executed when this object is created; it is executed each time it is enumerated, for example by using foreach. SQL queries for non-entities are created using the System.Data.Entity.DbContext.Database. See System.Data.Entity.Infrastructure.DbSqlQuery for a non-generic version of this class.

Please note the lines

The query is not executed when this object is created; it is executed each time it is enumerated, for example by using foreach.

Which means you have to iterate it to get the results.

Collectives™ on Stack Overflow

Entity Framework SQL query not returning results

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related