Execute Generic SQL query using Entity Framework C#

Question

I want to execute sql query.And then dump the retrieved value in a Web Page. I am able to do with SQLCommand in c#. But how can i do this using Entity Framework. The reason i find it difficult is because i don't know on which table this query is going to run(As for this i will have to parse the select query). Please help me out.

Answer 1

context.ExecuteStoreQuery<Product>("select * from table where id = {0}", 1);

ExecuteStoreQuery

Answer 2

I realize there is already a good answer to this but I can give you a tip - you may implement the Execute Around Method pattern to perform generic queries both for select, insert and update in transactions. I have done so this way:

internal class CommonDataTool
{
    internal delegate object SqlCommandDelegate();

    /// <summary>
    /// Use only for select where (a) return value(s) is/are expected and/or required
    /// </summary>
    /// <typeparam name="T"> Expected datacontext model return type, example: DataContext.User</typeparam>
    /// <param name="context"> The DataContext (YourDataModel) instance to be used in the transaction </param>
    /// <param name="action"> Linq to Entities action to perform</param>
    /// <returns> Returns an object that can be implicitly casted to List of T where T is the expected return type. Example: List of DataContext.User</returns>
    internal List <T> ExecuteSelect<T>(YourDataModel context, SqlCommandDelegate action)
    {
        using (context)
        {
            var retVal = action(); return ((ObjectQuery<T>)retVal).ToList();
        }
    }

    /// <summary>
    /// Use for updates and inserts where no return value is expected or required
    /// </summary>
    /// <param name="context"> The DataContext (YourDataModel) instance to be used in the transaction </param>
    /// <param name="action"> Linq to Entities action to perform</param>
    internal void ExecuteInsertOrUpdate(YourDataModel context, SqlCommandDelegate action)
    {
        using (context)
        {
            using (var transaction = context.BeginTransaction())
            {
                try
                { action(); context.SaveChanges(); transaction.Commit(); }
                catch (Exception )
                { transaction.Rollback(); throw; }
            }
        }
    }
}

public static class ObjectContextExtensionMethods
{
    public static DbTransaction BeginTransaction( this ObjectContext context)
    {
        if (context.Connection.State != ConnectionState .Open) { context.Connection.Open(); }
        return context.Connection.BeginTransaction();
    }
}

This is good because you may then implement a dataadapter with minimalistic linq queries that you can pass as delegate arguments as such:

var users = _dataTool.ExecuteSelect<DataContext.User>(Db, GetUsers);

private static object GetUsers()
{
    return (from u in Db.User select U).ToList();
}

Another good thing is that your update/inserts are run in transactions without you having to explicitly declare them in your linq queries.

Example:

public void UpdateUser(DomainUser user)
{
_dataTool.ExecuteInsertOrUpdate(Db, () =>
        {
            Db.User.First(u => u.UserId == user.Id).Email = user.Email;
            Db.User.First(u => u.UserId == user.Id).Name = user.Name;
            Db.User.First(u => u.UserId == user.Id).LastName = user.LastName;
            Db.User.First(u => u.UserId == user.Id).Password = user.Password;
            return null;
        });    
}

Src: http://www.marcusnordquist.com/?p=66