Querying MySQL using Entity Framework and MySQL Stored Procedures

What is a stored procedure?

MySQL Stored procedures can be used to run a query against a MySQL database in order to manipulate data and/or to return result sets.

What is the problem?

If you are following the Database First approach of the Entity Framework and then creating your model and database context code based on your database design, the generated code will not contain any methods that return you a nice list of entities.

For instance; if you have a stored procedure named getOnlineUsers which queries the Users table and returns the online users, you will find yourself realizing that you do not have any methods returning you, let’s say a List<User> result set by executing the mentioned stored procedure.

Here is a sample auto-generated database context class:

    public partial class Entities : DbContext
    {
        public Entities()
            : base("name=Entities")
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
        }

        public virtual int getOnlineUsers()
        {
            return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("getOnlineUsers");
        }
    }

What is the solution?

Should you worry about it? No. The designer generated a partial class for you, the database context. The only thing you should be doing is either adding your functions manually into the automatically generated database context class file or create a new class file and define the partial class in it once again then add the additional methods.

I personally suggest you creating a new class file and extending your partial class. Manually changing the designer generated files has never been and will never be a good idea. Remember, the next time you use the designer, you changes might be overwritten!

Here is an example method executing a stored procedure on a MySQL database and returning the data as a C# List.

    public virtual List getOnlineUsers()
    {
        List data = ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery("CALL getOnlineUsers();").ToList();

        return data;
    }

Well, what about parameters?

Finally, if you need to pass parameters to your stored procedure, you might use the ExecuteStoreQuery method as below.

    public virtual List getOnlineUsers(int someParameter, DateTime someOtherParameter)
    {
        List data = ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery("CALL getOnlineUsers({0}, {1});", someParameter, someOtherParameter).ToList();

        return data;
    }