Hi,

I am using llblgen pro version 5.3.3 and connecting to an ms access database (2010), and getting an unexpected result when trying to do a query with 2 fields of the same table concatenated, when looking for a record where 1 of the fields is empty it fails, while when both fields are not empty it works.

I have created a sample console project, and sample database with the minimum viable stuff to test the issue out https://www.dropbox.com/s/6ghr5br7jzwjipj/TestConcatenationIssue.7z?dl=0 the database needs to be placed in c: or the connection string needs to be changed, but besides that it should work out of the gate.

Either way the code is this:

            var completeAddress1 = "Test Of the address name";
            var completeAddress2 = "Address with Door NumberN535";

            var qf = new QueryFactory();
            var q1 = qf.Location.Where((LocationFields.Address + LocationFields.Door).Equal(completeAddress1));
            var q2 = qf.Location.Where((LocationFields.Address + LocationFields.Door).Equal(completeAddress2));

            var fetchedLocation1 = new EntityCollection<LocationEntity>();
            var fetchedLocation2 = new EntityCollection<LocationEntity>();

            using (var adapter = new DataAccessAdapter())
            {
                adapter.FetchQuery(q1, fetchedLocation1);
                adapter.FetchQuery(q2, fetchedLocation2);
            }

            Console.WriteLine("Address without Door retrieved " + fetchedLocation1.Count + " records");
            Console.WriteLine("Address with Door retrieved " + fetchedLocation2.Count + " records");
            Console.WriteLine("Press Any key to close");
            Console.ReadLine();

the generated query from q1 is this:

{SELECT
    With projection: 
        Field: LocationEntity.Address
        Field: LocationEntity.Door
        Field: LocationEntity.Id

WHERE
    PredicateExpression:
        PredicateExpression:
            Predicate:
                FieldCompareValuePredicate:
                    Field:
                        Field: .ExF
                            Expression: (Expression)
                                Left operand:
                                    Field: LocationEntity.Address
                                Operator: Add
                                Right operand:
                                    Field: LocationEntity.Door
                    Equal
                        Constant: Test Of the address name

Additional information:
    Offset: 0
    Alias: ''
    CacheResultset: False
}

and the generated query from q2 is exactly the same except for the constant line which is "Constant: Address with Door NumberN535".

Am I doing the joining of the fields to query wrong, and if so what is the correct way to do it?

Thank you.

Please attach the files separately to your message (paper clip button).

I guess this comes down to Null propagation using "+". Text + NULL => NULL

https://support.office.com/en-us/article/combine-text-values-by-using-an-expression-d1653b60-dd60-4a05-9747-6b31ba4c4eed

The above article suggest the usage of "&" instead of "+" to concatenate texts and avoid the Null propagation.

Walaa wrote:

Please attach the files separately to your message (paper clip button).

I guess this comes down to Null propagation using "+". Text + NULL => NULL

https://support.office.com/en-us/article/combine-text-values-by-using-an-expression-d1653b60-dd60-4a05-9747-6b31ba4c4eed

The above article suggest the usage of "&" instead of "+" to concatenate texts and avoid the Null propagation.

Have attached the 7zip file containing the test project in this post (had to remove the generated projects, and the packages from nuget, but included the .llblgenproj).

Hmm I see, hadn't considered that it would be ms access concatenating the strings instead of c#, since in c# a null gets replaced by "", it doesn't help that doing the query with linq (with LinqMetaData) things work as expected despite the query being exactly the same (on the c# side, the sql is probably different ofc).

var q1 = qf.Location.Where((LocationFields.Address + LocationFields.Door).Equal(completeAddress1)); // <-- This doesn't.

var linqFetchedLocation1 = metaData.Location.Where(x => (x.Address + x.Door).Equals(completeAddress1)); // <-- This works.

Is there some way to make it work within queryspec, without ofc creating a view with the fields already properly concatenated, asking purely because I am new to LLBLGen and would like to know if its possible, or if this is one of the limitations of queryspec in relation to linq.

Thank you for the help.

EDITED: While searching more information on QuerySpec I found this post from otis https://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=24109&StartAtMessage=0&#136925 and if this is indeed the case the QuerySpec should have returned the same results, or is this an exception to the rule?

We are examining your test project to reproduce your behavior...

The '+' in linq will be replaced with a string.Concat() call (or at least an implicit op call) by the compiler and the call to the concat method is present in the expression tree. This method call is then converted in a function mapping as there's a string.concat() function mapping for access defined. This is why you see '&' in the SQL for linq.

The '+' in queryspec is picked up as an addition operator between fields and used as such, it doesn't get compiler help here to convert the + into a string.Concat. If you want to have a string concat in queryspec you have to specify the function as such, using the StringFunctions.Concat() method:

var q1 = qf.Location.Where(StringFunctions.Concat(LocationFields.Address,  LocationFields.Door).Equal(completeAddress1));

Queryspec doesn't get help from the compiler here as linq does. The '+' operator overload is one defined on the entity field, and will result in an 'addition' operator, so you'll see a '+' operator in the SQL query as it doesn't know it has to be converted to a string.Concat. In Queryspec if you want something to do X you have to specify it, it's more explicit like that, but also gives more clarity what'll be done

I see, hadn't even considered looking up the manual for concat had only searched for concatenating and concatenation, as expected StringFunctions.Concat does indeed work the same as the linq experession.

Anyway thanks for the clarification on why QuerySpec here is working differently than Linq.