locked
How can I built an Entityframework which can work both MySQL and MsSQL dbs RRS feed

  • Question

  • User283528319 posted

    Hi all,

    I have MySQL <g class="gr_ gr_16 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="16" data-gr-id="16">db</g> behind my .net core app and have <g class="gr_ gr_17 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" id="17" data-gr-id="17">plan</g> to convert it to MsSQL in future. (detailed story but I have to).

    And because of the beauty of the <g class="gr_ gr_77 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="77" data-gr-id="77">ORM</g> I thought it would be magically easy.

    But it is not. <g class="gr_ gr_19 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" id="19" data-gr-id="19">Because</g> <g class="gr_ gr_18 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="18" data-gr-id="18">mysql</g>.data <g class="gr_ gr_21 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="21" data-gr-id="21">uses</g> <g class="gr_ gr_20 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="20" data-gr-id="20">sql</g> for data transfer <g class="gr_ gr_22 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="22" data-gr-id="22">unlike</g> <g class="gr_ gr_23 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="23" data-gr-id="23">entityframework</g>.

    look at the <g class="gr_ gr_25 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="25" data-gr-id="25">symplifed</g> code I use to update data to my MySQL <g class="gr_ gr_27 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="27" data-gr-id="27">db</g>. Am I doing something wrong? Should I install <g class="gr_ gr_29 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="29" data-gr-id="29">entityframework</g> package for MySQL and try to use it (which is shown in nowhere!).

    Any help would be appreciated.

      using (MySqlConnection conn = GetConnection())
                    {
                        conn.Open();
                        MySqlCommand cmd = new MySqlCommand("Update evraklar set " +
                           "gonderen=@gonderen, " +
                            "ebysno=@ebysno, " +
                            "gonderen_no=@gonderen_no, " +
                             "gondermetarihi=@gondermetarihi, " +                        
                            " where evraklar_id=@evraklar_id ", conn);
                  
                        cmd.Parameters.AddWithValue("@evraklar_id", id);
                        cmd.Parameters.AddWithValue("@gonderen",gonderen );
                        cmd.Parameters.AddWithValue("@ebysno", ebysno);
                        cmd.Parameters.AddWithValue("@gonderen_no", gonderen_no);
                                         
                        cmd.ExecuteNonQueryAsync();
                    }
    Saturday, January 26, 2019 7:50 AM

Answers

  • User1120430333 posted

    But it is not. Because mysql.data uses sql for data transfer unlike entityframework.

    You are wrong here. Any ORM its engine generates T-SQL or P-SQL, P-SQL used by Oracle, to be submitted and executed by the database engine The parameters that are used in the formulation of the T-SQL comes from the ORM's virtual/persistence model  object in the same parameterized manner you are doing it. You just can't see it being done under the hood.

    look at the symplifed code I use to update data to my MySQL db. Am I doing something wrong? Should I install entityframework package for MySQL and try to use it (which is shown in nowhere!).

    No,  you're doing nothing wrong. All you are doing is using ADO.NET, MySQL Command object and T-SQL. It's the same thing EF or any ORM would do using similar code under the hood that you don't see.

    But you could see it if using a profiler to look at the generated T-SQL the ORM generates. I am sure MySQL has a profiler too.

    https://entityframework.net/view-generated-sql

    Here is the difference about you doing it as oppsed to impleminting an ORM into the solution. . You have to code all  of it manually everything concerning CRUD for every database table you have,  as opposed the ORM,  that uses the virtual/persistence model,  the ORM's framework that does the CRUD for you by it generating the T-SQL that does it and it submits the T-SQL to the database engine for execution.

     https://blog.bitsrc.io/what-is-an-orm-and-why-you-should-use-it-b2b6f75f5e2a

    Let's say you have a MySQL database schema that has 50 or more tables that you have to use in an application you are developing. Are you going to sit there and write all of the code for 50 database tables to do CRUD (create, read, update and delete) operations for each table, or would the simple thing to do is implement an ORM in the solution and let the ORM do it?

    https://dev.mysql.com/doc/connector-net/en/connector-net-entityframework-core-example.html

    The above is a code first example, and you got to make those 50 objects for 50 tables. Maybe there is a Database first one somewhere that will make those 50 objects for you.

    It's either do it yourself using ADO.NET and MySQL command objects and T-SQL, use the ORM, or you write a Object Code Generator yourself that's going to make all of the objects and the T-SQL for CRUD that you manually have to implement the code the OCG generated.

    I have done all three ways, and I choose to go to the ORM. :)

    Oh, and if you  wanted to use MySQL or MS SQL server, then you would make a classlib project call it a Data Access Layer (DAL) and install EF for MySQL in it dedicated for MySQL. You create another classlib project,  a DAL,  and install EF for MS SQL Server in it.

    It's unclear as to the purpose of you wanting EF functionality for MySQL and MS SQL Server.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, January 26, 2019 9:02 AM
  • User283528319 posted

    Sorry for ignorance.

    with MySQL.Data and MySQL.Data.<g class="gr_ gr_29 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="29" data-gr-id="29">Entityframework</g> package everything is already solved 

    https://dev.mysql.com/doc/connector-net/en/connector-net-entityframework-core-scaffold-example.html

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, January 26, 2019 8:52 PM

All replies

  • User1120430333 posted

    But it is not. Because mysql.data uses sql for data transfer unlike entityframework.

    You are wrong here. Any ORM its engine generates T-SQL or P-SQL, P-SQL used by Oracle, to be submitted and executed by the database engine The parameters that are used in the formulation of the T-SQL comes from the ORM's virtual/persistence model  object in the same parameterized manner you are doing it. You just can't see it being done under the hood.

    look at the symplifed code I use to update data to my MySQL db. Am I doing something wrong? Should I install entityframework package for MySQL and try to use it (which is shown in nowhere!).

    No,  you're doing nothing wrong. All you are doing is using ADO.NET, MySQL Command object and T-SQL. It's the same thing EF or any ORM would do using similar code under the hood that you don't see.

    But you could see it if using a profiler to look at the generated T-SQL the ORM generates. I am sure MySQL has a profiler too.

    https://entityframework.net/view-generated-sql

    Here is the difference about you doing it as oppsed to impleminting an ORM into the solution. . You have to code all  of it manually everything concerning CRUD for every database table you have,  as opposed the ORM,  that uses the virtual/persistence model,  the ORM's framework that does the CRUD for you by it generating the T-SQL that does it and it submits the T-SQL to the database engine for execution.

     https://blog.bitsrc.io/what-is-an-orm-and-why-you-should-use-it-b2b6f75f5e2a

    Let's say you have a MySQL database schema that has 50 or more tables that you have to use in an application you are developing. Are you going to sit there and write all of the code for 50 database tables to do CRUD (create, read, update and delete) operations for each table, or would the simple thing to do is implement an ORM in the solution and let the ORM do it?

    https://dev.mysql.com/doc/connector-net/en/connector-net-entityframework-core-example.html

    The above is a code first example, and you got to make those 50 objects for 50 tables. Maybe there is a Database first one somewhere that will make those 50 objects for you.

    It's either do it yourself using ADO.NET and MySQL command objects and T-SQL, use the ORM, or you write a Object Code Generator yourself that's going to make all of the objects and the T-SQL for CRUD that you manually have to implement the code the OCG generated.

    I have done all three ways, and I choose to go to the ORM. :)

    Oh, and if you  wanted to use MySQL or MS SQL server, then you would make a classlib project call it a Data Access Layer (DAL) and install EF for MySQL in it dedicated for MySQL. You create another classlib project,  a DAL,  and install EF for MS SQL Server in it.

    It's unclear as to the purpose of you wanting EF functionality for MySQL and MS SQL Server.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, January 26, 2019 9:02 AM
  • User283528319 posted

    Sorry for ignorance.

    with MySQL.Data and MySQL.Data.<g class="gr_ gr_29 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="29" data-gr-id="29">Entityframework</g> package everything is already solved 

    https://dev.mysql.com/doc/connector-net/en/connector-net-entityframework-core-scaffold-example.html

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, January 26, 2019 8:52 PM