Can't connect to mysql database using entity framework

Question

I'm trying to use entity framework, but I can't connect to my database.

Steps I have followed:

Add ADO.NET entity data model and select Mysql, input the correct data, testing connection and then selecting the database table I wanted
Visual Studio created a file named "DatabaseContext" and a file for each table selected.

DatabaseContext:

    namespace TranscoopTrips.Database
    {
        using System.Data.Entity;

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

    public virtual DbSet<address> addresses { get; set; }
    public virtual DbSet<driver> drivers { get; set; }
    public virtual DbSet<journey> journeys { get; set; }
    public virtual DbSet<vehicle> vehicles { get; set; }
    public virtual DbSet<expeditionpoint> expeditionpoints { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<address>()
            .Property(e => e.name)
            .IsUnicode(false);

        modelBuilder.Entity<address>()
            .HasMany(e => e.expeditionpoints)
            .WithOptional(e => e.address)
            .HasForeignKey(e => e.fk_address)
            .WillCascadeOnDelete();

        modelBuilder.Entity<driver>()
            .Property(e => e.name)
            .IsUnicode(false);

        modelBuilder.Entity<driver>()
            .Property(e => e.surname)
            .IsUnicode(false);

        modelBuilder.Entity<driver>()
            .HasMany(e => e.journeys)
            .WithRequired(e => e.driver)
            .HasForeignKey(e => e.fk_driver);

        modelBuilder.Entity<journey>()
            .Property(e => e.measureunit)
            .IsUnicode(false);

        modelBuilder.Entity<journey>()
            .Property(e => e.customer)
            .IsUnicode(false);

        modelBuilder.Entity<journey>()
            .HasMany(e => e.expeditionpoints)
            .WithOptional(e => e.journey)
            .HasForeignKey(e => e.fk_journey)
            .WillCascadeOnDelete();

        modelBuilder.Entity<vehicle>()
            .Property(e => e.plate)
            .IsUnicode(false);

        modelBuilder.Entity<vehicle>()
            .HasMany(e => e.journeys)
            .WithRequired(e => e.vehicle)
            .HasForeignKey(e => e.fk_vehicle);
        }
      }
    }

Example of class generated automatically:

namespace TranscoopTrips.Database
{
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;

[Table("transcooptrips.driver")]
public partial class driver
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public driver()
    {
        journeys = new HashSet<journey>();
    }

    [Column(TypeName = "text")]
    [Required]
    [StringLength(65535)]
    public string name { get; set; }

    [Column(TypeName = "text")]
    [Required]
    [StringLength(65535)]
    public string surname { get; set; }

    public int id { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<journey> journeys { get; set; }
}
}

Tried to connect to the database in the main program

    using (var db = new DatabaseContext())
    {

        Console.Write(db.Database.Connection.State); //return always "Closed"
        Console.WriteLine();
    }

4: Connection string created by visual studio

<connectionStrings>
    <add name="Database1" connectionString="server=localhost;user id=root;password=asdasdasd;persistsecurityinfo=True;database=transcooptrips" providerName="MySql.Data.MySqlClient" />
  </connectionStrings>

The output of Console.Write(db.Database.Connection.State); is always "Closed".

What did I do wrong?

Instead of posting screen captures you really should just copy and paste code into the question. — juharr
– juharr, Commented Sep 22, 2019 at 15:30
Oh, ok, no problem. I thought the screenshot was better because the code is all autogenerated — Fanto88
– Fanto88, Commented Sep 22, 2019 at 15:36

Maurice Pheyton · Accepted Answer · 2019-09-22 16:10:42Z

3

This is from another forum someone had the same problem and this was the solution for it:

Install Nuget Package:

Install-Package EntityFramework Install-Package MySql.Data.Entity -Version 6.9.9 Install MySQL for Visual Studio 1.2.6 - https://dev.mysql.com/downloads/windows/visualstudio/

Changes in Web.Config

<EntityFramework>

to:

<EntityFramework codeConfigurationType = "MySql.Data.Entity.MySqlEFConfiguration, MySql.Data.Entity.EF6">

Add (** your information **):

<connectionStrings>
<add name="**YourContextName**" connectionString="server=**xxx.xxx.xxx.xxx**;port=3306;user id=**your user**;password=**your password**;database=**your database**" providerName="MySql.Data.MySqlClient" /></connectionStrings>

Restart Visual Studio

My Settings: - Microsoft Visual Studio Community 2015 - Dot Net Framework 4.5.2 - Asp.Net MVC 5.2.3.0 - MySql Server 5.6

answered Sep 22, 2019 at 16:10

Maurice Pheyton

1117 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Fanto88 Over a year ago

I have reinstalled Visual studio and Mysql server and now works fine. Thanks for the help, don't know what the problem was, but a clean setup solved it.

Maurice Pheyton Over a year ago

I'm very glad to hear that @Fanto88

cactus4 · Accepted Answer · 2019-09-23 03:27:20Z

0

A quick suggestion:

Check if your sql services are running or not.
Check if the database name is correct or not - it seems your actual database name is "transcooptrips" and you have passed "name=Database1" to the base class. The most preferred way to define such constants is in appsettings.json.

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

edited Sep 23, 2019 at 3:27

cactus4

1289 bronze badges

answered Sep 22, 2019 at 15:55

arvind singh

13 bronze badges

2 Comments

arvind singh Over a year ago

public DatabaseContext (DbContextOptions<DatabaseContext > options) : base(options) { }

Fanto88 Over a year ago

Thanks for the comment, but mysql service is running fine. Name=Database1 refers to the connection string named Database1 that actually has all the correct parameters

Collectives™ on Stack Overflow

Can't connect to mysql database using entity framework

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related