Hi I'm querying tow tables based on 2 nvarchar (4) columns , the problem that some rows are not joined correctly due to a case sensitivity in the tow tables .

How can I force the join regardless the case sensitivity ?

                var q = qf.AccountBalance
                    .From(QueryTarget.InnerJoin(qf.Companyinfo).On(AccountBalanceFields.Dataareaid == CompanyinfoFields.Dataareaid))
                    .WithPath(companyPrefetch)
                    .Where(AccountBalanceFields.ReferenceDate.Equal(dateIn.Date));
                
                return adapter.FetchQuery(q).OfType<AccountBalanceEntity>(); 

Regards

You can use pre-defined string functions like ToLower()

Thank you

Cant it be defined in the Inner block ?

.From(QueryTarget.InnerJoin(qf.Companyinfo).On(AccountBalanceFields.Dataareaid.ToLower() == CompanyinfoFields.Dataareaid.ToLower()))  ?

It seems not ?

What does it produce?

It does not compile

.From(QueryTarget.InnerJoin(qf.Companyinfo).On(AccountBalanceFields.Dataareaid.ToLower() == CompanyinfoFields.Dataareaid.ToLower()))

Error 1 The best overloaded method match for 'SD.LLBLGen.Pro.QuerySpec.InnerOuterJoin.On(SD.LLBLGen.Pro.ORMSupportClasses.IPredicate)' has some invalid arguments

Error 2 Argument 1: cannot convert from 'bool' to 'SD.LLBLGen.Pro.ORMSupportClasses.IPredicate

Regards

Which LLBLGen Pro runtime library version/build no. are you using?

I'm using 4.1 I've tried also this code

.From(QueryTarget.InnerJoin(qf.Companyinfo).On(AccountBalanceFields.Dataareaid.ToLower().Equal(CompanyinfoFields.Dataareaid.ToLower())))

The issue remains for the upper/lower case

Regards

in addition to the last comment the ver 4.1 FINAL

Try something like:

EntityField2 field1 = OrderFields.CustomerId.SetExpression(OrderFields.CustomerId.ToLower());
EntityField2 field2 = CustomerFields.CustomerId.SetExpression(CustomerFields.CustomerId.ToLower());

var qf1 = new QueryFactory();
var q1 = qf1.Order
   .From(QueryTarget.InnerJoin(qf1.Customer).On(field1 == field2))
   .Where(CustomerFields.Country == "UK");

var list =  adapter.FetchQuery(q1).OfType<OrderEntity>(); 

The above produced:

Query: SELECT DISTINCT [Northwind].[dbo].[Orders].[CustomerID] AS [CustomerId], [Northwind].[dbo].[Orders].[EmployeeID] AS [EmployeeId], [Northwind].[dbo].[Orders].[Freight], [Northwind].[dbo].[Orders].[OrderDate], [Northwind].[dbo].[Orders].[OrderID] AS [OrderId], [Northwind].[dbo].[Orders].[RequiredDate], [Northwind].[dbo].[Orders].[ShipAddress], [Northwind].[dbo].[Orders].[ShipCity], [Northwind].[dbo].[Orders].[ShipCountry], [Northwind].[dbo].[Orders].[ShipName], [Northwind].[dbo].[Orders].[ShippedDate], [Northwind].[dbo].[Orders].[ShipPostalCode], [Northwind].[dbo].[Orders].[ShipRegion], [Northwind].[dbo].[Orders].[ShipVia] FROM ( [Northwind].[dbo].[Orders]  INNER JOIN [Northwind].[dbo].[Customers]  ON  LOWER([Northwind].[dbo].[Orders].[CustomerID]) = LOWER([Northwind].[dbo].[Customers].[CustomerID])) WHERE ( ( [Northwind].[dbo].[Customers].[Country] = @p1))
    Parameter: @p1 : String. Length: 15. Precision: 0. Scale: 0. Direction: Input. Value: "UK".

partially it is solved , the Sql query is now correctly generated .

but when using .WithPath(IPrefetchPathElement) to fetch data from the master table , it fails to composite the returning objects when Lower/Upper differences found for key column .

companyPrefetch = AccountEntity.PrefetchPathCompanyinfos;

Set EntityFieldCore.CaseSensitiveStringHashCodes to false (it's true by default). If it's set to false, the engine will produce case-insensitive hashcodes, so the case doesn't matter.

It's a static property, so you have to set it only once. You can do that in the config file of your application as well, see the reference manual's documentation on that property.

(edit)

var q = qf.Customer
                .From(QueryTarget.InnerJoin(qf.Order).On(CustomerFields.CustomerId.ToLower().Equal(OrderFields.CustomerId.ToLower())));

Works fine though (both ToLower() calls are honored. You can't use '==' in this case as the operator '==' is overloaded for fields but not for FunctionMappingExpression instances (the object returned by ToLower()). Equal() solves that

Thank you both I did what Otis said .. it works fine