Is it feasible to use entity framework with native SQL?

ado.net entity-framework sql

Question

I'm attempting to search an XML field inside of a table, however EF does not allow this.

Is it feasible to have native SQL support with EF without utilizing pure Ado.net?

1
66
4/30/2014 1:56:54 PM

Accepted Answer

Use ObjectContext.ExecuteStoreCommand() if your query returns no results and ObjectContext.ExecuteStoreQuery if it does. For the.NET Framework version 4 and above.

Here is an example showing what to do for earlier.NET Framework versions. Whenever necessary, if your query produces results, replace ExecuteNonQuery().

static void ExecuteSql(ObjectContext c, string sql)
{
    var entityConnection = (System.Data.EntityClient.EntityConnection)c.Connection;
    DbConnection conn = entityConnection.StoreConnection;
    ConnectionState initialState = conn.State;
    try
    {
        if (initialState != ConnectionState.Open)
            conn.Open();  // open connection if not already open
        using (DbCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();
        }
    }
    finally
    {
        if (initialState != ConnectionState.Open)
            conn.Close(); // only close connection if not initially open
    }
}
73
10/6/2012 10:29:45 PM

Popular Answer

Using Entity Framework 5.0 Zzz-8-Zzz may be used to run pure multi-line/multi-command programs.SQL statements. Since the function returns an int, you won't need to provide a supporting object to save the returned value in this manner (the result returned by the database after executing the command).

Sample:

context.Database.ExecuteSqlCommand(@
"-- Script Date: 10/1/2012 3:34 PM  - Generated by ExportSqlCe version 3.5.2.18
SET IDENTITY_INSERT [Students] ON;

INSERT INTO [Students] ([StudentId],[FirstName],[LastName],[BirthDate],[Address],[Neighborhood],[City],[State],[Phone],[MobilePhone],[Email],[Enrollment],[Gender],[Status]) VALUES (12,N'First Name',N'SecondName',{ts '1988-03-02 00:00:00.000'},N'RUA 19 A, 60',N'MORADA DO VALE',N'BARRA DO PIRAÍ',N'Rio de Janeiro',N'3346-7125',NULL,NULL,{ts '2011-06-04 21:25:26.000'},2,1);

INSERT INTO [Students] ([StudentId],[FirstName],[LastName],[BirthDate],[Address],[Neighborhood],[City],[State],[Phone],[MobilePhone],[Email],[Enrollment],[Gender],[Status]) VALUES (13,N'FirstName',N'LastName',{ts '1976-04-12 00:00:00.000'},N'RUA 201, 2231',N'RECANTO FELIZ',N'BARRA DO PIRAÍ',N'Rio de Janeiro',N'3341-6892',NULL,NULL,{ts '2011-06-04 21:38:38.000'},2,1);
");

Visit this page for additional information: First thing in Entity Framework Code: running SQL files while creating a database



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow