Double Metaphone XAF

This is a PoC to use DoubleMetaphone algorithm in XAF

• Build DoubleMetaphone project

• Register dll in SQL Server

  You can follow these steps (here I am using Northwind):

        ALTER DATABASE Northwind
        SET TRUSTWORTHY ON;

        CREATE ASSEMBLY DoubleMetaphone
        FROM 'C:\Temp\DoubleMetaphone.dll' WITH PERMISSION_SET = SAFE

        ALTER DATABASE Northwind
        SET TRUSTWORTHY OFF;

Visualize list of assembly in db

    SELECT * from sys.assemblies

From Assembly select DoubleMetaphone and create script from assembly in clipboard Clipboard:

CREATE ASSEMBLY [DoubleMetaphone]
FROM 0x4D5A90...
WITH PERMISSION_SET = SAFE
GO

Copy 0x4D5A90... in variable @asmBin and run this script to add assembly in trusted assembly

USE master;
GO
DECLARE @clrName nvarchar(4000) = 'doublemetaphone, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil'
DECLARE @asmBin varbinary(max) = 0x4D5A90...;
DECLARE @hash varbinary(64);

SELECT @hash = HASHBYTES('SHA2_512', @asmBin);

EXEC sys.sp_add_trusted_assembly @hash = @hash,
                                 @description = @clrName;

Visualize list of assembly trusted

select * FROM sys.trusted_assemblies

Create function in db

CREATE FUNCTION dbo.udf_double_metaphone(@value nvarchar(max)) 
RETURNS nvarchar(max)   
AS EXTERNAL NAME DoubleMetaphone.DoubleMetaphoneUDT.DoubleMetaphoneExec;   
GO 

Enable crl

EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;  
GO 

In project XAF agnostic module create a custom function

    public class DoubleMetaphone : ICustomFunctionOperatorFormattable
    {
        public string Name
        {
            get
            {
                return "DoubleMetaphone";
            }
        }
        public object Evaluate(params object[] operands)
        {
            throw new NotImplementedException();
        }
        public Type ResultType(params Type[] operands)
        {
            return typeof(object);
        }
        static DoubleMetaphone()
        {
            DoubleMetaphone instance = new DoubleMetaphone();
            if (CriteriaOperator.GetCustomFunction(instance.Name) == null)
            {
                CriteriaOperator.RegisterCustomFunction(instance);
            }
        }
        public static void Register()
        {
        }

        public string Format(Type providerType, params string[] operands)
        {
            if (providerType == typeof(MSSqlConnectionProvider))
            {
               
                return $"dbo.udf_double_metaphone({operands[0]}) = {operands[1]}";
            }
                
            throw new NotSupportedException($"This provider is not supported: {providerType.Name}");           
        }
    }

In module.cs register the custom function

public sealed partial class DoubleMetaphoneXAFModule : ModuleBase {
        public DoubleMetaphoneXAFModule() {
            InitializeComponent();
            Functions.DoubleMetaphone.Register();
            ....
        }
        
    }

Add a controller to custom search standard

public partial class VCSearchDoubleMetaphone : ViewController
    {
        // Use CodeRush to create Controllers and Actions with a few keystrokes.
        // https://docs.devexpress.com/CodeRushForRoslyn/403133/
        public VCSearchDoubleMetaphone()
        {
            InitializeComponent();
            // Target required Views (via the TargetXXX properties) and create their Actions.
            TargetObjectType = typeof(Customers);
        }
        private FilterController standardFilterController;
        protected override void OnActivated()
        {
            base.OnActivated();
            standardFilterController = Frame.GetController<FilterController>();
            if (standardFilterController != null)
            {
                
                standardFilterController.CustomBuildCriteria += StandardFilterController_CustomBuildCriteria;
            }
        }

        private void StandardFilterController_CustomBuildCriteria(object sender, CustomBuildCriteriaEventArgs e)
        {
            if (string.IsNullOrEmpty(e.SearchText))
            {
                e.Handled = false;
                return;
            }
            
            e.Criteria = CriteriaOperator.Parse($"DoubleMetaphone('{e.SearchText}', [CompanyNameDoubleMetaphone]) or CharIndex('{e.SearchText}', [CompanyName]) > -1");
            e.Handled = true;    
        }

        
        protected override void OnDeactivated()
        {
            if (standardFilterController != null)
            {
                standardFilterController.CustomBuildCriteria -= StandardFilterController_CustomBuildCriteria;
            }
            base.OnDeactivated();
        }
    }

In db Northwind add the field CompanyNameDoubleMetaphone in table Customers

USE [Northwind]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER TABLE Customers
ADD [CompanyNameDoubleMetaphone]  AS ([dbo].[udf_double_metaphone]([CompanyName])) PERSISTED
GO

In table customers I have added three similar strings (CompanyName field)

'Blauer See    Delikatessen sa'
'Blauer Se    Delikatessen s.a.'
'Blaer Se    Deliktesse s.a.'

and for example I search for 'Blaer Se Delikatessen' then I match all strings