'Sequence contains no matching element' scaffolding database tables #1584
Comments
|
Probably related to PR #1364 that hasn't been backported to 3.2-maint branch yet. |
|
I'm not so sure of that unless there's other relevant infrastructure being addressed in that PR. Given the --verbose logs, it's not even getting to processing the DBContext to get tables, etc. - it simply cannot find a design-time service. So a question I might pose is what interface is being queried in the project's assembly? Early on in my project, I did manage to get this to work with an older 3.2.x pomelo. Back then, I'm pretty sure I had everything in a single assembly- definitely without the need to add the IDesignTimeDbContextFactory implementation above. If the DBContext is (now) in a classlib could that cause any additional grief? Even if I run the scaffolding on the main DLL or the underlying, dependant, classlib assembly? |
The stack trace shows it's hitting |
|
Ah, I see what you are talking about. Could you not also interpret that the exception is because it didn't have a design-time service in place when it ran GetContraints? Is the design-time service needed, or is it just a info message in the log? Could this problem just be some unforseen oddity in the DBContext definition? Might be worth it to strip down the DBContext, to say: one table, and start building it up until it fails? If it fails with one simple table with no constraints, that could be useful as it would lead to an environment issue. Thanks for your help. |
|
I'm pretty certain the exception is caused by that bug. Check for tables with foreign keys that reference a table in another schema. Otherwise try building the 3.2-maint branch with the fix from the aforementioned PR or upgrading to version 5.0.3 and retry. |
|
In this case I only have one schema. I'll will try and incrementally strip down the DBContext and see if I can narrow it down to an offending entity. I'll report what I find. |
|
Use the General Query Log to see the last SQL statement executed by the scaffold command before the exception and confim that REFERENCED_TABLE_NAME exists. |
|
@RotateAt60MPH The However, to easily debug the issue (or even implement a workaround later), you can actually just add a custom design time service. Add the Program.csusing System;
using System.Collections.Generic;
using System.Data.Common;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.EntityFrameworkCore.Scaffolding;
using Microsoft.EntityFrameworkCore.Scaffolding.Metadata;
using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Pomelo.EntityFrameworkCore.MySql.Design.Internal;
using Pomelo.EntityFrameworkCore.MySql.Infrastructure.Internal;
using Pomelo.EntityFrameworkCore.MySql.Scaffolding.Internal;
namespace IssueConsoleTemplate
{
public class CustomMySqlDesignTimeServices : IDesignTimeServices
{
private readonly MySqlDesignTimeServices _originalMySqlDesignTimeServices;
public CustomMySqlDesignTimeServices()
{
_originalMySqlDesignTimeServices = new MySqlDesignTimeServices();
}
public virtual void ConfigureDesignTimeServices(IServiceCollection serviceCollection)
{
_originalMySqlDesignTimeServices.ConfigureDesignTimeServices(serviceCollection);
serviceCollection.AddScoped<IDatabaseModelFactory, CustomMySqlDatabaseModelFactory>();
}
}
public class CustomMySqlDatabaseModelFactory : MySqlDatabaseModelFactory
{
private readonly IDiagnosticsLogger<DbLoggerCategory.Scaffolding> _logger;
public CustomMySqlDatabaseModelFactory(
IDiagnosticsLogger<DbLoggerCategory.Scaffolding> logger,
IRelationalTypeMappingSource typeMappingSource,
IMySqlOptions options)
: base(
logger,
typeMappingSource,
options)
{
_logger = logger;
}
private const string GetConstraintsQuery = @"SELECT
`CONSTRAINT_NAME`,
`TABLE_NAME`,
`REFERENCED_TABLE_NAME`,
GROUP_CONCAT(CONCAT_WS('|', `COLUMN_NAME`, `REFERENCED_COLUMN_NAME`) ORDER BY `ORDINAL_POSITION` SEPARATOR ',') AS PAIRED_COLUMNS,
(SELECT `DELETE_RULE` FROM `INFORMATION_SCHEMA`.`REFERENTIAL_CONSTRAINTS` WHERE `REFERENTIAL_CONSTRAINTS`.`CONSTRAINT_NAME` = `KEY_COLUMN_USAGE`.`CONSTRAINT_NAME` AND `REFERENTIAL_CONSTRAINTS`.`CONSTRAINT_SCHEMA` = `KEY_COLUMN_USAGE`.`CONSTRAINT_SCHEMA`) AS `DELETE_RULE`
FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`
WHERE `TABLE_SCHEMA` = '{0}'
AND `TABLE_NAME` = '{1}'
AND `CONSTRAINT_NAME` <> 'PRIMARY'
AND `REFERENCED_TABLE_NAME` IS NOT NULL
GROUP BY `CONSTRAINT_SCHEMA`,
`CONSTRAINT_NAME`,
`TABLE_NAME`,
`REFERENCED_TABLE_NAME`;";
protected virtual void GetConstraints(
DbConnection connection,
IReadOnlyList<DatabaseTable> tables)
{
Debugger.Launch(); // <-- launch the debugger
foreach (var table in tables)
{
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format(GetConstraintsQuery, connection.Database, table.Name);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var referencedTableName = reader.GetString(2);
var referencedTable = tables.FirstOrDefault(t => t.Name == referencedTableName);
if (referencedTable == null)
{
// On operation systems with insensitive file name handling, the saved reference table name might have a
// different casing than the actual table name. (#1017)
// In the unlikely event that there are multiple tables with the same spelling, differing only in casing,
// we can't be certain which is the right match, so rather fail to be safe.
referencedTable = tables.Single(t => string.Equals(t.Name, referencedTableName, StringComparison.OrdinalIgnoreCase));
}
if (referencedTable != null)
{
var fkInfo = new DatabaseForeignKey {Name = reader.GetString(0), OnDelete = ConvertToReferentialAction(reader.GetString(4)), Table = table, PrincipalTable = referencedTable};
foreach (var pair in reader.GetString(3).Split(','))
{
fkInfo.Columns.Add(table.Columns.Single(y =>
string.Equals(y.Name, pair.Split('|')[0], StringComparison.OrdinalIgnoreCase)));
fkInfo.PrincipalColumns.Add(fkInfo.PrincipalTable.Columns.Single(y =>
string.Equals(y.Name, pair.Split('|')[1], StringComparison.OrdinalIgnoreCase)));
}
table.ForeignKeys.Add(fkInfo);
}
else
{
_logger.Logger.LogWarning($"Referenced table `{referencedTableName}` is not in dictionary.");
}
}
}
}
}
}
}
internal static class Program
{
private static void Main()
{
}
}
}The Just scaffold again and the JIT debugger should pop up. You can then just debug the method (or replace |
mguinness
changed the title
|
@RotateAt60MPH What is the status of this issue? Where you able to debug it further with the information from my previous post? |
|
@RotateAt60MPH Since we have not heard back from you, we will close this one. |
lauxjpn
added
closed-abandoned
type-question
and removed
type-investigation
needs-more-info
labels
Steps to reproduce
dotnet ef dbcontext scaffold "connect string" "Pomelo.EntityFrameworkCore.MySql" --verboseThe issue
Throws an exception after not finding design-time services.
Further technical details
MySQL version: 10.3.29 MariaDB
Operating system: Windows 10
Pomelo.EntityFrameworkCore.MySql version: 3.2.7
Other details about my project setup:
Solution made up of main 'Services' project, with a dependancy to a 'Data' classlib project that contains the DBContext.
Ran scaffolding in main .EXE project source directory (OLabWebAPI.csproj). DBContext is in dependant project called 'Data'. Tried running the scaffolding in Data.csproj directory and same result.
I tried adding the following to the project that has my DBContext, to no avail:
The text was updated successfully, but these errors were encountered: