pomelofoundation/pomelo.entityframeworkcore.mysql

Entity Framework Core provider for MySQL and MariaDB built on top of MySqlConnector

Pomelo.EntityFrameworkCore.MySql

Pomelo.EntityFrameworkCore.MySql is the most popular Entity Framework Core provider for MySQL compatible databases. It supports EF Core up to its latest version and uses MySqlConnector for high-performance database server communication.

Compatibility

Dependencies

The following versions of MySqlConnector, EF Core, .NET (Core), .NET Standard and .NET Framework are compatible with published releases of Pomelo.EntityFrameworkCore.MySql:

Release	Branch	MySqlConnector	EF Core	.NET (Core)	.NET Standard	.NET Framework
9.0.0- preview.1	main	>= 2.3.5	9.0.0- preview.1	8.0+	-	-
8.0.2	8.0-maint	>= 2.3.5	8.0.2	8.0+	-	-
7.0.0	7.0-maint	>= 2.2.5	7.0.x	6.0+	-	-
6.0.3	6.0-maint	>= 2.1.2	6.0.x	6.0+	-	-
5.0.4	5.0-maint	>= 1.3.13	5.0.x	3.0+	2.1	-
3.2.7	3.2-maint	>= 0.69.10 < 1.0.0	3.1.x	2.0+	2.0	4.6.1+

Packages

• Pomelo.EntityFrameworkCore.MySql
• Pomelo.EntityFrameworkCore.MySql.Json.Microsoft
• Pomelo.EntityFrameworkCore.MySql.Json.Newtonsoft
• Pomelo.EntityFrameworkCore.MySql.NetTopologySuite

Supported Database Servers and Versions

Pomelo.EntityFrameworkCore.MySql is tested against all actively maintained versions of MySQL and MariaDB. Older versions (e.g. MySQL 5.7) and other server implementations (e.g. Amazon Aurora) are usually compatible to a high degree as well, but are not tested as part of our CI.

Officially supported versions are:

• MySQL 8.0
• MariaDB 11.3
• MariaDB 11.2
• MariaDB 11.1
• MariaDB 11.0
• MariaDB 10.11 (LTS)
• MariaDB 10.6 (LTS)
• MariaDB 10.5 (LTS)
• MariaDB 10.4 (LTS)

Schedule and Roadmap

Milestone	Status	Release Date
9.0.0-preview.1	Released	2024-02-28 (see #1841)
8.0.2	Released	2024-03-16
7.0.0	Released	2023-01-16
6.0.3	Released	2024-03-16
5.0.4	Released	2022-01-22
3.2.7	Released	2021-10-04

Nightly Builds

To use nightly builds from our Azure DevOps feed, add a NuGet.config file to your solution root with the following content and enable prereleases:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <packageSources>
        <add key="pomelo-nightly" value="https://pkgs.dev.azure.com/pomelo-efcore/Pomelo.EntityFrameworkCore.MySql/_packaging/pomelo-efcore-public/nuget/v3/index.json" />
        <add key="nuget.org" value="https://api.nuget.org/v3/index.json" />
    </packageSources>
</configuration>

Feeds

Feeds that contain optimized (Release configuration) builds:

• https://pkgs.dev.azure.com/pomelo-efcore/Pomelo.EntityFrameworkCore.MySql/_packaging/pomelo-efcore-public/nuget/v3/index.json
• https://www.myget.org/F/pomelo/api/v3/index.json

Feeds that contain debugging enabled unoptimized (Debug configuration) builds:

• https://pkgs.dev.azure.com/pomelo-efcore/Pomelo.EntityFrameworkCore.MySql/_packaging/pomelo-efcore-debug/nuget/v3/index.json
• https://www.myget.org/F/pomelo-debug/api/v3/index.json

The AZDO nupkg packages always contain .pdb files.

The MyGet nupkg packages only contain .pdb files for their debug builds. For optimized builds, the symbols are packed in a snupkg file and are available via the https://www.myget.org/F/pomelo/api/v2/symbolpackage/ symbol server URL.

All .pdb files use Source Link.

Getting Started

1. Project Configuration

Ensure that your .csproj file contains the following reference:

<PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="8.0.2" />

2. Services Configuration

Add Pomelo.EntityFrameworkCore.MySql to the services configuration in your the Startup.cs file of your ASP.NET Core project:

public class Startup
{
    public void ConfigureServices(IServiceCollection services)
    {
        // Replace with your connection string.
        var connectionString = "server=localhost;user=root;password=1234;database=ef";

        // Replace with your server version and type.
        // Use 'MariaDbServerVersion' for MariaDB.
        // Alternatively, use 'ServerVersion.AutoDetect(connectionString)'.
        // For common usages, see pull request #1233.
        var serverVersion = new MySqlServerVersion(new Version(8, 0, 36));

        // Replace 'YourDbContext' with the name of your own DbContext derived class.
        services.AddDbContext<YourDbContext>(
            dbContextOptions => dbContextOptions
                .UseMySql(connectionString, serverVersion)
                // The following three options help with debugging, but should
                // be changed or removed for production.
                .LogTo(Console.WriteLine, LogLevel.Information)
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors()
        );
    }
}

View our Configuration Options Wiki Page for a list of common options.

3. Sample Application

Check out our Integration Tests for an example repository that includes an ASP.NET Core MVC Application.

There are also many complete and concise console application samples posted in the issue section (some of them can be found by searching for Program.cs).

4. Read the EF Core Documentation

Refer to Microsoft's EF Core Documentation for detailed instructions and examples on using EF Core.

Scaffolding / Reverse Engineering

Use the EF Core tools to execute scaffolding commands:

dotnet ef dbcontext scaffold "Server=localhost;User=root;Password=1234;Database=ef" "Pomelo.EntityFrameworkCore.MySql"

Contribute

One of the easiest ways to contribute is to report issues, participate in discussions and update the wiki docs. You can also contribute by submitting pull requests with code changes and supporting tests.

We are always looking for additional core contributors. If you got a couple of hours a week and know your way around EF Core and MySQL, give us a nudge.

License

MIT

pomelo.entityframeworkcore.mysql's Issues

Contrain Migration Names too long

If the model and field names of a relationship are long, the generated contraint is too long for MYSQL:

Pomelo.Data.MySql.MySqlException: Identifier name 'AK_OrganizationPatientInsurances_OrganizationPatientId_InsurancePriority' is too long

Investigate travis ci errors

Pomelo.EntityFrameworkCore.MySql 1.0.1 milestone

Using Microsoft.EntityFrameworkCore.Relational 1.0.1

Microsoft has released Microsoft.EntityFrameworkCore.Relational 1.0.1. We have switched the version to 1.0.1 now. This has been included in 1.0.1-prerelease-10002

Scraped Pomelo.Data.MySql

In the past Pomelo.Data.MySql was forked from the official https://github.com/mysql/mysql-connector-net, it doesn't support async operations, and the forked version is not a real MIT licensed library. Pomelo.MySql.Data, is mostly a rip of the Oracle version and is likely in violation of their license.

So, we have switched to bgrainger/MySqlConnector, the MySqlConnector is friendly to async. Besides, the MySqlConnector is under the real MIT license.

Thanks for contributions of @caleblloyd @bgrainger

These features were included in 1.0.1-prerelease-10001.

Json supporting

We are still working on the Json queries. The Linq expressions which point to JsonObject will be analyzed into native sql expressions.

DB.Blogs.Where(x => x.Tags.Object.Contains("MySQL")).ToList();

to

SELECT * FROM `Blogs` WHERE JSON_CONTAINS(`Tags`, '"MySQL"');

Enum supporting

In the current version, enum fields has been stored as a int column. We have received the suggestion #36. So, in this milestone, we plan to support the native mysql enum field type.

How to use Pomelo.EntityFrameworkCore.MySql

I download the zip file, but I don't know how to use it to connect to mysql using ef core, Anyone could give me a full sample that I could directly run?

Implement unit tests for json storage

We have implemented json field type support for MySQL 5.7. Now we need test the functions.

SQL Profiler

Can anyone recommend a way that i can see the actual complete sql statement that is sent to mysql with this library? The debug logger is outputting the query wit the parameters, but it shows the actual parameter values as ? like below. I'm trying to debug some issues with date parameters and need to see the exact parameter value that mysql is receiving. Thanks!

Executed DbCommand (3,887ms) [Parameters=[@__utcDate_0='?', @__AddDays_1='?', @__viewModel_LastName_2='?' (Size = 8000), @__viewModel_BirthDate_Date_3='?', @__AddDays_4='?'], CommandType='Text', CommandTimeout='30']

MyGet ci cannot build our library

Since we have implemented perf tests, myget does not pass the pert test cases.(They don't support mysql service now).

Can we check the environment var "MyGet" in build.ps1, if the value is True then remove the folder ./test/Pomelo.EntityFrameworkCore.MySql.PerfTests?

@caleblloyd

LINQPad - Has anyone tried using it with this?

Not sure if this is the right place, but I would give it a shot to see if this is of interest and if the issue might be here. I have posted this at LinqPad also. If this is not of interest, I understand. Thanks for the time you spent on this driver it is working great for me.

In LinqPad trying to add connection and telling it Path to Custom Assembly

It returns:
"Could not load Assembly. System.Runtime, Version=4.1.0.0, PublicKeyToken=b03f5f7f11d50a3a when adding connection

I am currently running
.NETCore.App: 1.0.0-rc2-3002702
.EntityFrameworkCore.Tools: 1.0.0-preview1-final
Pomelo.EntityFrameworkCore.MySql: 0.0-prerelease-20160730
LINQPad: 5.08.01
LinqPad EntityFramework V7 Driver: 1.0.1.0
Client Operating System: Windows 10 Version: 1607 (OS Build 14393.5)
Database Server Operating System: Raspberry PI - Raspbian
MySql: Ver 14.14 Distrib 5.5.44, for debian-linux-gnu (armv71)

1.0.1 can't connect database(MariaDB) with no password

My connection string is server=127.0.0.1;database=mydb;uid=root;pwd=;
1.0.0 can work, but 1.0.1throw exception MySqlException: Access denied for user 'root'@'localhost' (using password: YES)

Port to EF Core 1.0.1

Since microsoft has released 1.0.1 version, we have to port this to 1.0.1 EF abstractions

https://www.nuget.org/packages/Microsoft.EntityFrameworkCore.Relational/1.0.1

1.0.1 throw an exception which didn't happen in 1.0.0

It throw an exception when I upgrade the release to the 1.0.1-prerelease-10002:

Unable to cast object of type 'System.Decimal' to type 'System.Int32'.

Resolve the failed tests

We should resolve these problem in which listed appveyor such as creating a unique index.

Implement unit tests

Weird Behavior with FirstOrDefault

I have a strange issue with using FirstOrDefault. I have the following methods in my generic repository:

   public TEntity FirstOrDefault(Expression<Func<TEntity, bool>> predicate)
        {
            return _entities.FirstOrDefault(predicate); // Option 1
            return _entities.Where(predicate).FirstOrDefault(); // Options 2
            return _entities.Where(predicate).ToList().FirstOrDefault(); // Option 3
        }

     public TEntity SingleOrDefault(Expression<Func<TEntity, bool>> predicate)
        {
            return _entities.SingleOrDefault(predicate);
        }

If i call the FirstOrDefault method with option 1 or 2 multiple times for different entities, it works the first time, but the second time it just hangs, almost like the first time through never let go of the transaction or something. I don't see any errors, but the system is just stuck and never finishes.

When i use FirstOrDefault with option 3 it works every time just fine. Also, SingleOrDefault does not seem to have this issue.

Here is the code that is calling it:

var gender = _unitOfWork.Gender.FirstOrDefault(g => g.Short == viewModel.Gender); 

var organizationPatientDemographic = _unitOfWork.OrganizationPatientDemographics
                .FirstOrDefault(opd => opd.OrganizationPatientId == session.Visit.OrganizationPatientId);

The gender call works, but the demographic call will just hang if using option 1 or 2, but is fine with option 3. And if i swap the order of the calls, demographics works, but gender fails.

Further technical details

MySQL version: 5.7
Operating system: OSX El Capitan
Pomelo.EntityFrameworkCore.MySql version: 1.0.0

I've attached my models, and repositories/unitofwork.

Code.zip

Already an open DataReader associated with this Connection

When I run the following query:

var blog = db.Blogs.Include(c => c.Posts).Single(c => c.BlogId == blogId);

I get the following error:

Connection id "0HKTC3BKS8GPL": An unhandled exception was thrown by the application.
Pomelo.Data.MySql.MySqlException: There is already an open DataReader associated with this Connection which must be closed first.
at Pomelo.Data.MySql.MySqlConnection.Throw(Exception ex)
at Pomelo.Data.MySql.MySqlCommand.Throw(Exception ex)
at Pomelo.Data.MySql.MySqlCommand.CheckState()
at Pomelo.Data.MySql.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at Pomelo.Data.MySql.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean openConnection, Boolean closeConnection)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues, Boolean manageConnection)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.MoveNext()
at Microsoft.EntityFrameworkCore.Query.Internal.IncludeCollectionIterator.<GetRelatedValues>d__4.MoveNext()
at System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()
at System.Linq.Enumerable.WhereEnumerableIterator`1.MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryBuffer.Include(QueryContext queryContext, Object entity, IReadOnlyList`1 navigationPath, IReadOnlyList`1 relatedEntitiesLoaders, Int32 currentNavigationIndex, Boolean queryStateManager)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryBuffer.Include(QueryContext queryContext, Object entity, IReadOnlyList`1 navigationPath, IReadOnlyList`1 relatedEntitiesLoaders, Boolean queryStateManager)
at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_Include>d__30`1.MoveNext()

Removing the .Include(c => c.Posts) portion of the query allows it to work, but doesn't include the posts.

Reading further into this, it seems that the Entity Framework may be expecting to use MultipleActiveResultSets, or MARS. Is there some configuration I can provide to the MySQL connector to use MARS?

How to correctly configure Default Value for CURRENT_TIMESTAMP

Steps To Reproduce

From the docs sample: https://ef.readthedocs.io/en/latest/modeling/relational/default-values.html

class MyContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Blog>()
                .Property(b => b.Created)
                .HasDefaultValueSql("CURRENT_TIMESTAMP");
        }
    }

    public class Blog
    {
        public int BlogId { get; set; }
        public string Url { get; set; }
        public DateTime Created { get; set; }
    }

The issue

The DB default in MySql is correct, but when saving this entity, without setting the Created property, it defaults to the DateTime Min of '0001-01-01 00:00:00'. If you do an insert directly in MySql it obviously works fine.
I would assume the correct behavior would be to read back the value or use in the sql statment 'UTC_TIMESTAMP()'.

I can workaround by setting date in code, but would be good to understand if this is just bug or not.

Further technical details

DotNet Core 1.0 RTM
MySQL version: 5.7
Pomelo.EntityFrameworkCore.MySql version: 1.0.0-prerelease-20160803

Improve Synchronous Performance

With the merging of #58, Async Performance is solid. Threads are not held and Performance tests can run 500+ RPS while keeping response time under 1s.

There is problems with threads getting held during Synchronous performance tests, however. Synchronous tests have trouble achieving 100 RPS without lockups.

While it is best practice to use Async methods, we can't guarantee that everyone is doing that. Need to investigate synchronous performance issues and improve so that there are no lockups.

OverFlowException

Steps to reproduce

For example, there are 3 entities:

class Order
{
    public List<OrderItem> Items;
}

class OrderItem
{
    public Book Book;
}

class Book
{
    // An entity
}

unhandled exception occurred at:

DbContext.Orders.Include(o=>o.OrderItems).ThenInclude(oi=>oi.Book).AsNoTracking().SingleOrDefault(predicate);

The issue

Exception message:"数组维度超过了支持的范围。"
Stack trace:
在 Pomelo.Data.MySql.Driver.GetColumns(Int64 count)
   在 Pomelo.Data.MySql.ResultSet.LoadColumns(Int64 numCols)
   在 Pomelo.Data.MySql.ResultSet..ctor(Driver d, Int32 statementId, Int64 numCols)
   在 Pomelo.Data.MySql.Driver.NextResult(Int32 statementId, Boolean force)
   在 Pomelo.Data.MySql.MySqlDataReader.NextResult()
   在 Pomelo.Data.MySql.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   在 Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean openConnection, Boolean closeConnection)
   在 Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues, Boolean manageConnection)
   在 Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.MoveNext()
   在 Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_ShapedQuery>d__3`1.MoveNext()
   在 Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_Include>d__30`1.MoveNext()
   在 Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   在 System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable`1 source, Func`2 predicate)
   在 BookList.Domain.Concrete.Repositories.OrderRepository.GetWithDetail(Func`2 predicate) 位置 C:\Users\vaezt\Source\Repos\BookList\BookList\src\BookList.Domain\Concrete\Repositories\OrderRepository.cs:行号 23
   在 BookList.ApiControllers.OrdersController.GetBookList(Int32 semesterId) 位置 C:\Users\vaezt\Source\Repos\BookList\BookList\src\BookList\ApiControllers\OrdersController.cs:行号 206
   在 lambda_method(Closure , Object , Object[] )
   在 Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionFilterAsync>d__28.MoveNext()

Further technical details

MySQL version: 5.7
Operating system: Windows 10 14393.105
Pomelo.EntityFrameworkCore.MySql version: 1.0.0

Other details about my project setup:
project framework: 4.5.2

Using JsonObject<T> in MySQL 5.6 thrown an error.

Steps to reproduce

照着sample，改了一下链接字符串报错

Exception message:

An unhandled exception of type 'Pomelo.Data.MySql.MySqlException' occurred in Microsoft.EntityFrameworkCore.Relational.dll

Additional information: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'json,

`Title` varchar(32),

`UserId` int NOT NULL,

CONSTRAINT `PK_' at line 4

MySQL version: 5.6.28-log

NuGet package version has been re-ruled.

Before

"Pomelo.EntityFrameworkCore.MySql": "1.0.0"

After

"Pomelo.EntityFrameworkCore.MySql": "1.0.0-prerelease-*"

And the packages are available on the official NuGet feed now. In other words, you don't need the NuGet.config any more.

JSON Object Migrations Missing Using

Steps to reproduce

Add a field to a model for JSONObject:

public JsonObject<List<string>> AlternateNames {get;set;}

Generate a migration using:

dotnet ef migrations add NewFieldTest

The issue

The migrations are created, but the Designer class and the Snapshot class do not include the required using statement:

using System.Collections.Generic;

This is required because the code for the field in the migrations contains LIst, which is in the System.Collections.Generic namespace:

b.Property<JsonObject<List<string>>>("AlternateNames");

Workaround:

Manually add using statement to the code after each newly generated migration.

Further technical details

MySQL version: 5.7
Operating system: mac osx el capitan
Pomelo.EntityFrameworkCore.MySql version: 1.0.0

Adding DateTimeOffset support

Count()=>System.ArgumentOutOfRangeException

Steps to reproduce

public ActionResult Users(PagedModel<User> model)
{
    var query = this._userRepo.Query();
    if (!string.IsNullOrWhiteSpace(model.Keyword))
    {
        var keyword = model.Keyword.Trim();
        query = query.Where(o => o.UserName.Contains(keyword));
    }
    model.Total = query.Count();
    model.List = query.OrderByDescending(o => o.CreateAt)
        .Skip(model.PageSize * (model.PageIndex - 1)).Take(model.PageSize)
        .ToList();
    return View(model);
}

The issue

Describe what is not working as expected.

If you are seeing an exception, include the full exceptions details (message and stack trace).

Exception message:
{System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: startIndex
   at System.BitConverter.ToUInt16(Byte[] value, Int32 startIndex)
   at Pomelo.Data.MySql.MySqlPacket.ReadLong(Int32 numbytes)
   at Pomelo.Data.MySql.NativeDriver.GetColumnData(MySqlField field)
   at Pomelo.Data.MySql.NativeDriver.GetColumnsData(MySqlField[] columns)
   at Pomelo.Data.MySql.Driver.GetColumns(Int64 count)
   at Pomelo.Data.MySql.ResultSet.LoadColumns(Int64 numCols)
   at Pomelo.Data.MySql.ResultSet..ctor(Driver d, Int32 statementId, Int64 numCols)
   at Pomelo.Data.MySql.Driver.NextResult(Int32 statementId, Boolean force)
   at Pomelo.Data.MySql.MySqlDataReader.NextResult()
   at Pomelo.Data.MySql.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean openConnection, Boolean closeConnection)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues, Boolean manageConnection)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.GetResult[TResult](IEnumerable`1 valueBuffers)
   at lambda_method(Closure , QueryContext )
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass19_1`1.<CompileQuery>b__1(QueryContext qc)
   at Web.Controllers.AdminController.Users(PagedModel`1 model) in C:\Users\wanggang\Documents\Visual Studio 2015\Projects2\ARBookServer\ARBookServer\Controllers\AdminController.cs:line 100
   at lambda_method(Closure , Object , Object[] )
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionFilterAsync>d__28.MoveNext()}
Stack trace:

Further technical details

MySQL version: MySQL 55
Operating system: windows 10 x64

Other details about my project setup:
this error do not appear when I swith database to sqlite.

MySQL 5.7 Json Field Type Is Available Now

Getting Started

① Adding pomelo myget feed into your NuGet.config which located in your solution root.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <packageSources>
    <add key="nuget.org" value="https://www.nuget.org/api/v2/" />
    <add key="Pomelo" value="https://www.myget.org/F/pomelo/api/v2/" />
  </packageSources>
  <disabledPackageSources />
</configuration>

② Add Pomelo.Data.MySql and Pomelo.EntityFrameworkCore.MySql into your project.json. The versions of them are 1.0.0.

③ If you have already installed the pomelo packages: Pomelo.Data.MySql and Pomelo.EntityFrameworkCore.MySql, please remove them and restore again(dotnet restore --no-cache). The packages are located in C:\Users\YOURNAME\.nuget\packages.

④ To define json field in model with System.JsonObject<T> will store this field as a json column.

Sample

using System;
using System.Linq;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;

namespace MySqlTest
{
    public class Blog
    {
        public Guid Id { get; set; }

        [MaxLength(32)]
        public string Title { get; set; }

        public string Content { get; set; }

        public JsonObject<string[]> Tags { get; set; } // Json storage
    }

    public class MyContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder
                .UseMySql(@"Server=localhost;database=ef;uid=root;pwd=19931101;");
    }

    public class Program
    {
        public static void Main()
        {
            using (var context = new MyContext())
            {
                // Create database
                context.Database.EnsureCreated();

                // Init sample data
                var blog1 = new Blog {
                    Title = "Title #1",
                    Tags = new string[] { "ASP.NET Core", "MySQL", "Pomelo" }
                };
                context.Add(blog1);
                var blog2 = new Blog
                {
                    Title = "Title #2",
                    Tags = new string[] { "ASP.NET Core", "MySQL" }
                };
                context.Add(blog2);
                context.SaveChanges();

                // Detect changes test
                blog1.Title = "Changed Title #1";
                context.SaveChanges();

                // Output data
                var ret = context.Blogs
                    .Where(x => x.Tags.Object.Contains("Pomelo"))
                    .ToList();
                foreach (var x in ret)
                {
                    Console.WriteLine($"{ x.Id } { x.Title }");
                    Console.Write("[Tags]: ");
                    foreach(var y in x.Tags.Object)
                        Console.Write(y + " ");
                    Console.WriteLine();
                }
            }

            Console.Read();
        }
    }
}

Migrations

Ive tried Migrations. Add-Migration worked as expected. The code gets generated well.
What i noticed is, that the __EFMigrationsHistory table does not get created. This makes it impossible to apply changes using Update-Database.

Any ideas?

Great work so far!

RelationalConnection has not been disposed after action executed in asp.net core

Repro

Inject db context in an action and make some query:

        public IActionResult Index([FromServices] MyContext DB)
        {
            var cnt = DB.Blogs.Count();
            return Content(cnt.ToString());
        }

To execute mysqladmin -uroot -pYOURPWD status

The action has been executed but didn't disconnect the connection.

Added JSON, like the original JSON content, but the indentation or layout is not the same, resulting in Assert is not equal

Added JSON, like the original JSON content, but the indentation or layout is not the same, resulting in Assert is not equal

Close this repository or keep maintaining?

Since oracle has released the mysql for ef core provider(https://www.nuget.org/packages/MySql.Data.EntityFrameworkCore/7.0.4-IR-19). Should we keep maintain this repository?

Viewpoint 1
Stop this, we can concentrate on other libs such as MyCat(Will inherit all features from Pomelo.EntityFrameworkCore.MySql).

Viewpoint 2
Keep this, we will support more features like json field querying.

I would like to know who is using our library, and which library you will choose in future(official version or pomelo version)

Implement functional tests

1.0.1 Include related data more than one will get ObjectDisposedException

Only include one related data not trigger exception, but if include more than one, will get follow exception

ObjectDisposedException: Cannot access a disposed object.
Object name: 'SynchronizedMySqlDataReader'.

the sample code

            var admin =  _myDbContext.CmsAdmins
                .Include(a => a.AdminRoles)
                .Include(a => a.MenuAdmins)
                .SingleOrDefault(a => a.Id == 1);

also async version

            var admin = await _myDbContext.CmsAdmins
                .Include(a => a.AdminRoles)
                .Include(a => a.MenuAdmins)
                .SingleOrDefaultAsync(a => a.Id == 1);

There is an error in migration

Repro
Executing Add-Migration init Update-Database init to init.

Is database scaffolding supported yet?

I didn't find any information if and how much of the scaffolding (DB to models) is supported or working as of now.

For a customer I need to scaffold from an existing database, and the official MySQL driver do not seem to support it (neither does it support database updates).

Fix failed unit tests

AddPrimaryKeyOperation
DropPrimaryKeyOperation

AlterColumnOperation,CreateIndexOperation Generator Sql is null

When Invoke AlterColumnOperation,CreateIndexOperation,DropColumnOperation Generator Sql,theSql is null .

Array is not long enough

I received a Exception which I can not describe.
I don't know what the reason is, I just know with EF 6 and the official MySQL provider the access works on the same db with the same EF configurations.

Maybe you find something by the stacktrace.

System.ArgumentException: Array is not long enough. Check array index and length.
Parameter name: value
at System.BitConverter.ToInt64(Byte[] value, Int32 startIndex)
at Pomelo.Data.MySql.NativeDriver.GetResult(Int64& affectedRow, Int64& insertedId)
at Pomelo.Data.MySql.Driver.NextResult(Int32 statementId, Boolean force)
at Pomelo.Data.MySql.MySqlDataReader.NextResult()
at Pomelo.Data.MySql.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary2 parameterValues, Boolean openConnection, Boolean closeConnection) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues, Boolean manageConnection)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.MoveNext()
at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_ShapedQuery>d__31.MoveNext() at System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable1 source)
at lambda_method(Closure , QueryContext )
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass19_1`1.b__1(QueryContext qc)

Further technical details

MySQL version: 5.6.22
Operating system: Windows Server 2012 R2
Pomelo.EntityFrameworkCore.MySql version: 1.0.0

Kind regards

Downgrade to netstandard1.3

When adding a new value type or numeric field a default value is added to the migration

Steps to reproduce

Add a new field to a mapped class without specifying a default value using the Fluent API and then generates a new migration. The corresponding column will be generated on the database correctly but with a default value which was not explicitly specified.

The issue

In EF Core by convention, a default value is not configured. However, in Pomelo, when you add a new field to a mapped class and generates a migration a default value is also generated, e.g:

            migrationBuilder.AddColumn<int>(
                name: "Attempts",
                table: "Players",
                nullable: false,
                defaultValue: 0);

The correct behavior should be not generating a default value unless explicitly specified it in the Fluent API.

Further technical details

MySQL version: 5.7.14
Operating system: Win10
Pomelo.EntityFrameworkCore.MySql version: 1.0.1-prerelease-10000

Other details

In the initial migration when creating the table the columns are mapped correctly without default values.

Implement Pomelo.EntityFrameworkCore.Design

MyCat (MySQL cluster proxy) has been supported

Hi,

We have published a MyCat provider today. Pomelo.EntityFrameworkCore.MyCat is an Entity Framework Core provider and optimized for MyCat (An open source MySQL cluster proxy which based on Cobar).

Now, you can install the package via NuGet, or view the source on GitHub.

• NuGet Package on NuGet.org
• Source on GitHub

Enjoy.

JsonObject in 1.0.0?

should this work in 1.0.0 ?
public JsonObject<List> Tags { get; set; } // Json storage (MySQL 5.7 only)

If so, what library/namespace does JsonObject come from?

An error occurred applying migrations, try applying them from the command line

Steps to reproduce

1.修改配置文件
"MysqlDefaultConnection": "Data Source=*_.com;port=8041;Database=Videos;uid=root;pwd=_;charset=utf8",

2.services.AddDbContext(options =>
options.UseMySql(Configuration.GetConnectionString("MysqlDefaultConnection")));

3.注册用户

4.提示ef迁移

5.点击 Apply Migrations

6.报错 提示 “An error occurred applying migrations, try applying them from the command line”
查看数据库已经自动创建成功，但是表只有__EFMigrationsHistory一个。

MySQL version: MySQL 5.6

Renaming a table while adding a new field/column fails when updating the migration in the database.

Steps to reproduce

Add a new migration renaming a table at the same time of adding a new field/column. Then try to update the database with the migration.

The issue

Migration database update fails with the following exception:

Exception message:

System.ArgumentNullException: Value cannot be null.
Parameter name: property

Stack trace:

   at Microsoft.EntityFrameworkCore.Utilities.Check.NotNull[T](T value, String parameterName)
   at Microsoft.EntityFrameworkCore.Storage.RelationalTypeMapper.FindMapping(IProperty property)
   at Microsoft.EntityFrameworkCore.Migrations.MySqlMigrationsSqlGenerationHelper.ColumnDefinition(String schema, String table, String name, Type clrType, String type, Nullable`1 unicode, Nullable`1 maxLength, Boolean rowVersion, Boolean nullable, Object defaultValue, String defaultValueSql, String computedColumnSql, IAnnotatable annotatable, IModel model, MigrationCommandListBuilder builder)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.ColumnDefinition(AddColumnOperation operation, IModel model, MigrationCommandListBuilder builder)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.Generate(AddColumnOperation operation, IModel model, MigrationCommandListBuilder builder)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.Generate(MigrationOperation operation, IModel model, MigrationCommandListBuilder builder)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.Generate(IReadOnlyList`1 operations, IModel model)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.GenerateUpSql(Migration migration)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.<MigrateAsync>d__12.MoveNext()
--- End of stack trace from previous location where exception was thrown ---

Further technical details

MySQL version: 5.7.13
Operating system: Win10
Pomelo.EntityFrameworkCore.MySql version: 1.0.0

Practical Way to use MySql Enums

Steps to reproduce

Almost everything works with MySql enums when you change the column type with the following method that you call for every property in your context:

        /// <summary>
        ///     Leverages MySql's enum type
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="property"></param>
        public static void EnumConventions(IMutableEntityType entity, IMutableProperty property)
        {
            var typeInfo = property.ClrType.GetTypeInfo();
            if (typeInfo.IsEnum && typeInfo.GetCustomAttribute<FlagsAttribute>() == null)
            {
                property.Relational().ColumnType = property.ClrType.EnumTypeToMySqlEnum();
            }
        }

        public static string EnumTypeToMySqlEnum(this Type value)
        {
            return $"ENUM('{string.Join("','", Enum.GetNames(value))}')";
        }

The issue

Persistence and migrations work fine here. The limitation is on querying as by default MySql will return a string for this column and EF blows up trying to convert that to an int. All that needs to be done for the simple scenario is to either pre-cast these columns to int when querying, and/or handling the string => enum conversion. I'm not sure which approach would be easier, but again, I'm not looking for full complete enum support, just enough hooks to make it usable.

I'm happy to help contribute, but would ask for recommendation on how to make one of these options work.

Further technical details

MySQL version: 5.7
Operating system: Ubuntu
Pomelo.EntityFrameworkCore.MySql version: 1.0

Add support json field type for MySQL 5.7

Specified key was too long; max key length is 767 bytes

If you did not specific maxlength of id, it will throw this exception.

utf8 will takes 3 bytes, so we should narrow down the key length to be 255.

Table '__efmigrationshistory' doesn't exist

I'm trying to use ASP.NET Core Identity with MySQL, but when updating the database using the tools I'm getting an error saying the table __efmigrationshistory doesn't exist.

If I don't use the migrations, but only the ctx.Database.EnsureCreated(); it creates the database without any problem.

Steps to reproduce

Sample code:

namespace TestEFCoreMySql
{
    using System;
    using System.Threading;
    using System.Threading.Tasks;
    using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
    using Microsoft.EntityFrameworkCore;
    using Microsoft.EntityFrameworkCore.Infrastructure;
    using Microsoft.Extensions.Configuration;

    public static class Program
    {
        public static IConfigurationRoot ConfigurationRoot { get; }

        static Program()
        {
            var cfgBuilder = new ConfigurationBuilder()
                .AddJsonFile("appsettings.json", false, true);

            ConfigurationRoot = cfgBuilder.Build();
        }

        public static void Main(string[] args)
        {
            Console.WriteLine("Application started...");
            try
            {
                MainAsync(args, CancellationToken.None).Wait();
            }
            catch (Exception e)
            {
                Console.WriteLine("An unhandled exception has occured");
                Console.WriteLine(e);
            }
            finally
            {
                Console.WriteLine("Application terminated. Press <enter> to exit...");
                Console.ReadLine();
            }
        }

        public static async Task MainAsync(string[] args, CancellationToken ct)
        {
            var testSecurityConnection =
                ConfigurationRoot.GetConnectionString("TestSecurityConnection");

            var optionsBuilder = new DbContextOptionsBuilder<TestSecurityContext>()
                .UseMySql(testSecurityConnection);
            using (var ctx = new TestSecurityContext(optionsBuilder.Options))
            {
                //  this works, but no ´__efmigrationshistory´ table is created
                ctx.Database.EnsureCreated();
            }
        }
    }

    public class TestSecurityUser : IdentityUser
    {

    }

    public class TestSecurityContext : IdentityDbContext<TestSecurityUser>
    {
        public TestSecurityContext(DbContextOptions options) : base(options)
        {
        }
    }

    public class TestSecurityContextFactory : IDbContextFactory<TestSecurityContext>
    {
        public TestSecurityContext Create(DbContextFactoryOptions options)
        {
            var testSecurityConnection =
                Program.ConfigurationRoot.GetConnectionString("TestSecurityConnection");

            var optionsBuilder = new DbContextOptionsBuilder<TestSecurityContext>()
                .UseMySql(testSecurityConnection);
            return new TestSecurityContext(optionsBuilder.Options);
        }
    }
}

{
  "version": "1.0.0-*",
  "buildOptions": {
    "emitEntryPoint": true,
    "copyToOutput": {
      "include": [
        "appsettings*.json"
      ]
    }
  },

  "tools": {
    "Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview2-final"
  },

  "dependencies": {
    "Microsoft.AspNetCore.Identity.EntityFrameworkCore": "1.0.0",
    "Microsoft.EntityFrameworkCore.Design": {
      "type": "build",
      "version": "1.0.0-preview2-final"
    },
    "Microsoft.Extensions.Configuration.Json": "1.0.0",
    "Pomelo.EntityFrameworkCore.MySql": "1.0.0"
  },

  "frameworks": {
    "net4.6.2": {}
  }
}

The issue

When running the tools to migrate I get the missing table error

dotnet ef migrations add Initial
dotnet ef database update

Pomelo.Data.MySql.MySqlException (0x80004005): Table 'test_security.__efmigrationshistory' doesn't exist
   at Pomelo.Data.MySql.MySqlStream.ReadPacket()
   at Pomelo.Data.MySql.NativeDriver.GetResult(Int64& affectedRow, Int64& insertedId)
   at Pomelo.Data.MySql.Driver.NextResult(Int32 statementId, Boolean force)
   at Pomelo.Data.MySql.MySqlDataReader.NextResult()
   at Pomelo.Data.MySql.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean openConnection, Boolean closeConnection)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues, Boolean manageConnection)
   at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.GetAppliedMigrations()
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
   at Microsoft.EntityFrameworkCore.Tools.Cli.DatabaseUpdateCommand.<>c__DisplayClass0_0.<Configure>b__0()
   at Microsoft.Extensions.CommandLineUtils.CommandLineApplication.Execute(String[] args)
   at Microsoft.EntityFrameworkCore.Tools.Cli.Program.Main(String[] args)
Table 'test_security.__efmigrationshistory' doesn't exist

Further technical details

MySQL version: 5.7.15
Operating system: Windows 10
Pomelo.EntityFrameworkCore.MySql version: 1.0.0

Make a benchmarks

Compare to Pomelo.EntityFrameworkCore.MySql-1.0.0(rtm), Pomelo.EntityFrameworkCore.MySql-1.0.1(prerelease), MySql.Data.EntityFrameworkCore(7.0.5)

Can I use EF migrations with identity?

Hi,
when I use:
dotnet ef database update --verbose
I got this error:
Pomelo.Data.MySql.MySqlException: Table 'testdb.__efmigrationshistory' doesn't exist

is ef migrations supported?

MySqlCreateDatabaseOperation Generator Sql miss ;

MySqlCreateDatabaseOperation Generator Sql miss ;

Pomelo.Data.MySql Significant Upgrade

Hi,

Today, we found a big bug which about MySqlConnection. In the past versions(Pomelo.Data.MySql), The connection cannot be disposed fully. If the amount of requests is large, it will exceed your MySQL max connection limit.

For more details: #25

So, we have fixed the bug in ADO layer. We suggest every body to do the following instructions to upgrade the package Pomelo.Data.MySql:

① To remove the old packages Pomelo.Data.MySql and Pomelo.EntityFrameworkCore.MySql. The packages are located in C:\Users\YOURNAME.nuget\packages.

② Restore your project with dotnet restore --no-cache which contains above packages.