<commit_before>﻿namespace Backend.Fx.EfCorePersistence.Mssql
{
    using System;
    using System.Collections.Generic;
    using System.Diagnostics;
    using System.Linq;
    using System.Linq.Expressions;
    using BuildingBlocks;
    using Microsoft.EntityFrameworkCore;

    public abstract class MsSqlFullTextSearchIndex<TAggregateRoot> : IFullTextSearchIndex where TAggregateRoot : AggregateRoot
    {
        private const string FullTextCatalogName = "FullTextSearch";
        private readonly string mssqlLocaleId = "1031"; // TODO: still hard coded to Germany

        public abstract Expression<Func<TAggregateRoot, object>>[] IndexedProperties { get; }

        public void EnsureIndex(DbContext dbContext)
        {
            var entityType = dbContext.Model.FindEntityType(typeof(TAggregateRoot));
            var relationalEntityTypeAnnotations = entityType.Relational();
            string schema = relationalEntityTypeAnnotations.Schema ?? "dbo";
            string table = relationalEntityTypeAnnotations.TableName;

            IEnumerable<string> indexedColumnDefinitions = IndexedProperties
                    .Select(prop => entityType.FindProperty(GetMemberName(prop)).Relational().ColumnName)
                    .Select(col => $"{col} Language {mssqlLocaleId}");

            //var primaryKey = entityType.FindPrimaryKey();
            //var mutableIndex = entityType.FindIndex(primaryKey.Properties);
            //string primaryKeyIndexName = mutableIndex.Relational().Name;
            string primaryKeyIndexName = $"PK_{table}";

            string indexedColumnsSqlFragment = string.Join(", ", indexedColumnDefinitions);

            dbContext.Database.ExecuteSqlCommand(
                    $"IF ((SELECT count(*) FROM sys.fulltext_catalogs WHERE name = '{FullTextCatalogName}') = 0) CREATE FULLTEXT CATALOG {FullTextCatalogName}");

            dbContext.Database.ExecuteSqlCommand(
                    $"IF ((SELECT count(*) FROM sys.fulltext_indexes i INNER JOIN sys.tables t ON t.object_id = i.object_id WHERE t.name = '{table}') = 0) " +
                    $" CREATE FULLTEXT INDEX ON [{schema}].[{table}] ({indexedColumnsSqlFragment}) KEY INDEX {primaryKeyIndexName} ON {FullTextCatalogName}");
        }

        private static string GetMemberName(Expression<Func<TAggregateRoot, object>> exp)
        {<commit_msg>Fix: hand crafted SQL vs parameters<commit_after>﻿namespace Backend.Fx.EfCorePersistence.Mssql
{
    using System;
    using System.Collections.Generic;
    using System.Diagnostics;
    using System.Linq;
    using System.Linq.Expressions;
    using BuildingBlocks;
    using Microsoft.EntityFrameworkCore;

    public abstract class MsSqlFullTextSearchIndex<TAggregateRoot> : IFullTextSearchIndex where TAggregateRoot : AggregateRoot
    {
        private const string FullTextCatalogName = "FullTextSearch";
        private readonly string mssqlLocaleId = "1031"; // TODO: still hard coded to Germany

        public abstract Expression<Func<TAggregateRoot, object>>[] IndexedProperties { get; }

        public void EnsureIndex(DbContext dbContext)
        {
            var entityType = dbContext.Model.FindEntityType(typeof(TAggregateRoot));
            var relationalEntityTypeAnnotations = entityType.Relational();
            string schema = relationalEntityTypeAnnotations.Schema ?? "dbo";
            string table = relationalEntityTypeAnnotations.TableName;

            IEnumerable<string> indexedColumnDefinitions = IndexedProperties
                    .Select(prop => entityType.FindProperty(GetMemberName(prop)).Relational().ColumnName)
                    .Select(col => $"{col} Language {mssqlLocaleId}");

            //var primaryKey = entityType.FindPrimaryKey();
            //var mutableIndex = entityType.FindIndex(primaryKey.Properties);
            //string primaryKeyIndexName = mutableIndex.Relational().Name;
            string primaryKeyIndexName = $"PK_{table}";

            string indexedColumnsSqlFragment = string.Join(", ", indexedColumnDefinitions);

            dbContext.Database.ExecuteSqlCommand(
                    $"IF ((SELECT count(*) FROM sys.fulltext_catalogs WHERE name = '{FullTextCatalogName}') = 0) CREATE FULLTEXT CATALOG " + FullTextCatalogName);

            dbContext.Database.ExecuteSqlCommand(
                    $"IF ((SELECT count(*) FROM sys.fulltext_indexes i INNER JOIN sys.tables t ON t.object_id = i.object_id WHERE t.name = '{table}') = 0) " +
                    " CREATE FULLTEXT INDEX ON ["+schema+"].["+table+"] ("+indexedColumnsSqlFragment+") KEY INDEX "+primaryKeyIndexName+" ON "+FullTextCatalogName);
        }

        private static string GetMemberName(Expression<Func<TAggregateRoot, object>> exp)
        {
{'fraction_non_alphanumeric': 0.09567966280295047, 'fraction_numerical': 0.0025289778714436247, 'mean_word_length': 3.1486013986013988, 'pattern_counts': {'":': 0, '<': 15, '<?xml version=': 0, '>': 19, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3872521', 'n_tokens_mistral': 1262, 'n_tokens_neox': 1225, 'n_words': 279}