0

How can I use sql query, in sql language, if I used entity framework to make the connection? I understood from this post that ObjectContext.ExecuteStoreQuery won't help because it works only with some queries (not 1:1 to sql language).

The other option mentioned there is to use ObjectContext.Connection and write "classic" ADO.NET code from there, but I just can't figure out how.

Can someone please write a very simple code example how can I perform a simple query like 

select MAX(customer_id) from Customers

with the entity framework? I know that Linq-To-Sql exist, but I prefer to use sql language, it looks simpler to me and I am more familiar with it.

Community
  • 1
  • 1
BornToCode
  • 9,495
  • 9
  • 66
  • 83

2 Answers2

9

use the Database.SqlQuery method to execute SQL queries

var maxId= context.Database.
           SqlQuery<int>("select MAX(customer_id) from Customers")
                                                              .SingleOrDefault();

This should work assuming context in your DataContext class object.

Shyju
  • 214,206
  • 104
  • 411
  • 497
2

Not an answer, but I have to...

I prefer to use sql language

Do yourself a favour and "forget" SQL! Linq is really not that hard. SQL in string literals is maintenance hell. Linq is strong-typed so the compiler guards you against code corruption. You've got intellisense, far less ceremonial code, it is much easier to express complex queries. And so forth and so on.

Your statement is a piece of cake in Linq.

context.Customers.Max(c => c.customer_id)

Well, just an advice :D

Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
  • actually I was also hoping to get that kind of answer as well, because I feel kind of primitive not using Linq and I wanted to see other people opinions :) but I do have a project with a deadline and it'll be much easier to do it with something familiar than guessing how it should be written in Linq.. – BornToCode Jun 12 '12 at 20:48
  • Ahh, that's life. But after that: full steam ahead with Linq, right? – Gert Arnold Jun 12 '12 at 20:51
  • if nobody here is going to solve my problem soon I might be forced to full steam ahead with Linq now.. :) But I still fear from joinning tables or performing complicated queries with Linq – BornToCode Jun 12 '12 at 20:59
  • 1
    I think you can use ExecuteStoreQuery after all. [MSDN](http://msdn.microsoft.com/en-us/library/dd487208.aspx) states: "uses the existing connection to execute an _arbitrary_ command directly against the data source." (emphasis mine). BUT you must take care that the columns in the query match the properties of the type specified by TElement. – Gert Arnold Jun 12 '12 at 21:13
  • Actually, thanks to your clarification I finally managed to use ExecuteStoreQuery! I'm now creating a special custom class everytime with all fields expected to retrieve from the sql query and use it for the TElement. – BornToCode Jun 13 '12 at 10:03
  • I followed your advice and adopted Linq and it is much easier to maintain code with it, but what about when performance is critical (and in many cases it is), using Linq will degrade performance, so it looks like we can't have the pleasure of "forgetting" SQL after all..? – BornToCode Sep 15 '14 at 13:21
  • 2
    Hehe, no we can't. SQL generated by an ORM can't compete with manually crafted and optimized SQL statements. Of course, when performance is critical, we don't want to depend on these ORM queries. – Gert Arnold Sep 15 '14 at 14:20