Object Relational Mapping (ORM) between MySQL database and c# objects in Visual Studio using LINQ

Question

I am trying to establish an ORM between a MySQL database and c# objects in Visual Studio

I have installed the following packages to add MySQL Database to the data sources list:

Connector/NET 8.0.16: https://dev.mysql.com/downloads/connector/net/
MySQL for Visual Studio 1.2.8: https://dev.mysql.com/downloads/windows/visualstudio/

following the recommendations in this post: How to connect to a MySQL Data Source in Visual Studio

I have created the data source and my code looks like this ...

Connection string in app.config:

  <connectionStrings>
    <add name="MyMapper.Properties.Settings.myConnectionString" connectionString="server=my-db-instance.xxxxxxxxxxxx.eu-west-1.rds.amazonaws.com;user id=my_user_id;password=my_user_password;persistsecurityinfo=True;database=my_database" providerName="MySql.Data.MySqlClient" />
  </connectionStrings>

DataContext in MyDataContext.cs:

namespace MyMapper
{
   [Database]
   public class MyDataContext : DataContext
   {
      public MyDataContext() : base(Properties.Settings.Default.myConnectionString) { }
      public Table<ElementId> ElementIds;
   }
}

Mapped class in ElementId.cs:

namespace MyMapper
{
   [Table(Name = "ElementId")]
   public class ElementId
   {
      [Column(Name = "IntegerValue", IsPrimaryKey = true)]
      public int IntegerValue { get; set; }
   }
}

Insert operation in MyController.cs:

ElementId my_element_id = new ElementId();
my_element_id.IntegerValue(88);

using (MyDataContext my_data_context = new MyDataContext())
{
   my_data_context.ElementIds.InsertOnSubmit(my_element_id);
   my_data_context.SubmitChanges();
}

When executing SubmitChanges() the following exception is thrown:

System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server

It seems that the problem is the connection with the database

Update answering proposed solution in Why am I getting "Cannot Connect to Server - A network-related or instance-specific error"?: I am not trying to connect to a SqlConnection but to a MySqlConnection

Possible duplicate of [Why am I getting "Cannot Connect to Server - A network-related or instance-specific error"?](https://stackoverflow.com/questions/18060667/why-am-i-getting-cannot-connect-to-server-a-network-related-or-instance-speci) — SᴇM, Jun 20 '19 at 11:55
Looks like you have used SqlConnection instead of MySqlConnection — Chetan, Jun 20 '19 at 11:55
https://dev.mysql.com/doc/connector-net/en/connector-net-entityframework60.html — Chetan, Jun 20 '19 at 11:58
@SᴇM I was not trying to connect to a SqlConnection but to a MySqlConnection — j.xavier.atero, Jun 20 '19 at 12:16
Is this using System.Data.Linq (Linq to SQL)? That framework is Microsoft SQL Server only. — Stephen Kennedy, Jun 22 '19 at 23:22

j.xavier.atero · Accepted Answer · 2019-06-26T21:14:58.170

The data context connection was not a MySqlConnection

namespace MyMapper
{
   [Database]
   public class MyDataContext : DataContext
   {
      public MyDataContext() : base(new MySqlConnection(Properties.Settings.Default.myConnectionString)) { }
      public Table<ElementId> ElementIds;
   }
}

but now the following exception is thrown:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '[ElementId]([IntegerValue])
VALUES (88)' at line 1

it looks that the query is not being formatted as MySQL

UPDATE: MySQL support requires NuGet Packages:

A fully-managed ADO.NET driver for MySQL. The most popular options are:
- MySQL.Data: https://dev.mysql.com/downloads/connector/net/ (NuGet: https://www.nuget.org/packages/MySql.Data/)
- SQLite: https://www.sqlite.org/index.html (NuGet: https:// www.nuget.org/packages/System.Data.SQLite/ or https:// www.nuget.org/packages/System.Data.SQLite.Core/)
Entity Framework (https://learn.microsoft.com/en-us/ef/)
- Entity Framework: https://www.nuget.org/packages/EntityFramework/
- Entity Framework Core: https:// www.nuget.org/packages/Microsoft.EntityFrameworkCore/

I have used MySQL.Data and Entity Framework 6 (EF6): https://dev.mysql.com/doc/connector-net/en/connector-net-entityframework60.html

The reason to choose EF6 is because EF Core does support Type Per Type (https://entityframework.net/tpt) at the time of writing this answer (https://github.com/aspnet/EntityFrameworkCore/issues/2266)

DataContext in MyDataContext.cs:

namespace MyMapper
{
   [DbConfigurationType(typeof(MySqlEFConfiguration))]
   public class MyDataContext : DbContext
   {
      public DbSet<BaseClass> BaseClass{ get; set; }
      public DbSet<ElementId> ElementId { get; set; }

      public MyDataContext () : base(new MySqlConnection(Properties.Settings.Default.myConnectionString).ConnectionString) { }

      protected override void OnModelCreating(DbModelBuilder dbModelBuilder)
      {
         dbModelBuilder.Conventions.Remove<PluralizingTableNameConvention>(); // to avoid pluralization of the tables

         // EntityTypeConfiguration<T>
         dbModelBuilder.Configurations.Add(new BaseClassConfiguration());
         dbModelBuilder.Configurations.Add(new ElementIdConfiguration());

      }
   }
}

Object Relational Mapping (ORM) between MySQL database and c# objects in Visual Studio using LINQ

1 Answers1