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;
    }

6 Responses

  1. dulei says:

    Thank You!

  2. meanbunny says:

    Seriously thank you! Been looking for this for a couple of hours

  3. Nisha says:

    Many Thanks! It perfectly worked.

  4. Alaa says:

    Hi, Thanks you, but i want to retrieve data into datatable

  5. Malik Ahmed says:

    Thanks a lot, i had been looking for this solution entire day. You saved me.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.