C# MySQL EF 6 Code First Migrations Create Custom Stored procedure with dynamic where clause prepare statement gives error

Question

I'm using MySql 5.5 database. I have created my database using Entity Framework code first migrations and created default stored procedures for Insert Update and delete operations using the MapToStoredProcedures() method in DBContext class. I'm trying to add a custom migration class. Code snippet below:

using System;
using System.Data.Entity.Migrations;

public partial class Views : DbMigration
{
    public override void Up()
    {
        Sql(@"
            CREATE VIEW View_Jobs
            AS 
            select 
            job.JobId as JobId,
            job.JobNo as JobNo,
            ....";
        );

        CreateStoredProcedure(
           "dbo.GetOpenJobs",
           p => new
           {
               UserId = p.Long(),
               RoleId = p.Long()
           },
           body:
            @"
            DECLARE query varchar(1000);
            DECLARE strWhere varchar(100);

            set @query = 'select JobId, JobNo, ... from view_jobs  ';

            IF RoleId <> 1 THEN
                set @query = concat(@query, ' WHERE AssignedTo = ', UserId, ' AND AssignedToRole = ', RoleId, ' ');  
            end if;

            set @query = concat(@query, ' group by JobId, JobNo,... ');

            select @query;

            PREPARE stmt FROM @query;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;"
        );
    }

    public override void Down()
    {
        DropStoredProcedure("dbo.GetOpenJobs");
        Sql(@"DROP VIEW IF EXISTS view_jobs;");
    }
}

When i use Update-Database command, the view is getting created properly in database but for creating stored procedure i'm receiving below error.

"You have an error in your SQL syntax; check the manual that corresponds to     your MySQL server version for the right syntax to use near 'query;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
 END' at line 19"

The same stored proc is working fine in the database. Even though i have mentioned @query in my .NET code, while updating database it is considering as query without @ symbol.

Here is one work around of creating them in seed method. But i dont prefer this. Can you create sql views / stored procedure using Entity Framework 4.1 Code first approach

Does anyone know how to resolve this or any work around available apart from the above link shared?

Vlakshmi · Accepted Answer · 2016-07-08T06:27:47.503

0

CreateStoredProcedure() though intended to create stored procedure in mysql database doesnt work properly if we use the Prepare statement in it.

Instead of using CreateStoredProcedure() method, I used Sql() to create the stored procedure and finally it worked.

Also in the connection string, Allow User Variables=true should be specified without which this will not work.

edited Jul 08 '16 at 06:27

answered Jul 06 '16 at 09:38

Vlakshmi

1
2

C# MySQL EF 6 Code First Migrations Create Custom Stored procedure with dynamic where clause prepare statement gives error

1 Answers1