Running inline SQL with Entity Framework 5 and returning tracked Entities

Question

How can I execute a command such as:

SELECT * FROM CATS

That behaves exactly as if I'd done myContext.Cats.Where(c => true); ?

Google suggested context.Database.ExecuteSqlCommand() but that returns an int. There is also context.Database.SqlQuery which looks promising, but it says that entities returned are not tracked, which I suspect is important (I'm not really familiar with how EF works in terms of tracking things).

It suggests using System.Data.Entity.DbSet<TEntity>.SqlQuery(Object[]) to track it, but I'm not entirely sure what this means or how to implement it.

In addition to make it even more confusing I want to write a generic method to allow me to execute the specific query against any table.

Here's a rough example of what I'd like in pseudocode

public DbSet<T> ExecuteSelect<T>(DbContext context, string table)
{
     DbSet<T> entities = context.RunSql("SELECT * FROM " + table);

     return entities;
}

Any ideas?

undefined · Accepted Answer · 2013-06-07 13:10:36Z

2

Accoring to this: http://msdn.microsoft.com/en-us/data/jj592907.aspx you want the following:

public IEnumerable<T> ExecuteSelect<T>(DbContext context, string table)
{
     IEnumerable<T> entities = context.Set<T>.SqlQuery("SELECT * FROM " + table).ToList();

     return entities;
}

myContext.Cats.Where(c => true) returns IQueriable<Cat> (not DbSet)

BUT

Your returned set will actually be finalized already (eg you cant add extra bits to your query later) so having it Queriable is misdirecting.

edited Jun 7, 2013 at 13:10

answered Jun 7, 2013 at 13:04

undefined

34.4k23 gold badges136 silver badges203 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Abhishek Sharma · Accepted Answer · 2014-05-26 09:29:48Z

0

You can execute sql queries directly as follows :

        private int DeleteData()
    {
        using (var ctx = new MyEntities(this.ConnectionString))
        {
            if (ctx != null)
            {

                //Delete command
                return ctx.ExecuteStoreCommand("DELETE FROM ALARM WHERE AlarmID > 100");

            }
        }
        return 0;
    }

For select we may use

using (var context = new MyContext()) 
{ 
    var blogs = context.MyTable.SqlQuery("SELECT * FROM dbo.MyTable").ToList(); 
}

answered May 26, 2014 at 9:29

Abhishek Sharma

2812 silver badges6 bronze badges

Collectives™ on Stack Overflow

Running inline SQL with Entity Framework 5 and returning tracked Entities

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related