I have a big query wrapped in a sproc that takes some params and uses the soundex function which i don't belive is supported by llblgen as its a mssql function. We use this to check for duplicates in our system, and need to redo it for our new system which is using llblgen adapter model. Its a simple select query from one table, but has about ten parameters and on some of them uses Soundex function. I would like to stay away from having a sproc just for this one module. Is there a way to build my own sql string and paramater collection and then execute this through llblgen some how? Samples anyone?

Thanks.

I'm unfamiliar with the SOUNDEX function, looking into BOL I only see silly examples as SELECT SOUNDEX(...), SOUNDEX(...) but nothing in a where clause, and my attempts to produce a query with SOUNDEX fails. Could you give a simple example how this should be used?

Jeff,

If the SOUNDEX is simply in the where clause, you might consider generating the SOUNDEX code in you BL as a workaround using a SOUNDEX method.

using System;
using System.Text;

public class Soundex
{
    private Soundex()
    {}

    public static String SoundexCodeConverter(String text)
    {
        String word = text.ToUpper();
        StringBuilder soundexCode = new StringBuilder();

        int wordLength = word.Length;
        soundexCode.Append(word.Substring(0, 1));

        for (int i = 1; i < wordLength; i++)
        {
            String s_transformedChar = Transform(word.Substring(i, 1));
            if (!s_transformedChar.Equals(soundexCode.ToString().Substring(soundexCode.Length - 1)))
            {
                if (!s_transformedChar.Equals("A") && !s_transformedChar.Equals("S"))
                {if (!s_transformedChar.Equals(" "))
                        soundexCode.Append(s_transformedChar);
                }
            }
        }
        soundexCode.Append("0000");

        return soundexCode.ToString().Substring(0, 4);
    }

    private static String Transform(String character)
    {
        switch (character)
        {
            case "A":
            case "E":
            case "I":
            case "O":
            case "U":
            case "Y":
                return "A";

            case "H":
            case "W":
                return "S";

            case "B":
            case "F":
            case "P":
            case "V":
                return "1";

            case "C":
            case "G":
            case "J":
            case "K":
            case "Q":
            case "S":
            case "X":
            case "Z":
                return "2";

            case "D":
            case "T":
                return "3";

            case "L":
                return "4";

            case "M":
            case "N":
                return "5";

            case "R":
                return "6";

        }

        return " ";
    }
}

Marcus

In reponse to Marcus, if i follow you, you are saying run the soundex outside of the db. I will consider this, but i don't know if that will work becuase then i am bringing the data out of the db to process it. I will think about this though.

To "Otis": I really have only used them on a few occasions, but they work well when the Like statement is not enough. Also, its a huge resource hog if you don't use it correctly.

Basically there are two functions, Differenence and Soundex. Difference takes two parameters and returns a number measuring their similarities. Soundex takes only one parameter and returns a number also, which reprents a hash or some sort of the vowells in the word. This is my interpretation and there is much room for error here of course

This is how i have used them in the passed:

Select SomeFields From SomeTable Where

{using difference} ((Difference( FirstName, @fname ) + Difference( LastName, @lname )) >= 7) --OR-- {using soundex} (Soundex(FirstName) = Soundex(@fname) & Soundex(LastName) = Soundex(@lname))

something like that.

Jeff wrote:

In reponse to Marcus, if i follow you, you are saying run the soundex outside of the db. I will consider this, but i don't know if that will work becuase then i am bringing the data out of the db to process it. I will think about this though.

Select SomeFields From SomeTable Where

{using difference} ((Difference( FirstName, @fname ) + Difference( LastName, @lname )) >= 7) --OR-- {using soundex} (Soundex(FirstName) = Soundex(@fname) & Soundex(LastName) = Soundex(@lname))

Jeff,

I have used SOUNDEX in the past and I have found that the best results can be obtained by pre-calculating and saving the SOUNDEX values in the table along with the original names. Performing SELECTS then become trivial since it's a simple lookup. Running the SOUNDEX function on each field during the SELECT will be orders of magnitude less performant as you have to calculate the values for every row in the table on each SELECT...

Marcus

i agree completely. I am trying to convince them to go this route. However, regardless i would like to know if it is possible with llblgen to execute my own sql and param collection, and how you would do that?

Jeff wrote:

i agree completely. I am trying to convince them to go this route. However, regardless i would like to know if it is possible with llblgen to execute my own sql and param collection, and how you would do that?

To use the SOUNDEX feature in your code, you can opt for creating a new predicate class, which you build using the code from FieldLikePredicate. Just copy the code over from that class in teh runtime libraries code to a class in your own project. Then, alter the ToQueryText in such a way that you instead of emitting: queryText.AppendFormat("{0} LIKE {1}", base.DatabaseSpecificCreator.CreateFieldName(_field, _persistenceInfo, _field.Name, _objectAlias, ref uniqueMarker, inHavingClause), parameter.ParameterName);

you do: queryText.AppendFormat("SOUNDEX({0}) = SOUNDEX({1})", base.DatabaseSpecificCreator.CreateFieldName(_field, _persistenceInfo, _field.Name, _objectAlias, ref uniqueMarker, inHavingClause), parameter.ParameterName);

(just remove the case sensitive code)

Then, when you produce the filter, use something like: PredicateExpression filter = new PredicateExpression() filter.Add(new FieldSoundExPredicate(EntityFieldFactory.Create(CustomerFieldIndex.ContactFirstName), "John")); (this is selfservicing, if you use adapter, you've to pass in null as second parameter) which will then become:

WHERE SOUNDEX([Customer].[ContactFirstName]) = SOUNDEX(@param1)

you can combine them ofcourse with normal predicate expression code.

If you need further help, please let me know.

This doesnt work in my own project since the SelfServicing property is internal in the Predicate base class. Any Idea? Can you include that FieldSoundExPredicate in the runtime library by default? I dont have any other idea how to implement a basic "fuzzy search" in SQL-Server. Has anyone else maybe?

Thanks, Florian

You can also opt to implement IPredicate. It's the interface that matters. The Predicate base class implements some things for you, but you can also implement them yourself, as long as you implement IPredicate. SelfServicing is default true, if that helps you

Otis wrote:

To use the SOUNDEX feature in your code, you can opt for creating a new predicate class, which you build using the code from FieldLikePredicate.

Has anyone done this yet?

I have run into a situation where this little SOUNDEX predicate might come in quite handy.

Take care,

Leo

yeah it works well. I created a new "PredicateExtendedFactory" including a "FuzzyLike" Method which I can use to create fuzzy queries as usual ones:

filter.Add(PredicateExtendedFactory.FuzzyLike( CustomerFieldIndex.Kundenbezeichnung, ComparisonOperator.Equal, constrains.Customer.Name));

This "PredicateExtendedFactory" class is just an amended copy of the PredicateFactory class.

Unlike the PrecidateFactory, the PredicateExtendedFactory doesnt call the CompareValue method of SD.LLBLGen.Pro.ORMSupportClasses.FieldCompareValuePredicate but a customized method called "FuzzyLike" in my "FieldFuzzyLikePredicate", an amended copy of FieldCompareValuePredicate.

You just have to amend the "ToQueryText()" method here:

queryText.AppendFormat( "(DIFFERENCE(dbo.ReplaceAllSpecialChars({0}),dbo.ReplaceAllSpecialChars({1})) > 3)", base.DatabaseSpecificCreator.CreateFieldName(_field, _persistenceInfo, _field.Name, _objectAlias, ref uniqueMarker, inHavingClause), parameter.ParameterName);

The query itself is a selfmade algorithm which does a very simple fuzzy search using the TSQL DIFFERENCE command and a stored procedure. The "3" is btw the value which adjusts the level of accuracy. See the TSQL reference for details.

Florian

Cool stuff Florian!

I can't seem to find any of this code you guys talk about. Can somebody post the complete derived class to handle Soundex queries ... Thank-you.

Really puzzled.

Joerg wrote:

I can't seem to find any of this code you guys talk about. Can somebody post the complete derived class to handle Soundex queries ... Thank-you.

Really puzzled.

The code of the 'Predicate' class and for example the FieldCompareValuePredicate class is in the Runtimelibrary sourcecode, available to customers.

Ah right ... there it is in Program Files...