/// <summary>
/// Gets a value indicating whether the entity type is ignored by Migrations.
/// </summary>
/// <param name="entityType">The entity type.</param>
/// <returns>A value indicating whether the entity type is ignored by Migrations.</returns>
public static bool IsIgnoredByMigrations([NotNull] this IEntityType entityType)
{
if (entityType.BaseType != null)
{
return(entityType.BaseType.IsIgnoredByMigrations());
}
if (entityType.GetTableName() != null)
{
return(false);
}
if (entityType.FindAnnotation(RelationalAnnotationNames.QueryableFunctionResultType) != null)
{
return(true);
}
var viewDefinition = entityType.FindAnnotation(RelationalAnnotationNames.ViewDefinition);
if (viewDefinition?.Value != null)
{
return(false);
}
var ownership = entityType.FindOwnership();
if (ownership != null &&
ownership.IsUnique)
{
return(ownership.PrincipalEntityType.IsIgnoredByMigrations());
}
return(true);
}
public override IEnumerable <IAnnotation> For(IEntityType entityType)
{
var baseAnnotations = base.For(entityType);
var interleaveInParentAnnotation = entityType.FindAnnotation(SpannerAnnotationNames.InterleaveInParent);
return(interleaveInParentAnnotation == null ? baseAnnotations
: baseAnnotations.Concat(new[] {
interleaveInParentAnnotation,
entityType.FindAnnotation(SpannerAnnotationNames.InterleaveInParentOnDelete)
}));
}
protected virtual void GenerateClass(
[NotNull] IEntityType entityType)
{
Check.NotNull(entityType, nameof(entityType));
var comment = entityType.FindAnnotation("Relational:Comment");
if (comment != null && comment.Value != null)
{
_sb.AppendLine("///<summary>");
_sb.AppendLine("///" + comment.Value.ToString());
_sb.AppendLine("///</summary>");
}
if (_useDataAnnotations)
{
GenerateEntityTypeDataAnnotations(entityType);
}
_sb.AppendLine($"public partial class {entityType.Name}");
_sb.AppendLine("{");
using (_sb.Indent())
{
GenerateConstructor(entityType);
GenerateProperties(entityType);
GenerateNavigationProperties(entityType);
}
_sb.AppendLine("}");
}
private void GenerateTableAttribute(IEntityType entityType)
{
var tableName = entityType.GetTableName();
var schema = entityType.GetSchema();
var defaultSchema = entityType.Model.GetDefaultSchema();
var schemaParameterNeeded = schema != null && schema != defaultSchema;
#if CORE50
var isView = entityType.GetViewName() != null;
#else
var isView = entityType.FindAnnotation(RelationalAnnotationNames.ViewDefinition) != null;
#endif
var tableAttributeNeeded = !isView && (schemaParameterNeeded || tableName != null && tableName != entityType.GetDbSetName());
if (tableAttributeNeeded)
{
var tableAttribute = new AttributeWriter(nameof(TableAttribute));
tableAttribute.AddParameter(_code.Literal(tableName));
if (schemaParameterNeeded)
{
tableAttribute.AddParameter($"{nameof(TableAttribute.Schema)} = {_code.Literal(schema)}");
}
_sb.AppendLine(tableAttribute.ToString());
}
}
/// <summary>
/// Gets a value indicating whether the associated table is ignored by Migrations.
/// </summary>
/// <param name="entityType">The entity type.</param>
/// <returns>A value indicating whether the associated table is ignored by Migrations.</returns>
public static bool IsTableExcludedFromMigrations([NotNull] this IEntityType entityType)
{
var excluded = (bool?)entityType[RelationalAnnotationNames.IsTableExcludedFromMigrations];
if (excluded != null)
{
return(excluded.Value);
}
if (entityType.FindAnnotation(RelationalAnnotationNames.TableName) != null)
{
return(false);
}
if (entityType.BaseType != null)
{
return(entityType.GetRootType().IsTableExcludedFromMigrations());
}
var ownership = entityType.FindOwnership();
if (ownership != null &&
ownership.IsUnique)
{
return(ownership.PrincipalEntityType.IsTableExcludedFromMigrations());
}
return(false);
}
private List <string> ValidateColumns(DatabaseModel databaseModel, IEntityType persistedType, SchemaValidationOptions validationOptions)
{
var valErrors = new List <string>();
foreach (var persistedColumn in persistedType.GetProperties())
{
var dbColumn = databaseModel.GetColumn(persistedColumn);
if (dbColumn == null)
{
valErrors.Add($"Missing column: {persistedColumn.GetColumnName()} in {persistedType.GetTableName()}");
continue;
}
var columnTypesMatch =
dbColumn.StoreType.Equals(persistedColumn.GetColumnType(), StringComparison.OrdinalIgnoreCase);
if (!columnTypesMatch)
{
valErrors.Add(
$"Column type mismatch in {persistedType.GetTableName()} for column {persistedColumn.GetColumnName()}. Found: {dbColumn.StoreType.ToLowerInvariant()}, Expected {persistedColumn.GetColumnType().ToLowerInvariant()}");
}
var isViewType = persistedType.FindAnnotation(RelationalAnnotationNames.ViewDefinition) != null;
var shouldValidateColumnNullability = (validationOptions.ValidateNullabilityForTables && !isViewType) || (validationOptions.ValidateNullabilityForViews && isViewType);
if (shouldValidateColumnNullability && persistedColumn.IsNullable != dbColumn.IsNullable)
{
valErrors.Add(
$"Column nullability mismatch in {persistedType.GetTableName()} for column {persistedColumn.GetColumnName()}. Found: {(dbColumn.IsNullable ? "Nullable" : "NotNullable")}, Expected {(persistedColumn.IsNullable ? "Nullable" : "NotNullable")}");
}
}
return(valErrors);
}
private void GenerateTableName(IEntityType entityType)
{
var tableName = entityType.GetTableName();
var schema = entityType.GetSchema();
var defaultSchema = entityType.Model.GetDefaultSchema();
var explicitSchema = schema != null && schema != defaultSchema;
var explicitTable = explicitSchema || tableName != null && tableName != entityType.GetDbSetName();
var isView = entityType.FindAnnotation(RelationalAnnotationNames.ViewDefinition) != null;
if (explicitTable || isView)
{
var parameterString = this._code.Literal(tableName);
if (explicitSchema)
{
parameterString += ", " + this._code.Literal(schema);
}
var lines = new List <string>
{
$".{(isView ? nameof(RelationalEntityTypeBuilderExtensions.ToView) : nameof(RelationalEntityTypeBuilderExtensions.ToTable))}({parameterString})"
};
this.AppendMultiLineFluentApi(entityType, lines);
}
}
private string GetNomeTabela(IEntityType entity)
{
//procurar a tabela através da entidade respectiva
var annotation = entity.FindAnnotation("Relational:TableName");
//retornar valor como string
return(annotation?.Value?.ToString());
}
public override IEnumerable <IAnnotation> For(IEntityType entityType)
{
var baseAnnotations = base.For(entityType);
var annotation = entityType.FindAnnotation("Comment");
return(annotation == null
? baseAnnotations
: baseAnnotations.Concat(new[] { annotation }));
}
public static bool IsUserDefinedType(this IEntityType model)
{
var result = model.FindAnnotation(CassandraAnnotationNames.IsUserDefinedType);
if (result == null)
{
return(false);
}
return((bool)result.Value);
}
public static IEnumerable <CassandraClusteringOrderByOption> GetClusteringOrderByOptions(this IEntityType model)
{
var result = model.FindAnnotation(CassandraAnnotationNames.ClusteringOrderByOptions);
if (result == null)
{
return(new CassandraClusteringOrderByOption[0]);
}
return(JsonConvert.DeserializeObject <IEnumerable <CassandraClusteringOrderByOption> >(result.Value.ToString()));
}
public static EntityInfo ToEntityInfo(this IEntityType et)
{
var annoName = et.FindAnnotation(TableNameAnnoName);
if (annoName == null)
{
throw new NotSupportedException("Entity must have a table name");
}
var annoSchema = et.FindAnnotation(TableSchemaAnnoName);
var info = new EntityInfo
{
Namespace = annoSchema != null?annoSchema.Value.ToString() : string.Empty,
EntityName = annoName.Value.ToString(),
Type = et.ClrType
};
return(info);
}
public static IEnumerable <string> GetStaticColumns(this IEntityType model)
{
var result = model.FindAnnotation(CassandraAnnotationNames.StaticColumns);
if (result == null)
{
return(new string[0]);
}
return((IEnumerable <string>)result.Value);
}
/// <summary>
/// Gets a value indicating whether the entity type is ignored by Migrations.
/// </summary>
/// <param name="entityType">The entity type.</param>
/// <returns>A value indicating whether the entity type is ignored by Migrations.</returns>
public static bool IsIgnoredByMigrations([NotNull] this IEntityType entityType)
{
if (entityType.BaseType != null)
{
return(entityType.BaseType.IsIgnoredByMigrations());
}
var viewDefinition = entityType.FindAnnotation(RelationalAnnotationNames.ViewDefinition);
return((viewDefinition != null && viewDefinition.Value == null) ||
entityType.GetDefiningQuery() != null);
}
/// <summary>
/// Returns the database schema that contains the mapped view.
/// </summary>
/// <param name="entityType"> The entity type to get the view schema for. </param>
/// <returns> The database schema that contains the mapped view. </returns>
public static string GetViewSchema([NotNull] this IEntityType entityType)
{
var schemaAnnotation = entityType.FindAnnotation(RelationalAnnotationNames.ViewSchema);
if (schemaAnnotation != null)
{
return((string)schemaAnnotation.Value ?? GetDefaultViewSchema(entityType));
}
return(entityType.BaseType != null
? entityType.GetRootType().GetViewSchema()
: GetDefaultViewSchema(entityType));
}
public override IEnumerable <IAnnotation> For(ITable table, bool designTime)
{
var annotations = base.For(table, designTime);
IEntityType entityType = table.EntityTypeMappings.First().EntityType;
IAnnotation?autoIncrement = entityType.FindAnnotation(AutoincrementAnnotation);
if (autoIncrement is not null)
{
annotations = annotations.Append(autoIncrement);
}
return(annotations);
}
/// <inheritdoc />
public override IEnumerable <IAnnotation> For(IEntityType entityType)
{
var baseAnnotations = base.For(entityType);
var annotation = entityType.FindAnnotation(CoRelationalAnnotationNames.Comment);
//if (annotation != null)
//{
// System.Diagnostics.Debugger.Launch();
//}
return(annotation == null
? baseAnnotations
: baseAnnotations.Concat(new[] { annotation }));
}
/// <summary>
/// Whether or not the <see cref="IEntityType"/> is configured as MultiTenant.
/// </summary>
/// <param name="entityType">The entity type to test for MultiTenant configuration.</param>
/// <returns><see cref="true"/> if the entity type has MultiTenant configuration, <see cref="false"/> if not.</returns>
public static bool IsMultiTenant(this IEntityType entityType)
{
while (entityType != null)
{
var hasMultiTenantAnnotation = (bool?)entityType.FindAnnotation(Constants.MultiTenantAnnotationName)?.Value ?? false;
if (hasMultiTenantAnnotation)
{
return(true);
}
entityType = entityType.BaseType;
}
return(false);
}
public override IEnumerable <IAnnotation> For(IEntityType entityType)
{
var baseAnnotations = base.For(entityType);
entityType = entityType.GetBaseEntity();
var annotationBase = entityType.FindAnnotation(SystemVersioningConstants.SqlServerSystemVersioning);
if (annotationBase != null)
{
return(baseAnnotations.Concat(new[] { annotationBase }));
}
return(baseAnnotations);
}
protected virtual void GenerateTableName([NotNull] IEntityType entityType, [NotNull] IndentedStringBuilder stringBuilder)
{
Check.NotNull(entityType, nameof(entityType));
Check.NotNull(stringBuilder, nameof(stringBuilder));
if (entityType.FindAnnotation(RelationalAnnotationNames.Prefix + RelationalAnnotationNames.TableName) == null &&
entityType.HasClrType())
{
stringBuilder
.AppendLine()
.Append("b.ToTable(")
.Append(_code.Literal(entityType.DisplayName()))
.AppendLine(");");
}
}
/// <summary>
/// Returns the name of the table to which the entity type is mapped
/// or <see langword="null" /> if not mapped to a table.
/// </summary>
/// <param name="entityType"> The entity type to get the table name for. </param>
/// <returns> The name of the table to which the entity type is mapped. </returns>
public static string GetTableName([NotNull] this IEntityType entityType)
{
if (entityType.BaseType != null)
{
return(entityType.GetRootType().GetTableName());
}
var nameAnnotation = entityType.FindAnnotation(RelationalAnnotationNames.TableName);
if (nameAnnotation != null)
{
return((string)nameAnnotation.Value);
}
return((entityType as IConventionEntityType)?.GetViewNameConfigurationSource() == null &&
((entityType as IConventionEntityType)?.GetDefiningQueryConfigurationSource() == null)
? GetDefaultTableName(entityType)
: null);
}
public override SelectExpression Select(IEntityType entityType)
{
if (entityType.FindAnnotation(SqlServerEntityTypeBuilderExtensions.ANNOTATION_TEMPORAL) != null)
{
var temporalTableExpression = new TemporalTableExpression(
entityType.GetTableName(),
entityType.GetSchema(),
entityType.GetTableName().ToLower().Substring(0, 1));
var selectContructor = typeof(SelectExpression).GetConstructor(BindingFlags.NonPublic | BindingFlags.Instance, null, new Type[] { typeof(IEntityType), typeof(TableExpressionBase) }, null);
var select = (SelectExpression)selectContructor.Invoke(new object[] { entityType, temporalTableExpression });
var privateInitializer = typeof(SqlExpressionFactory).GetMethod("AddConditions", BindingFlags.NonPublic | BindingFlags.Instance);
privateInitializer.Invoke(this, new object[] { select, entityType, null, null });
return(select);
}
return(base.Select(entityType));
}
/// <summary>
/// Returns the name of the view to which the entity type is mapped.
/// </summary>
/// <param name="entityType"> The entity type to get the view name for. </param>
/// <returns> The name of the view to which the entity type is mapped. </returns>
public static string GetViewName([NotNull] this IEntityType entityType)
{
if (entityType.BaseType != null)
{
return(entityType.GetRootType().GetViewName());
}
if (entityType.FindAnnotation(RelationalAnnotationNames.ViewDefinition) != null)
{
return(entityType.GetTableName());
}
var ownership = entityType.FindOwnership();
if (ownership != null &&
ownership.IsUnique)
{
return(ownership.PrincipalEntityType.GetViewName());
}
return(null);
}
/// <summary>
/// Returns the name of the table to which the entity type is mapped
/// or <see langword="null" /> if not mapped to a table.
/// </summary>
/// <param name="entityType"> The entity type to get the table name for. </param>
/// <returns> The name of the table to which the entity type is mapped. </returns>
public static string GetTableName([NotNull] this IEntityType entityType)
{
var nameAnnotation = entityType.FindAnnotation(RelationalAnnotationNames.TableName);
if (nameAnnotation != null)
{
return((string)nameAnnotation.Value);
}
if (entityType.BaseType != null)
{
return(entityType.GetRootType().GetTableName());
}
return((entityType as IConventionEntityType)?.GetViewNameConfigurationSource() == null &&
((entityType as IConventionEntityType)?.GetFunctionNameConfigurationSource() == null)
#pragma warning disable CS0618 // Type or member is obsolete
&& ((entityType as IConventionEntityType)?.GetDefiningQueryConfigurationSource() == null)
#pragma warning restore CS0618 // Type or member is obsolete
? GetDefaultTableName(entityType)
: null);
}
private void GenerateEntityType(IEntityType entityType, bool useDataAnnotations)
{
this.GenerateKey(entityType.FindPrimaryKey(), entityType, useDataAnnotations);
var annotations = entityType.GetAnnotations().ToList();
RemoveAnnotation(ref annotations, CoreAnnotationNames.ConstructorBinding);
RemoveAnnotation(ref annotations, RelationalAnnotationNames.TableName);
RemoveAnnotation(ref annotations, RelationalAnnotationNames.Comment);
RemoveAnnotation(ref annotations, RelationalAnnotationNames.Schema);
RemoveAnnotation(ref annotations, ScaffoldingAnnotationNames.DbSetName);
RemoveAnnotation(ref annotations, RelationalAnnotationNames.ViewDefinition);
var isView = entityType.FindAnnotation(RelationalAnnotationNames.ViewDefinition) != null;
if (!useDataAnnotations || isView)
{
this.GenerateTableName(entityType);
}
var annotationsToRemove = new List <IAnnotation>();
var lines = new List <string>();
foreach (var annotation in annotations)
{
if (annotation.Value == null ||
this._annotationCodeGenerator.IsHandledByConvention(entityType, annotation))
{
annotationsToRemove.Add(annotation);
}
else
{
var methodCall = this._annotationCodeGenerator.GenerateFluentApi(entityType, annotation);
if (methodCall != null)
{
lines.Add(this._code.Fragment(methodCall));
annotationsToRemove.Add(annotation);
}
}
}
lines.AddRange(this.GenerateAnnotations(annotations.Except(annotationsToRemove)));
if (entityType.GetComment() != null)
{
lines.Add(
$".{nameof(RelationalEntityTypeBuilderExtensions.HasComment)}" +
$"({this._code.Literal(entityType.GetComment())})");
}
this.AppendMultiLineFluentApi(entityType, lines);
foreach (var index in entityType.GetIndexes())
{
this.GenerateIndex(index);
}
foreach (var property in entityType.GetProperties())
{
this.GenerateProperty(property, useDataAnnotations);
}
foreach (var foreignKey in entityType.GetForeignKeys())
{
this.GenerateRelationship(foreignKey, useDataAnnotations);
}
}
/// <summary>
/// Whether or not the <see cref="IEntityType"/> is configured as MultiTenant.
/// </summary>
/// <param name="entityType">The entity type to test for MultiTenant configuration.</param>
/// <returns><see cref="true"/> if the entity type has MultiTenant configuration, <see cref="false"/> if not.</returns>
public static bool IsMultiTenant(this IEntityType entityType)
{
return((bool?)entityType.FindAnnotation(Constants.MultiTenantAnnotationName)?.Value ?? false);
}
protected string GetTableName(IEntityType entity)
{
var annotation = entity.FindAnnotation("Relational:TableName");
return(annotation?.Value?.ToString());
}
/// <summary>Determines whether the passed in entity type has the readonly annotation set.
/// </summary>
/// <param name="entity">The entity type to check.</param>
/// <returns>true if the entity type is marked as read-only, false otherwise</returns>
public static bool IsReadOnly(this IEntityType entity)
{
var annotation = entity.FindAnnotation(READONLY_ANNOTATION);
return(annotation != null && (bool)annotation.Value);
}
public static IQueryable <TEntity> TreeQuery <TEntity, TKey>(string invariantName, IQueryable <TEntity> query, IEntityType entityType, Expression <Func <TEntity, bool> > startQuery, Expression <Func <TEntity, bool> > whereQuery = null, bool upSearch = false, string orderByProperty = null, int level = 0, bool distinct = false)
where TEntity : class, IEntity <TKey> //ITreeEntity<TKey>
//where TKey : struct, IEquatable<TKey>
{
var tableAnnn = entityType.FindAnnotation("Relational:TableName");
string tableName = tableAnnn?.Value.ToString();
var anno = entityType.FindProperty(nameof(ITreeEntity <int> .Pid)).FindAnnotation("Relational:ColumnName");
string parentFieldName = anno != null?anno.Value.ToString() : nameof(ITreeEntity <int> .Pid);
var idAnno = entityType.FindProperty(nameof(ITreeEntity <int> .Id)).FindAnnotation("Relational:ColumnName");
string fieldName = idAnno != null?idAnno.Value.ToString() : nameof(ITreeEntity <int> .Id);
string firstQuery = query.Where(startQuery).ToSql().Replace(Environment.NewLine, " ");
string startQueryParament = startQuery.Parameters[0].Name;
bool forNpgsql = IsNpgSql(invariantName);
string orderByFieldName = null;
if (!string.IsNullOrEmpty(orderByProperty))
{
var orderByAnno = entityType.FindProperty(orderByProperty).FindAnnotation("Relational:ColumnName");
orderByFieldName = orderByAnno != null?orderByAnno.Value.ToString() : string.Format("\"{0}\"", orderByProperty);
//forNpgsql = invariantName == "Npgsql.EntityFrameworkCore.PostgreSQL";
}
if (forNpgsql && !string.IsNullOrEmpty(orderByFieldName))
{
firstQuery = firstQuery.Insert(firstQuery.IndexOf(" FROM", StringComparison.CurrentCultureIgnoreCase), string.Format(", 0 As level, array[\"{0}\".\"{1}\"] as sorder", startQueryParament, orderByFieldName));
}
else
{
firstQuery = firstQuery.Insert(firstQuery.IndexOf(" FROM", StringComparison.CurrentCultureIgnoreCase), ", 0 As level");
}
string secondQuery = null;
string thirdQuery = null;
string whereQueryParament = tableName.Substring(0, 1).ToLower();
if (whereQuery == null)
{
secondQuery = query.ToSql().Replace(Environment.NewLine, " ");
thirdQuery = secondQuery;
if (forNpgsql && !string.IsNullOrEmpty(orderByFieldName))
{
secondQuery = secondQuery.Insert(secondQuery.IndexOf(" FROM", StringComparison.CurrentCultureIgnoreCase), string.Format(", mytree.level + 1, sorder || \"{0}\".\"{1}\" as sorder", whereQueryParament, orderByFieldName));
}
else
{
secondQuery = secondQuery.Insert(secondQuery.IndexOf(" FROM", StringComparison.CurrentCultureIgnoreCase), ", mytree.level + 1");
}
if (upSearch)
{
secondQuery += string.Format(" join mytree on \"{0}\".\"{1}\" = \"mytree\".\"{2}\" ", whereQueryParament, fieldName, parentFieldName);
}
else
{
secondQuery += string.Format(" join mytree on \"{0}\".\"{1}\" = \"mytree\".\"{2}\" ", whereQueryParament, parentFieldName, fieldName);
}
}
else
{
secondQuery = query.Where(whereQuery).ToSql().Replace(Environment.NewLine, " ");
thirdQuery = secondQuery;
whereQueryParament = startQuery.Parameters[0].Name;
if (whereQueryParament == startQueryParament)
{
if (forNpgsql)
{
string forReplace = startQueryParament + ".";
whereQueryParament += startQueryParament;
string toReplace = whereQueryParament + ".";
secondQuery = secondQuery.Replace(forReplace, toReplace);
secondQuery = secondQuery.Replace("AS " + startQueryParament, "AS " + whereQueryParament);
}
else
{
string forReplace = "\"" + startQueryParament + "\"";
whereQueryParament += startQueryParament;
string toReplace = "\"" + whereQueryParament + "\"";
secondQuery = secondQuery.Replace(forReplace, toReplace);
}
}
if (forNpgsql)
{
secondQuery = secondQuery.Insert(secondQuery.IndexOf(" FROM", StringComparison.CurrentCultureIgnoreCase), string.Format(", mytree.level + 1, sorder || \"{0}\".\"{1}\" as sorder", whereQueryParament, orderByFieldName));
}
else
{
secondQuery = secondQuery.Insert(secondQuery.IndexOf(" FROM", StringComparison.CurrentCultureIgnoreCase), ", mytree.level + 1");
}
if (upSearch)
{
secondQuery = secondQuery.Insert(secondQuery.IndexOf(" WHERE", StringComparison.CurrentCultureIgnoreCase), string.Format(" join mytree on \"{0}\".\"{1}\" = \"mytree\".\"{2}\" ", whereQueryParament, fieldName, parentFieldName));
}
else
{
secondQuery = secondQuery.Insert(secondQuery.IndexOf(" WHERE", StringComparison.CurrentCultureIgnoreCase), string.Format(" join mytree on \"{0}\".\"{1}\" = \"mytree\".\"{2}\" ", whereQueryParament, parentFieldName, fieldName));
}
}
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("with recursive mytree as ( ");
sqlBuilder.Append(firstQuery);
sqlBuilder.Append(" union all ");
sqlBuilder.Append(secondQuery);
if (!forNpgsql)
{
sqlBuilder.Append(" order by level desc");
if (!string.IsNullOrEmpty(orderByFieldName))
{
//var orderByAnno = entityType.FindProperty(orderByProperty).FindAnnotation("Relational:ColumnName");
//string orderByFieldName = orderByAnno!=null? orderByAnno.Value.ToString() : string.Format("\"{0}\"", orderByProperty);
sqlBuilder.Append(string.Format(", {0}", orderByFieldName));
}
}
sqlBuilder.Append(") ");
if (distinct)
{
thirdQuery = thirdQuery.Replace("SELECT", "SELECT DISTINCT");
}
thirdQuery = thirdQuery.Replace(tableName, "mytree");
sqlBuilder.Append(thirdQuery);
if (level > 0)
{
if (whereQuery == null)
{
sqlBuilder.Append(" where ");
}
else
{
sqlBuilder.Append(" and ");
}
sqlBuilder.Append(string.Format("level = {0}", level));
}
if (forNpgsql && !string.IsNullOrEmpty(orderByFieldName))
{
sqlBuilder.Append(" order by sorder");
}
return(query.FromSql(sqlBuilder.ToString()));
}
public static IQueryable <TResult> TreeQuery <TEntity, TKey, TResult>(IRepositoryHelper repositoryHelper, IQueryable <TEntity> query, IEntityType entityType, Expression <Func <TEntity, bool> > startQuery, Expression <Func <TEntity, TResult> > selector, Expression <Func <TEntity, bool> > whereQuery = null, bool upSearch = false, string orderByProperty = null, int level = 0, bool distinct = false)
where TEntity : class, IEntity <TKey> //ITreeEntity<TKey>
where TResult : class
//where TKey : struct, IEquatable<TKey>
{
var tableAnnn = entityType.FindAnnotation("Relational:TableName");
string tableName = tableAnnn?.Value.ToString();
var anno = entityType.FindProperty(nameof(ITreeEntity.Pid)).FindAnnotation("Relational:ColumnName");
string parentFieldName = anno != null?anno.Value.ToString() : nameof(ITreeEntity <int> .Pid);
var idAnno = entityType.FindProperty(nameof(ITreeEntity.Id)).FindAnnotation("Relational:ColumnName");
string fieldName = idAnno != null?idAnno.Value.ToString() : nameof(ITreeEntity <int> .Id);
List <DbParameter> dbParameters = new List <DbParameter>();
SqlWithParameters firstSqls = query.Where(startQuery).Select(selector).GetSqlTextWithParement();;
AddDbParameter(repositoryHelper, dbParameters, firstSqls);
string firstQuery = firstSqls.Sql.Replace(Environment.NewLine, " ");
string startQueryParament = startQuery.Parameters[0].Name;
bool forNpgsql = IsNpgSql(repositoryHelper?.ProviderName);
string orderByFieldName = null;
if (!string.IsNullOrEmpty(orderByProperty))
{
var orderByAnno = entityType.FindProperty(orderByProperty).FindAnnotation("Relational:ColumnName");
orderByFieldName = orderByAnno != null?orderByAnno.Value.ToString() : string.Format("\"{0}\"", orderByProperty);
//forNpgsql = repositoryHelper.ProviderName == "Npgsql.EntityFrameworkCore.PostgreSQL";
}
if (forNpgsql && !string.IsNullOrEmpty(orderByFieldName))
{
firstQuery = firstQuery.Insert(firstQuery.IndexOf(" FROM", StringComparison.CurrentCultureIgnoreCase), string.Format(", 0 As level, array[\"{0}\".\"{1}\"] as sorder", startQueryParament, orderByFieldName));
}
else
{
firstQuery = firstQuery.Insert(firstQuery.IndexOf(" FROM", StringComparison.CurrentCultureIgnoreCase), ", 0 As level");
}
//with recursive mytree as (
//select "t"."id", "t"."createtime", "t"."creator", "t"."customdata", "t"."displayname", "t"."group", "t"."icon", "t"."isenabled", "t"."isvisible", "t"."lastmodifytime", "t"."modifier", "t"."name", "t"."pid", "t"."platsupport", "t"."requiredpermissionname", "t"."requiresauthentication", "t"."sindex", "t"."target", "t"."url", 0 as level, ARRAY[t.sindex] as sorder from "menus" as "t" where "t"."pid" is null
//union all
//select "tt"."id", "tt"."createtime", "tt"."creator", "tt"."customdata", "tt"."displayname", "tt"."group", "tt"."icon", "tt"."isenabled", "tt"."isvisible", "tt"."lastmodifytime", "tt"."modifier", "tt"."name", "tt"."pid", "tt"."platsupport", "tt"."requiredpermissionname", "tt"."requiresauthentication", "tt"."sindex", "tt"."target", "tt"."url", mytree.level + 1, sorder || tt.sindex as sorder from "menus" as "tt" join mytree on "tt"."pid" = "mytree"."id" where "tt"."group" = 0
//)
//select * from "mytree" as "t" where "t"."group" = 0 ORDER BY SORDER
string secondQuery = null;
string thirdQuery = null;
string whereQueryParament = tableName.Substring(0, 1).ToLower();
if (whereQuery == null)
{
secondQuery = query.Select(selector).ToSql().Replace(Environment.NewLine, " ");
whereQueryParament = selector.Parameters[0].Name;
thirdQuery = secondQuery;
if (forNpgsql && !string.IsNullOrEmpty(orderByFieldName))
{
secondQuery = secondQuery.Insert(secondQuery.IndexOf(" FROM", StringComparison.CurrentCultureIgnoreCase), string.Format(", mytree.level + 1, sorder || \"{0}\".\"{1}\" as sorder", whereQueryParament, orderByFieldName));
}
else
{
secondQuery = secondQuery.Insert(secondQuery.IndexOf(" FROM", StringComparison.CurrentCultureIgnoreCase), ", mytree.level + 1");
}
if (upSearch)
{
secondQuery += string.Format(" join mytree on \"{0}\".\"{1}\" = \"mytree\".\"{2}\" ", whereQueryParament, fieldName, parentFieldName);
}
else
{
secondQuery += string.Format(" join mytree on \"{0}\".\"{1}\" = \"mytree\".\"{2}\" ", whereQueryParament, parentFieldName, fieldName);
}
}
else
{
SqlWithParameters secondSqls = secondSqls = query.Where(whereQuery).Select(selector).GetSqlTextWithParement();
AddDbParameter(repositoryHelper, dbParameters, secondSqls);
secondQuery = secondSqls.Sql.Replace(Environment.NewLine, " ");
thirdQuery = secondQuery;
whereQueryParament = whereQuery.Parameters[0].Name;
if (whereQueryParament == startQueryParament)
{
if (forNpgsql)
{
string forReplace = startQueryParament + ".";
whereQueryParament += startQueryParament;
string toReplace = whereQueryParament + ".";
secondQuery = secondQuery.Replace(forReplace, toReplace);
secondQuery = secondQuery.Replace("AS " + startQueryParament, "AS " + whereQueryParament);
}
else
{
string forReplace = "\"" + startQueryParament + "\"";
whereQueryParament += startQueryParament;
string toReplace = "\"" + whereQueryParament + "\"";
secondQuery = secondQuery.Replace(forReplace, toReplace);
}
}
if (forNpgsql)
{
secondQuery = secondQuery.Insert(secondQuery.IndexOf(" FROM", StringComparison.CurrentCultureIgnoreCase), string.Format(", mytree.level + 1, sorder || \"{0}\".\"{1}\" as sorder", whereQueryParament, orderByFieldName));
}
else
{
secondQuery = secondQuery.Insert(secondQuery.IndexOf(" FROM", StringComparison.CurrentCultureIgnoreCase), ", mytree.level + 1");
}
if (upSearch)
{
secondQuery = secondQuery.Insert(secondQuery.IndexOf(" WHERE", StringComparison.CurrentCultureIgnoreCase), string.Format(" join mytree on \"{0}\".\"{1}\" = \"mytree\".\"{2}\" ", whereQueryParament, fieldName, parentFieldName));
}
else
{
secondQuery = secondQuery.Insert(secondQuery.IndexOf(" WHERE", StringComparison.CurrentCultureIgnoreCase), string.Format(" join mytree on \"{0}\".\"{1}\" = \"mytree\".\"{2}\" ", whereQueryParament, parentFieldName, fieldName));
}
}
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("with recursive mytree as ( ");
sqlBuilder.Append(firstQuery);
sqlBuilder.Append(" union all ");
sqlBuilder.Append(secondQuery);
if (!forNpgsql)
{
sqlBuilder.Append(" order by level desc");
if (!string.IsNullOrEmpty(orderByFieldName))
{
//var orderByAnno = entityType.FindProperty(orderByProperty).FindAnnotation("Relational:ColumnName");
//string orderByFieldName = orderByAnno!=null? orderByAnno.Value.ToString() : string.Format("\"{0}\"", orderByProperty);
sqlBuilder.Append(string.Format(", {0}", orderByFieldName));
}
}
sqlBuilder.Append(") ");
if (distinct)
{
thirdQuery = thirdQuery.Replace("SELECT", "SELECT DISTINCT");
}
thirdQuery = thirdQuery.Replace(tableName, "mytree");
sqlBuilder.Append(thirdQuery);
if (level > 0)
{
if (whereQuery == null)
{
sqlBuilder.Append(" where ");
}
else
{
sqlBuilder.Append(" and ");
}
sqlBuilder.Append(string.Format("level = {0}", repositoryHelper.CreateParameterName("level")));
AddDbParameter(repositoryHelper, dbParameters, "level", level);
}
if (forNpgsql && !string.IsNullOrEmpty(orderByFieldName))
{
sqlBuilder.Append(" order by sorder");
}
//外部where
// var pp = db.Database.GetDbConnection().CreateCommand().CreateParameter();
// pp.ParameterName ="tid";
// pp.Value = 1;
return(query.Select(selector).FromSql(sqlBuilder.ToString(), dbParameters.ToArray()));
}
