How to connect to MySQL server using Entity Framework Core

2022-05-06 22:44 OpenStack imported from Stackoverflow

I am using Visual Studio 2019 community edition. I want to connect to MySQL using Entity Framework core. I do not want to use Entity Framework 6.

I am running into following issues:

I created a new project using "ASP .NET CORE App and ASP .NET CORE Web App" template and it does not show option to add Entity Framework.

If I use tools > Connect to Database option from menu, I do not see option to connect to MySQL. How can I enable this option.

4 answers

answered 2022-05-07 02:04 Bradley Grainger
Install "Pomelo.EntityFrameworkCore.MySql" using the NuGet Package Manager in Visual Studio 2019.

Then follow the instructions to configure it in your project: https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/#getting-started

Disclaimer: I have made a small number of contributions to the Pomelo package.
answered 2022-05-07 04:30 Garrett
EFCore is a package, not a file you would add to your project. Go to Project>NuGet Package Manager and install Microsoft.EntityFrameworkCore, Microsoft.EntityFrameworkCore.Tools. Then you can scaffold the connection to your database by running the scaffold-dbcontext command in the PM console.
answered 2022-05-07 15:27 Hilal
did you try installing through the console?

Install-Package Microsoft.EntityFrameworkCore -Version 5.0.11

make sure that this source is added to your nuget sources in VS https://api.nuget.org/v3/index.json
answered 2022-05-07 17:18 thisisnabi
Step by Step - Database First

First install these packages
1. Microsoft.EntityFrameworkCore.Design
So on Powershell go to project folder [right click on project and select open in terminal (visual studio)]

Now, you can run this command

dotnet ef dbcontext scaffold "Servel=localhost;Database=tempSQLonNetCore;user=root;password=;" "Pomelo.EntityFrameworkCore.MySql"

If your connection is true, your DbContext Generated and entities adding to your project.

Now you must inject DbContext, Described in the Codefirst section

Step by Step - Code First

First install these packages
1. Microsoft.EntityFrameworkCore
2. Microsoft.EntityFrameworkCore.Tools
3. Pomelo.EntityFrameworkCore.MySql
Add connection string look like in appsetting.json
```
"ConnectionStrings" : {
    "DefaultConnection" : "Servel=localhost;Database=tempSQLonNetCore;user=root;password=;"
}
```
**Now, Create your DB context **
```
public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
    {

    }

    // Your Entities
}
```
finally configure the app for connecting
```
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
builder.Services.AddDbContext<ApplicationDbContext>(options => {
     options.UseMySql(connectionString, ServerVersion.AutoDetect(connectionString));
});
```
Now you can use Migration if you need to create or update your database Migration

See also questions close to this topic

C# - Adding condition to func results in stack overflow exception
I have a func as part of specification class which sorts the given iqueryable
```
Func<IQueryable<T>, IOrderedQueryable<T>>? Sort { get; set; }
```
When i add more than one condition to the func like below , it results in stack overflow exception.
```
spec.OrderBy(sc => sc.Case.EndTime).OrderBy(sc => sc.Case.StartTime);
```
The OrderBy method is implemented like this
```
public ISpecification<T> OrderBy<TProperty>(Expression<Func<T, TProperty>> property)
    {
        _ = Sort == null ? Sort = items => items.OrderBy(property) : Sort = items => Sort(items).ThenBy(property);
        return this;
    }
```
Chaining or using separate lines doesn't make a difference.

This problem gets resolved if I assign a new instance of the specification and set it's func, but i don't want to be assigning to a new instance everytime. Please suggest what am i missing here and how to reuse the same instance (if possible).

How to projection fields for a dictionary (C#, MongdoDB)

I am trying my luck here, I have a model which is like the following

public class RowData : BaseBsonDefinition
{
.
  [BsonExtraElements]
  [BsonDictionaryOptions(DictionaryRepresentation.ArrayOfDocuments)]
  public Dictionary<string, object> Rows { get; set; } = new(StringComparer.OrdinalIgnoreCase);
.
}

In result, the schema in the MongoDB looks like

{
"_id": {
    "$binary": {
      "base64": "HiuI1sgyT0OZmcgGUit2dw==",
      "subType": "03"
    }
  },
  "c1": "AAA",
  "c8": "Fully Vac",
  "c10": "",
}

Those c1, c8 and c10 fields are keys from the dictionary, my question is how to dynamic project those fields?

I tried

Builders<RowData>.Projection.Exclude(p => "c1")

It seems the MongoDB driver can not handle a value directly.

Anyone could point me in the correct direction?

Thanks,

How do I add new DataSource to an already Databinded CheckBoxList

i'm building a web form that show Database's item(Tables, Rows, FK,...)

I have a CheckBoxList of Tables (chkListTable) which will show a new CheckBoxList of Rows (chkListRow) everytime I SelectedIndexChanged from chkListTable. The problem is i can show the items from chkListTable with 1 selected item. But i don't know how to show chkListRow if multiple item from chkListTable are selected.

Here are my codes:

aspx:

<div>
             <asp:Label ID="Label2" runat="server" Text="Table: "></asp:Label>
             <asp:CheckBoxList ID="chkListTable" runat="server"
                 DataTextField="name"
                 DataValueFeild="name"
                 AutoPostBack="true"
                 OnSelectedIndexChanged="chkListTable_SelectedIndexChanged">
             </asp:CheckBoxList>
        </div>
        <div>
             <asp:CheckBoxList ID="chkListRow" runat="server"
             DataTextField="COLUMN_NAME"
             DataValueField="COLUMN_NAME"
             RepeatDirection="Horizontal">
             </asp:CheckBoxList>
        </div>

aspx.cs:

protected void chkListTable_SelectedIndexChanged(object sender, EventArgs e)
    {
        tableName.Clear();
        
        foreach (ListItem item in chkListTable.Items)
        {
            if(item.Selected)
            {
                tableName.Add(item.Text.Trim());
            }    
        }

        for(int i = 0; i < tableName.Count; i++)
        {
            String query = "USE " + dbname +
                " SELECT * FROM information_schema.columns" +
                " WHERE table_name = '" + tableName[i] + "'" +
                " AND COLUMN_NAME != 'rowguid'";
            chkListRow.DataSource = Program.ExecSqlDataReader(query);
            chkListRow.DataBind(); 
            Program.conn.Close();
        }
    }

Program.cs:

public static bool Connect()
    {
        if (Program.conn != null && Program.conn.State == ConnectionState.Open)
            Program.conn.Close();
        try
        {
            Program.conn.ConnectionString = Program.constr;

            Program.conn.Open();

            return true;
        }

        catch (Exception e)
        {
            return false;
        }
    }

    public static SqlDataReader ExecSqlDataReader(String query)
    {
        SqlDataReader myreader;
        SqlCommand sqlcmd = new SqlCommand(query, Program.conn);
        sqlcmd.CommandType = CommandType.Text;
        if (Program.conn.State == ConnectionState.Closed) Program.conn.Open();
        try
        {
            myreader = sqlcmd.ExecuteReader();
            return myreader;
            myreader.Close();
        }
        catch (SqlException ex)
        {
            Program.conn.Close();
            return null;
        }
    }

I want my display to be like this:

[x]Table1 [x]Table2 [ ]Table3
[ ]Row1(Table1) [ ]Row2(Table1) [ ]Row3(Table1)
[ ]Row1(Table2) [ ]Row2(Table2)

Why does this Sql query shows the possible changes but does not implement it?

So, I want to change the prefix of my tables and the following command shows the possible changes that will take place which seems alright but does not seem to implement it.

SELECT Concat('RENAME TABLE ', TABLE_NAME, ' TO fan_', SUBSTRING_INDEX(TABLE_NAME, 'pc_',-1), ';') FROM information_schema.tables WHERE table_name like 'pc_%' and table_schema='testdbhere'

Moreover, this isn't a writing privilege issue as changing the tables name individually works perfectly from the same user.

how to get weeklytotal and yesterday record in mysql in one table

Hi Everyone i am trying to implement query to get weekly and yesterday data in same table, dummy output i have shared below, if yesterday not exist as per employee_id it should we showing 0 also as per my table week start from monday and end at sunday.please help me out how to query this get weekly_total and yesterday record and one table.

Table name-dailydata-

Sample data

employee_id	date	total
20	2022-04-25	10
20	2022-04-26	20
20	2022-04-27	20
20	2022-04-28	10
20	2022-04-29	20
20	2022-04-30	30
20	2022-04-31	40
20	2022-05-01	50
40	2022-04-26	20

expected output

employee_id	weekly_total	yesterday_record
20	200	40
40	20	0

mysql query to get weekly data

select employee_id,sum(total) as week_total from dailydata where date between '2022-04-25' and '2022-05-01'

Join to another table only matching specific records

I have a table of ports:

drop table if exists ports;
create table ports(id int, name char(20));

insert into ports (id, name ) values
  (1, 'Port hedland'), 
  (2, 'Kwinana');

And a table of tariffs connected to those ports:

drop table if exists tariffs;
create table tariffs(id int, portId int, price decimal(12,2), expiry bigint(11));

insert into tariffs (id, portId, price, expiry ) values
    (1, 2, 11.00, 1648408400), 
    (2, 2, 12.00, 1648508400), 
    (3, 2, 13.00, 1648594800), 
    (4, 2, 14.00, 1651273200),
    (5, 2, 15.00, 2250000000 );

insert into tariffs (id, portId, price, expiry ) values
    (1, 1, 21.00, 1648408400), 
    (2, 1, 22.00, 1648508400), 
    (3, 1, 23.00, 1648594800), 
    (4, 1, 24.00, 1651273200),
    (5, 1, 25.00, 2250000000 );

Each tariff has an expiry.

I can easily make a query to figure out the right tariff for as specific date for each port. For example at timestamp 1648594700 the right tariff is:

SELECT * FROM tariffs 
WHERE 1648594700 < expiry AND portId = 2
ORDER BY expiry
LIMIT 1

Result:

id  portId  price   expiry
3   2   13.00   1648594800

However, in my application I want to be able to pull in the right tariff starting from the ports record.

For one record, I can do this:

SELECT * FROM ports 
LEFT JOIN tariffs on tariffs.portId = ports.id 
WHERE 1648594700 < tariffs.expiry AND ports.id = 2
LIMIT 1

Result:

id  name    id  portId  price   expiry
2   Kwinana 3   2   13.00   1648594800

This feels a little 'dirty', especially because I am doing a lookup on a record, and then forcing only one result using LIMIT. But, OK.

What I cannot do, and can't work out how to do, is a query that will return a list of ports, and each port having a price field that matches the constraint above (that is, the record with the highest expiry compared to 1648594700 for each port).

This obviously won't work:

SELECT * FROM ports 
left join tariffs on tariffs.portId = ports.id
where 1648594700 < tariffs.expiry

Since the result of the query, testing with timestamp 1648594700, would be:

id  name    id  portId  price   expiry
2   Kwinana 3   2   13.00   1648594800
2   Kwinana 4   2   14.00   1651273200
2   Kwinana 5   2   15.00   2250000000
1   Port he 3   1   23.00   1648594800
1   Port he 4   1   24.00   1651273200
1   Port he 5   1   25.00   2250000000

Instead, the result for all ports (before further filtering) should be:

id  name    id  portId  price   expiry
2   Kwinana 3   2   13.00   1648594800
1   Port he 3   1   23.00   1648594800

Is there a clean, non-hacky way to have such a result? As an added constraint, is this possible for this to be done in ONE query, without temp tables etc.?

site only opens in CefSharp

zaakr.net is a site can be opened only in zaakr.exe application or through android(.apk), when opened in browser u been redirected to https://browser.zaakr.net so u download there application, (.exe) file is using cefsharp as a browser so it can access website, from 2 month i could open it from chromium as cefsharp uses chromium, now i can't access website through chromium for some reason idk, i even made a cefsharp browser myself using visual studio nothing worked all the time i been redirected to browser.zaakr.com, i want to access through any browser that have devtools available for a project, note that application zaakr.exe is still using cefsharp even the older version still can access site. i would appreciate any help, sry for any spelling mistakes, thanks.
C# MariaDB Update Function working in DataStore but not updating in .NET Application
My initial issue is that my database does not update when I call on this specific code. I am not sure if it is C# itself or the update query that I am calling.
```
string _connectionString = "validConnectionstring";

using (MySqlConnection _mySqlConnection = new MySqlConnection(_connectionString)
{
_mySqlConnection.Open();
using (MySqlCommand command = new MySqlCommand("UpdateProfileStatus", _mySqlConnection))
{
    command.Transaction = _mySqlConnection.BeginTransaction();
    command.CommandTimeout = TimeSpan.FromSeconds(60).Seconds;
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddWithValue("@_username", "test");
    command.Parameters.AddWithValue("@_status", true);
    command.ExecuteNonQuery();
    
}
_mySqlConnection.Close();
}
```
I get no updates in my database but my console logs that the query is executed returning the value of 1 but there is no update that actually happens in my DB. Is there something in my code to reason why it is failing?

Here is the stored procedure that I have for the update command
```
CREATE PROCEDURE UpdateProfileStatus(_username VARCHAR(25), _status BOOL)
UPDATE Profile SET status = _status WHERE username = _username;
```
I know the Stored Procedure works but am not sure why my .NET application is not responding to my procedure call. Is it something to do with my implementation of the parameters or is it my procedure itself?

Image of DataStore Setting within MariaDB
How to add a new Model visually in Mac OS visual Studio
I do not see any choice to create a model in Mac visual studio? I In the windows version the Class created with its imports
- using Linq
  
  using Threading.Tasks
  
  using Collection.Generic
C# ef code first How to have a one-to-one relationship with

How to make a one-to-one relationship with code first in c# desktop application, can you help
LINQ select column that contains some array element
I have a field "details" varchar(MAX) in my table with the detailed description of an item, I need to select the records that contain the keywords passed by the user in a string array.
```
var searchTerms = new List<string> { "car", "black", "bike", "blue" };
```
I tried to do it like this:
```
var result = (from i in _contexto.itens
where searchTerms.Any(d => i.details.Contains(d))
select i.id);
```
and I get the following error:

The LINQ expression 'DbSet() .Where(i => __searchTerms_0 .Any(d => i.details.Contains(d)))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

I also tried this link's approach using the "SearchExtensions nuget package" and the same error occurs.

How can I do this? or should I make a query for each item in the array?

Example of a text I want to search for:

It is a long established fact that a car will be distracted by the readable content of a page when looking at its bike. The point of using Lorem Ipsum is that it has a more-or-less normal distribution

Thanks!

How to use a database connection string in App.config?

I use EF 6, and if I use a hardcode database connection in the OnConfiguring:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer("Data Source=.\\sqlexpress;Initial Catalog=MyOrders;Persist Security Info=True;User ID=sa;Password=***4n**user");
            }
        }

the code will work well.

but if I use a connect string in App.config:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseSqlServer(ConfigurationManager.AppSettings["DataBase"].ToString().Trim());                
        }
    }

it will not work.

the connect string in App.config:

    <add key="DataBase" value="Data Source=.\\sqlexpress;Initial Catalog=MyOrders;Persist Security Info=True;User ID=sa;Password=pk94newuser"/>

the error message:

Instance failure.

the StrackTrace:

at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover, SqlAuthenticationMethod authType)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open()
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.OpenDbConnection(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternal(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__19_0(DbContext _, Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Linq.Enumerable.TryGetSingle[TSource](IEnumerable`1 source, Boolean& found)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)

I want to know how to use a connect string in EF 6 correctly. Thanks!

GetColumnName() in EF Core 6 returns property name not mapped column name
I'm using EF Core 6 (6.0.2) with SQL Server 2019 with the following entity:
```
public partial class MyEntity
{
        [Column("External_ExternalId")]
        public Guid? ExternalId { get; set; }
}
```
I'm trying to get the mapped column name (External_ExternalId) for the ExternalId property with the following code:
```
var entityType = dbContext.Model.FindEntityType(typeof(MyEntity));
var property = entityType.GetProperties().Single(property => property.Name.Equals("ExternalId", StringComparison.OrdinalIgnoreCase));
var columnName = property.GetColumnName(StoreObjectIdentifier.Create(property.DeclaringEntityType, StoreObjectType.Table).GetValueOrDefault());
```
columnName is ExternalId instead of External_ExternalId. How can I get the actual name of the table's column? I tried GetColumnBaseName() but got the same result. Same if I configure the mapping with the fluent API.

Related questions

How to connect to MySQL server using Entity Framework Core

4 answers

If your connection is true, your DbContext Generated and entities adding to your project.

See also questions close to this topic