fail: Microsoft.EntityFrameworkCore.Database.Command[20102] on linux MySql database

I am using the Entity Framework Core ORM v3.1.3 to manage a MySql database. Trying to deploy an Angular ASP.NET app on a linux Ubuntu 18 server and encountering the following error. This error only occurs in deployment on linux and NOT when published on my windows machine which uses MySql version 8.0.18.

fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for context type 'dynamify.Models.MyContext'.
      MySql.Data.MySqlClient.MySqlException (0x80004005): 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 '(PARTITION BY `a`.`admin_id` ORDER BY `a`.`admin_id`) AS `row`
              FROM `Adm' at line 6
       ---> MySql.Data.MySqlClient.MySqlException (0x80004005): 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 '(PARTITION BY `a`.`admin_id` ORDER BY `a`.`admin_id`) AS `row`
              FROM `Adm' at line 6
         at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ResultSet.cs:line 49
         at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 130
         at MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 391
         at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\CommandExecutor.cs:line 62
         at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 217
         at System.Data.Common.DbCommand.ExecuteReader()
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
         at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()

The error goes on to detail how Entity parsed the SQL:

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (4ms) [Parameters=[@__site_id_parameter_1='?' (DbType = Int32), @__site_id_parameter_2='?' (DbType = Int32), @__site_id_parameter_3='?' (DbType = Int32), @__site_id_parameter_4='?' (DbType = Int32), @__site_id_parameter_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SELECT `s`.`title`, `s`.`active`, `s`.`admin_id`, `t0`.`admin_id`, `t0`.`first_name`, `t0`.`last_name`, `t0`.`email`, `t0`.`password`, `t0`.`c`, `s`.`site_id`, `t1`.`paragraph_box_id`, `t1`.`title`, `t1`.`priority`, `t1`.`content`, `t1`.`site_id`, `t2`.`image_id`, `t2`.`title`, `t2`.`priority`, `t2`.`image_src`, `t2`.`site_id`, `t3`.`portrait_id`, `t3`.`title`, `t3`.`priority`, `t3`.`image_src`, `t3`.`content`, `t3`.`site_id`, `t5`.`two_column_box_id`, `t5`.`title`, `t5`.`priority`, `t5`.`heading_one`, `t5`.`heading_two`, `t5`.`content_one`, `t5`.`content_two`, `t5`.`site_id`, `l`.`title`, `l`.`priority`, `l`.`site_id`, `l`.`link_box_id`, `l`.`type`, `l`.`content`, `l`.`url`, `l`.`link_display`, `n`.`site_id`, `n`.`string_of_links`, `n`.`nav_bar_id`
      FROM `Sites` AS `s`
      LEFT JOIN (
          SELECT `t`.`admin_id`, `t`.`first_name`, `t`.`last_name`, `t`.`email`, `t`.`password`, `t`.`c`
          FROM (
              SELECT `a`.`admin_id`, `a`.`first_name`, `a`.`last_name`, `a`.`email`, `a`.`password`, 1 AS `c`, ROW_NUMBER() OVER(PARTITION BY `a`.`admin_id` ORDER BY `a`.`admin_id`) AS `row`
              FROM `Admins` AS `a`
          ) AS `t`
          WHERE `t`.`row` <= 1
      ) AS `t0` ON `s`.`admin_id` = `t0`.`admin_id`
      LEFT JOIN (
          SELECT `p`.`paragraph_box_id`, `p`.`title`, `p`.`priority`, `p`.`content`, `p`.`site_id`
          FROM `ParagraphBoxes` AS `p`
          WHERE `p`.`site_id` = @__site_id_parameter_1
      ) AS `t1` ON `s`.`site_id` = `t1`.`site_id`
      LEFT JOIN (
          SELECT `i`.`image_id`, `i`.`title`, `i`.`priority`, `i`.`image_src`, `i`.`site_id`
          FROM `Images` AS `i`
          WHERE `i`.`site_id` = @__site_id_parameter_2
      ) AS `t2` ON `s`.`site_id` = `t2`.`site_id`
      LEFT JOIN (
          SELECT `p0`.`portrait_id`, `p0`.`title`, `p0`.`priority`, `p0`.`image_src`, `p0`.`content`, `p0`.`site_id`
          FROM `Portraits` AS `p0`
          WHERE `p0`.`site_id` = @__site_id_parameter_3
      ) AS `t3` ON `s`.`site_id` = `t3`.`site_id`
      LEFT JOIN (
          SELECT `t4`.`two_column_box_id`, `t4`.`title`, `t4`.`priority`, `t4`.`heading_one`, `t4`.`heading_two`, `t4`.`content_one`, `t4`.`content_two`, `t4`.`site_id`
          FROM `TwoColumnBoxes` AS `t4`
          WHERE `t4`.`site_id` = @__site_id_parameter_4
      ) AS `t5` ON `s`.`site_id` = `t5`.`site_id`
      LEFT JOIN `LinkBoxes` AS `l` ON (`s`.`site_id` = `l`.`site_id`) AND (`s`.`site_id` = `l`.`site_id`)
      LEFT JOIN `NavBars` AS `n` ON `s`.`site_id` = `n`.`site_id`
      WHERE `s`.`site_id` = @__site_id_parameter_0
      ORDER BY `s`.`site_id`, `t1`.`paragraph_box_id`, `t2`.`image_id`, `t3`.`portrait_id`, `t5`.`two_column_box_id`, `l`.`link_box_id`, `n`.`nav_bar_id`

Here is the query I wrote in C# that triggers the error:

List<Site> FoundSites = dbContext.Sites.Where(x => x.site_id == site_id_parameter).Select( site => new Site()
            {
                site_id = site_id_parameter,
                title = site.title,
                active = site.active,
                admin_id = site.admin_id,
                owner = dbContext.Admins.Where(x => x.admin_id == site.admin_id).Select(s => new Admin()
                {
                    admin_id = s.admin_id,
                    first_name = s.first_name,
                    last_name = s.last_name,
                    email = s.email,
                    password = s.password
                }).FirstOrDefault(),
                paragraph_boxes = dbContext.ParagraphBoxes.Where(x => x.site_id == site.site_id).Select(box => new ParagraphBox()
                {
                    paragraph_box_id = box.paragraph_box_id,
                    title = box.title,
                    priority = box.priority,
                    content = box.content,
                    site_id = box.site_id
                }).Where(x => x.site_id == site_id_parameter).ToList(),
                images = dbContext.Images.Where(x => x.site_id == site.site_id).Select(i => new Image()
                {
                    image_id = i.image_id,
                    title = i.title,
                    priority = i.priority,
                    image_src = i.image_src,
                    site_id = i.site_id
                }).Where(x => x.site_id == site_id_parameter).ToList(),

                portraits = dbContext.Portraits.Where(x => x.site_id == site.site_id).Select(p => new Portrait()
                {
                    portrait_id = p.portrait_id,
                    title = p.title,
                    priority = p.priority,
                    image_src = p.image_src,
                    content = p.content,
                    site_id = p.site_id
                }).Where(x => x.site_id == site_id_parameter).ToList(),

                two_column_boxes = dbContext.TwoColumnBoxes.Where(x => x.site_id == site.site_id).Select(tcb => new TwoColumnBox()
                {
                    two_column_box_id = tcb.two_column_box_id,
                    title = tcb.title,
                    priority = tcb.priority,
                    heading_one = tcb.heading_one,
                    heading_two = tcb.heading_two,
                    content_one = tcb.content_one,
                    content_two = tcb.content_two,
                    site_id = tcb.site_id
                }).Where(x => x.site_id == site_id_parameter).ToList(),
                link_boxes = dbContext.LinkBoxes.Where(x => x.site_id == site.site_id).Select(lb => new LinkBox()
                {
                    title = lb.title,
                    priority = lb.priority,
                    site_id = lb.site_id,
                    link_box_id = lb.link_box_id,
                    type = lb.type,
                    content = lb.content,
                    url = lb.url,
                    link_display = lb.link_display
                }).Where(x => x.site_id == site.site_id).ToList(),

                nav_bars = dbContext.NavBars.Where(x => x.site_id == site.site_id).Select(nb => new NavBar(){
                    site_id = nb.site_id,
                    string_of_links = nb.string_of_links

                }).ToList()
            }).ToList();

I have remade the linux instance and managed to re-create the issue. A friend of mine also deployed the app code base and was able to reproduce the error as well.

MySql was installed on linux using the command:

sudo apt-get update
sudo apt-get install mysql-server

Any feedback on this issue is appreciated.

I was able to find the solution to this. At the time of this posting, the command:

sudo apt-get install mysql-server

installs MySQL version 5.7 by default. This version of MySQL will not work with the newer features in Entity Framework. If you want to use Entity Framework v3.1 +, you should install MySQL 8 on your linux server.

This Article Gave me clear directions for how to install MySql on Ubuntu 18