Is it feasible to use entity framework with native SQL?

ado.net entity-framework sql

Question

I am trying to search an XML field within a table, This is not supported with EF.

Without using pure Ado.net is possible to have native SQL support with EF?

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

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

For .NET Framework version 4 and above: use ObjectContext.ExecuteStoreCommand() if your query returns no results, and use ObjectContext.ExecuteStoreQuery if your query returns results.

For previous .NET Framework versions, here's a sample illustrating what to do. Replace ExecuteNonQuery() as needed if your query returns results. 

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 you can use ExecuteSqlCommand to execute multi-line/multi-command pure SQL statements. This way you won't need to provide any backing object to store the returned value since the method returns an int (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);
");

For more on this, take a look here: Entity Framework Code First: Executing SQL files on database creation

Leniel Maccaferri

Related Questions

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

The approach you are using to build the EF connection string is correct....BUT......The Entity Framework only works with Providers (i.e. SqlClient) that support something called provider services....The OleDB provider doesn't support 'Provider Services' so you can't use EF with the OleDb (unless you can find a third party OleDb provider with suppor...
c# entity-framework ms-access
asked by Alex James

Entity Framework ObjectContext -> native DBMS raw SQL calls

Craig's answer, while it didn't work as-is, got me looking in the right direction. Turns out there's an EntityConnection.StoreConnection property which gets you a connection to the underlying DBMS. So executing "native" SQL is as easy as this:... static void ExecuteSql(ObjectContext c, string sql) { var entityConnection = (System.Da...
.net ado.net entity-framework postgresql
asked by Justin Grant

In entity framework, can I run SQL?

Was Googling around for this myself the other day, this is the example I found hope it helps... static void ExecuteSql(ObjectContext c, string sql) { var entityConnection = (System.Data.EntityClient.EntityConnection)c.Connection; DbConnection conn = entityConnection.StoreConnection; ConnectionState initialState = co...
entity-framework
asked by Gavin

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

I got the same problem when accidently loading a Module multiple times in a modular Architecture....Additional, the Module(s) where using 2 copies of the same ...DbContext..., trying to connect one Entity from one Context to another Entity of the other Context....This masked the ...InvalidOperationException... (cannot use an Entity in multiple Trac...
c# entity-framework wpf
asked by FrankM

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

I observed this issue with EF6. ...await _context.Database.SqlQuery<MyType>(sql)... was timing out even when my timeout value was cranked up to 60 seconds. However, executing the exact same SQL (used profiler to confirm the sql I passed in was unmodified) in SSMS yielded expected results in one second....exec sp_updatestats...Fixed the issue for me...
entity-framework sql-server
asked by Chris

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

Microsoft got ...rid of that... for SSMS 2012, and suggested to use the integrated tools in WebMatrix instead (from memory, can't find a link for that sorry)....As suggested in that ...Microsoft Answers... post, however, you could use the ...SQL Server Compact Toolbox..., it's pretty effective.
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

You need to install the SQL Server Compact Toolbox on the new dev machine, it installs the DDEX provider for you. (You may have to go to the About screen in the Toolbox and clikc the DDEX install button, then restart VS)
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

According to ...Entity Framework Logging...:...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

I have done the 'fromsql' off of the context directly when it is a single table, but I realize this is not what you want but it builds on it....var blogs = context.Blogs .FromSql("SELECT * FROM dbo.Blogs") .ToList(); ...However in a case like yours it is complex and a joining of multiple tables and CTEs. I would suggest you create a custom...
entity-framework-6 entity-framework-core
asked by djangojazz

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

Heed this:...It is quite easily possible if the developer uses EF as a wrapper around ADO.NET and uses FromSQL. Of course, this is not the intended nor normal use of EF, but I have seen it – Camilo Terevinto...REF: ...Raw SQL Queries...Additionally, while not really "sql injection", since one of the goals of such is to somewhat alter your dat...
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