Devart Forums

johnb

I recently upgraded from 6.30.165 to 6.50.228 and started getting some strange behaviour. As a result i have had to revert back to 6.30.165 to satbise function and have things work as expected.

The first issue was on subsequent calls the query was failing to return a result with FirstOrDefault even though there is something in the result set and you can get the value using a .ToList()[0].

My query was below. IF i just executed the the query and put a countr on it would return 1. If is did a tolist i got one record. Just the firstordefault failed and return null.

Code: Select all

                    var _jobAEsandaStatesClaimant1 =
                        (from JobAEsandaState in _AIMSContext.JobAEsandaStates
                         where JobAEsandaState.Statekey == _claimant1State
                         select JobAEsandaState).FirstOrDefault();

I then reverted back to 6.50.214 and had another strange issue in teh the query result appeard to be being cached and was getting the same result as the first call even though the query parameters had changed.

My query was below. The same jobid was always returned the one that was first called even though the cleintref was different

Code: Select all

                    var _result =
                        from Job in _AIMSContext.Jobs
                        where Job.ClientRef1.IndexOf(ClientRef, StringComparison.OrdinalIgnoreCase) >= 0
                        || Job.ClientRef2.IndexOf(ClientRef, StringComparison.OrdinalIgnoreCase) >= 0
                        || Job.ClientRef3.IndexOf(ClientRef, StringComparison.OrdinalIgnoreCase) >= 0
                        select Job.JobId;

All very strange but please it is now working as expected in 6.30.165. It is very strange and expect if these linq queries werent working then there would be others.

Hope this helps. You help would be much appreaciated.

John

johnb

Hi StanislavK

Thanks for that it generated the corrcet sqL with a LIKE.

Quick question, how come this wasnt implemented using Contains which is a bit less obscure and consistent with StartWith etc?

Thanks again

John

johnb

Hi StanislavK

I tried the code you suggested but it dosent generate a like statement as expected. The SQl generate is

Code: Select all

WHERE (INSTR(t10.org_name, :p0)-1)  0  ORDER BY

the line I used is

Code: Select all

            _JobModelViewCollection = _JobModelViewCollection.Where(Function(j) j.Client.IndexOf(ClientName, StringComparison.OrdinalIgnoreCase))

Am I doing soeming wrong or is the expected behaviuor because the query returns nothing which is not the case.

JOhn

johnb

Hi StanislavK
Thanks for the reply and look forward to the update as my cleint was asking why when the do a search it was case sensitive.

Thanks again

John

johnb

Thats very unfortunate as a ToLower does a whole table scan and the table I am using is a big table. I am allowing the user to prefilter based up[on text enetered, the enetered value is not case sensistive and it is data like names - "mark", "Mark" & "MARK" should always be returned regardless of what is enetered - i.e sql query which has LIKE "%mArk%" should return all "marks" regardless of case.

When I checked the generated sql from a Contains it puts the word BINARY in the sql. It did the same with StartWith the same thing until I said case insensistve using the second option then the binary disappered. Is it not possible to so the same.

All that I am after is the server not to treat it as a Binary search (remove the BINARY keyword) to apply the sql corrcetly to get the desired result as you would when you execute a query normally through a query interface.

johnb

Hi AndreyR
You mentioned that in a previous post that implemented contains to allow case insensitive searches. I am unsure i can enable this as it always seems to select BINARY in ther SQL. Is there a trick to enabling this. StartsWith work well when you put the string comparision to caseinsenstive.

Thanks

John

Devart Forums

Search found 6 matches

Issue with 6.50.x LINQ queries