locked
Cannot seem to access database tables in MYSQL from ASP.NET MVC RRS feed

  • Question

  • User-782511613 posted

    Hi, 

    I am trying to access my database tables from my ASP.NET MVC using Entity framework. I have added the Entity Framework references to the project and updated the Web Config to include it in the connection strings. I am using VS 2019 with MYSQL 8.0.22. I can update my model from the database but am unable to access the tables from the controller. I am using a DataBase First feature of Entity. 

    Any ideas on what could be wrong?

    This is the error I am getting: MySqlException: Table 'justadatabase.justadatabase.justatable' doesn't exist

    I have a database - justadatabase and a table-justatable

    I have added the connectionstrings in the webconfig here: 

    <connectionStrings>
        <!--<add name="justadatabase" providerName="MySql.Data.MySqlClient" connectionString="Server=localhost;Port=3306;Database=justadatabase;Uid=root;Password=IamRoot!1;" />
            <add name="justadatabaseEntities" connectionString="metadata=res://*/Models.JustAModel.csdl|res://*/Models.JustAModel.ssdl|res://*/Models.JustAModel.msl;provider=MySql.Data.MySqlClient;provider connection string=&quot;server=localhost;user id=root;password=*******;persistsecurityinfo=True;database=justadatabase&quot;" providerName="System.Data.EntityClient" />-->
        <add name="justadatabase" providerName="MySql.Data.MySqlClient" connectionString="Server=localhost;Database=justadatabase;Port=3306;Uid=root;Password=*******;" />
        <add name="databaseEntities" connectionString="metadata=res://*/Models.JustAModel.csdl|res://*/Models.JustAModel.ssdl|res://*/Models.JustAModel.msl;provider=MySql.Data.MySqlClient;provider connection string=&quot;server=localhost;user id=root;password=IamRoot!1;persistsecurityinfo=True;database=justadatabase&quot;" providerName="System.Data.EntityClient" />
      </connectionStrings>

    and Entity Frame work and the DbProviders in the web config:

      <entityFramework>
        <defaultConnectionFactory type="MySql.Data.Entity.MySqlConnectionFactory, MySql.Data.EntityFramework" />
        <providers>
          <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.EntityFramework" />
        </providers>
      </entityFramework>
      <system.codedom>
        <compilers>
          <compiler extension=".cs" language="c#;cs;csharp" warningLevel="4" compilerOptions="/langversion:7.3 /nowarn:1659;1699;1701;612;618" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=3.6.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
          <compiler extension=".vb" language="vb;vbs;visualbasic;vbscript" warningLevel="4" compilerOptions="/langversion:default /nowarn:41008,40000,40008 /define:_MYTYPE=\&quot;Web\&quot; /optionInfer+" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=3.6.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
        </compilers>
      </system.codedom>
      <system.data>
        <DbProviderFactories>
          <remove invariant="MySql.Data.MySqlClient" />
          <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=8.0.22, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
        </DbProviderFactories>
      </system.data>

    I can hardcode the sql connection and query using something similar to this:

    https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-sql-command.html

    But with entity framework I keep running into the table not found error. 

    Trying to access it like this:

    List<justatable> jT = new List<justatable>();
                using (databaseEntities dbE = new databaseEntities())
                {
                    jT = dbE.justatables.ToList();
                    return View(jT);
                }

    Wednesday, October 28, 2020 6:06 PM

All replies

  • User-474980206 posted

    what is the issue? connection failure? query failure? any error message?

    Wednesday, October 28, 2020 7:22 PM
  • User1686398519 posted

    Hi SindhushaD, 

    What is the problem you encountered? Do you want to know how to read and write data to the database?

    The classes you are going to use to access data are being automatically generated for you based on the EDMX file.

    You can create a new instance of the context and then use it to insert new data.

    You can refer to the example in this link:Reading & Writing Data.

    Best Regards,

    YihuiSun

    Thursday, October 29, 2020 2:18 AM
  • User-34587282 posted

    Hi there, I am struggling with this issue too.

    The problem is that the FROM clause in sql queries created with Entity Framework are made with schema name not only table name.

    With older client libraries the FROM clause was made like this "FROM `justable`" with newest libraries it is made like this "FROM `justdatabase.justable`" ie "FROM `schema.table`"

    Table 'justadatabase.justadatabase.justatable' doesn't exist means that MySql client libraries cannot access table justatable in database justdatabase schema justdatabase.

    Any advices would be appreciated!

    Thanks!

    Saturday, December 5, 2020 11:28 AM