Execute T-SQL from ObjectContext

Question

Hi group,
I'm beginning with EF so probably this is a stupid question, anyway is there a way of passing some "raw" T-SQL code through the ObjectContext, as in LINQ to SQL with the DataContext? I'm asking this because I'm developing an ASP.NET application which is fully based on EF except for a unique section which directly builds some T-SQL code from search parameters, combining various table field filters with an SQL 2008 FTS query. The component just gets search query and parameters as input and spits out some T-SQL as output. If I were using LINQ to SQL I'd use a DbCommand and execute a reader, wrapping all the (readonly) results into business objects then passed to an ObjectDataSource in a page. Anyway here I'm dealing with EF. I suppose the only way would be opening another connection with a different data access technology? Or is there anything more palatable I could do using EF itself?
Thanx to all

Answer 1

I know of no such way out of box in v1. I think EF extensions will let u do that but i cant remember. in vs 2010, there are several ways u can execute arbitary query or command using the objectcontext.

db.ExecuteStoreCommand("insert..") lets you exeucte the stored procedure or dynamic sql returning the number of rows affected.

db.ExecuteStoreQuery<Customer>("sql) returns a strongly typed object. It bypasses EF's mapping layer.

db.Translate<Customer>(reader) will tranlate the reader to entities but i think the entities in this case would be tracked in hte objectcontext.

Zeeshan

Answer 2

The topics in this section of the documentation demonstrate how to execute queries that are defined by ESQL expressions. Are those examples helpful?

Thanks.
-Brian

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 3

As Brian says, using esql may be an option. Otherwise, you can certainly get at the underlying database connection and execute a store command against it:
http://blogs.msdn.com/meek/archive/2008/03/26/ado-entity-framework-stored-procedure-customization.aspx

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 4

Thanks to all you guys! The library pointed out by Noam can be very useful in other scenarios, but here I'm dealing with dynamically generated T-SQL code (thus, no stored procedure) which above all is not compatible with eSQL as it contains FTS statements; and fortunately I don't need to track the results for changes as they are read-only. So I think the simplest and most effective way will just be sticking to an older technology, use a reader to populate a set of data wrapper read only objects and directly execute the TSQL query on the database. It looks somewhat ugly in the marvelous world of EF :), but I suppose this is the only way for using FTS, until now at least (EF team, any plans on integrating FTS?).

Thanks again!

Answer 5

Execute T-SQL Statements and stored using Entity framework 4
http://ledomoon.blogspot.com/2009/12/execute-t-sql-statements-and-stored.html 

---

Waleed Mohamed