[ACCEPTED]-Is it possible to run native sql with entity framework?-ado.net

Accepted answer
Score: 73

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

For previous 3 .NET Framework versions, here's a sample 2 illustrating what to do. Replace ExecuteNonQuery() as 1 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
    }
}
Score: 24

Using Entity Framework 5.0 you can use ExecuteSqlCommand to execute multi-line/multi-command 6 pure SQL statements. This way you won't need 5 to provide any backing object to store the 4 returned value since the method returns 3 an int (the result returned by the database 2 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 1 more on this, take a look here: Entity Framework Code First: Executing SQL files on database creation

Score: 17

Since .NET 4 you can use ExecuteStoreQuery method:

var list = myDBEntities.ExecuteStoreQuery<MyClass>(MyClass.sql);

where myDBEntities 2 is inherited from ObjectContext.

class MyClass
{
    /* You can change query to more complicated, e.g. with joins */
    public const string sql = @"select [MyTable].[MyField] from [MyTable]";
    public string MyField { get; set; }
}

Notice that 1 MyTable is real table name, not EF class.

Score: 17

For Entity Framework 5 use context.Database.SqlQuery.

And for Entity Framework 4 use context.ExecuteStoreQuery the following code:

 public string BuyerSequenceNumberMax(int buyerId)
    {
        string sequenceMaxQuery = "SELECT TOP(1) btitosal.BuyerSequenceNumber FROM BuyerTakenItemToSale btitosal " +
                                  "WHERE btitosal.BuyerID =  " + buyerId +
                                  "ORDER BY  CONVERT(INT,SUBSTRING(btitosal.BuyerSequenceNumber,7, LEN(btitosal.BuyerSequenceNumber))) DESC";

        var sequenceQueryResult = context.Database.SqlQuery<string>(sequenceMaxQuery).FirstOrDefault();

        string buyerSequenceNumber = string.Empty;

        if (sequenceQueryResult != null)
        {
            buyerSequenceNumber = sequenceQueryResult.ToString();
        }

        return buyerSequenceNumber;
    }

To 1 return a List use the following code:

 public List<PanelSerialList> PanelSerialByLocationAndStock(string locationCode, byte storeLocation, string itemCategory, string itemCapacity, byte agreementType, string packageCode)
 {
       string panelSerialByLocationAndStockQuery = "SELECT isws.ItemSerialNo,  im.ItemModel " +
        "FROM Inv_ItemMaster im   " +
        "INNER JOIN  " +
        "Inv_ItemStockWithSerialNoByLocation isws  " +
        "   ON im.ItemCode = isws.ItemCode   " +
        "       WHERE isws.LocationCode = '" + locationCode + "' AND  " +
        "   isws.StoreLocation = " + storeLocation + " AND  " +
        "   isws.IsAvailableInStore = 1 AND " +
        "   im.ItemCapacity = '" + itemCapacity + "' AND " +
        "   isws.ItemSerialNo NOT IN ( " +
        "           Select sp.PanelSerialNo From Special_SpecialPackagePriceForResale sp  " +
        "           Where sp.PackageCode = '" + packageCode + "' )";


    return context.Database.SqlQuery<PanelSerialList>(panelSerialByLocationAndStockQuery).ToList();


}
Score: 3

Keep it simple

using (var context = new MyDBEntities())
{
    var m = context.ExecuteStoreQuery<MyDataObject>("Select * from Person", string.Empty);
    //Do anything you wonna do with 
    MessageBox.Show(m.Count().ToString());
}

0

Score: 2
public class RaptorRepository<T>
    where T : class
{
    public RaptorRepository()
        : this(new RaptorCoreEntities())
    {
    }

    public RaptorRepository(ObjectContext repositoryContext)
    {
        _repositoryContext = repositoryContext ?? new RaptorCoreEntities();
        _objectSet = repositoryContext.CreateObjectSet<T>();
    }

    private ObjectContext _repositoryContext;
    private ObjectSet<T> _objectSet;
    public ObjectSet<T> ObjectSet
    {
        get
        {
            return _objectSet;
        }
    }


    public void DeleteAll()
    {
        _repositoryContext
            .ExecuteStoreCommand("DELETE " + _objectSet.EntitySet.ElementType.Name);
    }
}

0

Score: 0

So what do we say about all this in 2017? 80k 51 consultations suggests that running a SQL 50 request in EF is something a lot of folk 49 want to do. But why? For what benefit?

Justin, a 48 guru with 20 times my reputation, in the 47 accepted answer gives us a static method 46 that looks line for line like the equivalent 45 ADO code. Be sure to copy it well because 44 there are a few subtleties to not get wrong. And 43 you're obliged to concatenate your query 42 with your runtime parameters since there's 41 no provision for proper parameters. So all 40 users of this method will be constructing 39 their SQL with string methods (fragile, untestable, sql 38 injection), and none of them will be unit 37 testing.

The other answers have the same 36 faults, only moreso. SQL buried in double 35 quotes. SQL injection opportunities liberally 34 scattered around. Esteemed peers, this is 33 absolutely savage behaviour. If this was 32 C# being generated, there would be a flame 31 war. We don't even accept generating HTML 30 this way, but somehow its OK for SQL. I 29 know that query parameters were not the 28 subject of the question, but we copy and 27 reuse what we see, and the answers here 26 are both models and testaments to what folk 25 are doing.

Has EF melted our brains? EF doesn't 24 want you to use SQL, so why use EF to do 23 SQL.

Wanting to use SQL to talk to a relational 22 DB is a healthy, normal impulse in adults. QueryFirst shows 21 how this could be done intelligently, your 20 sql in .sql file, validated as you type, with 19 intellisense for tables and columns. The 18 C# wrapper is generated by the tool, so 17 your queries become discoverable in code, with 16 intellisense for your inputs and results. End 15 to end strong typing, without ever having 14 to worry about a type. No need to ever remember 13 a column name, or its index. And there are 12 numerous other benefits... The temptation 11 to concatenate is removed. The possibility of mishandling 10 your connections also. All your queries 9 and the code that accesses them are continuously 8 integration-tested against your dev DB. Schema 7 changes in your DB pop up as compile errors 6 in your app. We even generate a self test 5 method in the wrapper, so you can test new versions 4 of your app against existing production 3 databases, rather than waiting for the phone 2 to ring. Anyone still need convincing?

Disclaimer: I 1 wrote QueryFirst :-)

More Related questions