Hello,

I am using LLBLGen 2.6 and Sql Server 2008 R2, .Net 3.5.

I am trying to change the collation on the queries sent to the database. The collation on the database is SQL_Latin1_General_CP1_CI_AS (accent sensitive).

Is there any way to write a query with LLBLGen that will translate in SQL in

SELECT * 
FROM Table
WHERE Column like 'rené' COLLATE SQL_Latin1_General_CP1_CI_AI
--accent insensitive

(COLLATE SQL_Latin1_General_CP1_CI_AI = Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page 1252 for non-Unicode Data)

Regards,

Claudia

See the following post.

http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=16531&HighLight=1

Matt

I tried to do what the mentioned post said. Here is the code:

 result.Add(
    new FieldLikePredicate(
               m_Field.SetExpression(new DbFunctionCall("COLLATE  SQL_Latin1_General_CP1_CI_AI",
                                                                 new object[] {m_Field})), null, value));

where m_Field is a IEntityField2 and value is the value to compare with.

This code gives an StackOverflowException: An unhandled exception of type 'System.StackOverflowException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll

Do you have any idea of what went wrong?

Do you have a stack trace?

Not really. It is a StackOverFlow exception. {Cannot evaluate expression because the current thread is in a stack overflow state.}

The crash appears in DataAccessAdapter at this line:

likePredicate.PersistenceInfo = GetFieldPersistenceInfo((IEntityField2)likePredicate.FieldCore);

How do you create m_Field? Can you re-produce this if you use an EntityField like CustomerFields.CustomerName?

With a non generic field I couldn't reproduce the error.

this is the code:

predicateExpression.Add(
                    new FieldLikePredicate(
             CustomerFields.CustomerName.SetExpression(
             new DbFunctionCall("COLLATE SQL_Latin1_General_CP1_CI_AI",new object[] 
                                            {CustomerFields.CustomerName}))
                    , null, value));

But the following select is generated:

    SELECT *
    FROM Customer
        WHERE ( ( (COLLATE SQL_Latin1_General_CP1_CI_AI CustomerName LIKE '%rene%')))

instead of

SELECT *
FROM Customer
WHERE CustomerName like '%rene%' COLLATE SQL_Latin1_General_CP1_CI_AI

It seems that that the framework can't find the persistence info of the field. Could you please post a code snippet of how you create the "m_Field" object.

The m_field is initialized with the folowing code:

//typedViewName is the View in LLBLGen - base on a Database view
//fieldName = the name of the field in the View

m_Field = EntityFieldFactory.Create(typedViewName, fieldName);

I think you can just cast the field into EntityField2 as follows:

var m_Field = (EntityField2)EntityFieldFactory.Create(typedViewName, fieldName);

If this doesn't work please check this post: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=4594&StartAtMessage=0&#70730

I will try that. Thanks.

But coming back to my initial problem. Even if I try with an explicit field, I have the following problem:

This is the code that I wrote:

predicateExpression.Add(
                    new FieldLikePredicate(
             CustomerFields.CustomerName.SetExpression(
             new DbFunctionCall("COLLATE SQL_Latin1_General_CP1_CI_AI",new object[]
                                            {CustomerFields.CustomerName}))
                    , null, value));

But the following select is generated:

SELECT *
    FROM Customer
        WHERE ( ( (COLLATE SQL_Latin1_General_CP1_CI_AI CustomerName LIKE '%rene%')))

instead of

SELECT *
FROM Customer
WHERE CustomerName like '%rene%' COLLATE SQL_Latin1_General_CP1_CI_AI

Did I write something wrong?

Thanks

I think the following SQL should work for you

SELECT *
FROM Customer
WHERE CustomerName COLLATE SQL_Latin1_General_CP1_CI_AI like '%rene%'

And here is the code to generate it:

predicateExpression.Add(
                    new FieldLikePredicate(
             CustomerFields.CustomerName.SetExpression(
             new DbFunctionCall("{0} COLLATE SQL_Latin1_General_CP1_CI_AI",new object[]
                                            {CustomerFields.CustomerName})) , null, value));

Please pay attention to the {0} token.

Walaa wrote:

I think the following SQL should work for you

SELECT *
FROM Customer
WHERE CustomerName COLLATE SQL_Latin1_General_CP1_CI_AI like '%rene%'

And here is the code to generate it:

predicateExpression.Add(
                    new FieldLikePredicate(
             CustomerFields.CustomerName.SetExpression(
             new DbFunctionCall("{0} COLLATE SQL_Latin1_General_CP1_CI_AI",new object[]
                                            {CustomerFields.CustomerName})) , null, value));

Please pay attention to the {0} token.

Thanks,

The last example works. I also managed to solve the more generic solution that gave me the StackOverFlowException:

EntityField2 fieldToApplyExpressionOn = (EntityField2)EntityFieldFactory.Create(m_Field.ContainingObjectName, m_Field.Name);

predicateExpression.Add(
    new FieldLikePredicate(
             m_Field.SetExpression(new DbFunctionCall("{0) COLLATE SQL_Latin1_General_CP1_CI_AI",
                                                                 new object[] {fieldToApplyExpressionOn })), null, value));