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

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

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

Leniel Maccaferri

Related Questions

Am I crazy for using Entity Framework with an OleDB connection?

The method you're using to create the EF connection string is the right one....BUT......Only Providers (such as SqlClient) that provide anything referred to as provider services are compatible with the Entity Framework....Due to the OleDB provider's lack of support for "Provider Services," EF cannot be used with OleDb (unless you can find a third p...
c# entity-framework ms-access
asked by Alex James

Entity Framework ObjectContext -> native DBMS raw SQL calls

Even if Craig's response didn't function in its current form, it pointed me in the correct direction. Unexpectedly, an EntityConnection exists. You may establish a connection to the underlying DBMS using the StoreConnection attribute. Thus, carrying out "native" SQL is as simple as this:... static void ExecuteSql(ObjectContext c, string sql) ...
.net ado.net entity-framework postgresql
asked by Justin Grant

In entity framework, can I run SQL?

I recently searched for this myself and came across this example; perhaps it will be helpful.... static void ExecuteSql(ObjectContext c, string sql) { var entityConnection = (System.Data.EntityClient.EntityConnection)c.Connection; DbConnection conn = entityConnection.StoreConnection; ConnectionState initialState = c...
entity-framework
asked by Gavin

In System.Data.Entity.dll (Entity Framework), why am I receiving a NullReferenceException?

When I unintentionally loaded a Module more than once in a modular architecture, I had the identical issue....Additionally, the Module(s) used two identical copies of...DbContext... attempting to establish a connection between an entity from one context and an entity from another context....This concealed the...InvalidOperationException... Without ...
c# entity-framework wpf
asked by FrankM

Query runs in less than a millisecond in SQL, but times out in Entity Framework

I saw this problem with EF6....await _context.Database.SqlQuery<MyType>(sql)... even with my timeout setting set to 60 seconds, it still clocking out. However, running the exact same SQL in SSMS (using the profiler to verify that the SQL I gave in was unaltered) produced the desired results in a matter of seconds....Run sp updatestats....I have the...
entity-framework sql-server
asked by Chris

Running SQL Server CE 4 Queries with Visual Studio 2012 and SQL Server Management Studio 2012

Microsoft received ...Remove that... for SSMS 2012 and advised using WebMatrix's integrated tools instead (from memory; sorry, I can't locate a link for that)....But you might use the ...Toolbox for SQL Server Compact... instead, as was recommended in the ...Microsoft Solutions... thread; it works fairly well.
entity-framework sql-server-2012 sql-server-ce visual-studio-2012
asked by Fabian Tamp

Entity Framework 6.1.3 and SQL Server CE 4.0 - No Entity Framework provider found

The DDEX provider is installed for you by the SQL Server Compact Toolbox, which you must install on the new development machine. (You might need to open the Toolbox's About screen, click the DDEX install button, and then restart Visual Studio.)
ef-database-first entity-framework-6 git sql-server-ce-4
asked by ErikEJ

Logging the SQL generated by LINQ to SQL in Entity Framework in .net

In line with ...Logging in Entity Framework...The DbContext.Database.Log property can be set to a delegate for any method that takes a string.... Most commonly it is used with any TextWriter by setting it to the “Write” method of that TextWriter. All SQL generated by the current context will be logged to that writer. For example, the following ...
entity-framework-6 logging sql sql-server
asked by Markus Safar

Entity Framework 6 vs Entity Framework Core Raw Sql

When there is just one table, I have done the "fromsql" right off of the context; however, I recognise that this is not what you want, even if it advances the cause....var blogs = context.Blogs .FromSql("SELECT * FROM dbo.Blogs") .ToList(); ...But in a situation like yours, it is complicated and involves the combining of many tables and CTE...
entity-framework-6 entity-framework-core
asked by djangojazz

Is it possible to perform a SQL Injection on a application that uses Entity Framework?

ZZZ_tmp
c# entity-framework entity-framework-6 sql-injection
asked by EdSF

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

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