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

Answers

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.

    Zeeshan
    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?

    Thanks.
    -Brian
    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:
    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.
    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
    http://ledomoon.blogspot.com/2009/12/execute-t-sql-statements-and-stored.html 

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