Execute T-SQL from ObjectContext


  • 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
    Thursday, September 10, 2009 6:06 PM


All replies

  • 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.

    Thursday, September 10, 2009 6:13 PM
  • The topics in this section of the documentation demonstrate how to execute queries that are defined by ESQL expressions. Are those examples helpful?

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, September 10, 2009 6:51 PM
  • 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:

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, September 10, 2009 6:58 PM
  • 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!
    Friday, September 11, 2009 7:46 AM
  • Execute T-SQL Statements and stored using Entity framework 4 

    Waleed Mohamed
    Tuesday, December 01, 2009 11:45 AM