This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
I'm having trouble getting Like to work properly with LightSpeed. I'm using c# and using LikeOperator.LikeString, however it also fails in VB using the Like keyword. For example, using LINQPad with the following VB expression:
from p in People where p.FirstName Like "B%" select p.FirstName ends up generating the following SQL: SELECT Person.FirstName AS [Person.FirstName] FROM Community.Person WHERE Person.FirstName LIKE 'B\\%' ESCAPE '\' The SQL doesn't work, it returns zero results. If I remove the double slashes (\\) from the SQL and run it, then it works as it should. Why is it adding these double slashes? |
|
|
It isn't. The double-slash is an artefact of how the logger prints parameters. The actual LIKE string being sent to the database is 'B\%' (you can verify this by setting VerboseLogging = true and/or attaching a trace tool such as SQL Server Profiler). The reason it is sending 'B\%' instead of 'B%' is to escape the percent sign. The VB Like operator's wildcard is *, not %. Therefore Like "B%" should match only the string "B%", not all strings beginning with B. Use Like "B*" to get all strings beginning with B: you will see that LightSpeed translates this to a LIKE 'B%' SQL clause. |
|
|
Yes, you're right. Thanks for that :) |
|
|
Hi Ivan,
Thanks for the link to this thread, I have already read it and tried the following: query.Where(m => m.Addresses.Address1.ToLower().Contains("111*blah")) but i get the following SQL: AND LOWER(t1.Address1) LIKE '%140*sussex%' ESCAPE '\')
This is still not what i want, I need AND LOWER(t1.Address1) LIKE '%140%sussex%' ESCAPE '\')
Am I missing something? |
|
|
The generated SQL is correct. String.Contains does not have wildcard semantics: it tests if one string is a substring of another. If you need wildcards, use Like. Your desired SQL would be represented by Like "*140*sussex*". |
|
|
ok well the only other way in which i could figure out how to do this via LAMBDA is the following: query.Where(m => SqlMethods.Like(m.Addresses.Suburb.ToLower(), "%this%is%annoying%"));
Is there a better way of doing this? |
|
|
doesn't look like LS support this anyways. |
|
|
Unfortunately, there is no convenient way of doing this in C#. Visual Basic has a Like operator as shown in giddyuphorsey's first message, but there's no equivalent in C#. The only way to do this in LINQ is to use the LikeOperator.LikeString method from the Microsoft.VisualBasic assembly: query.Where(m => LikeOperator.LikeString(m.Addresses.Suburb.ToLower(), "*this*is*still*annoying*", CompareMethod.Text)); Unfortunately, this is a LINQ issue rather than a LightSpeed issue -- because the query is language-integrated, it is restricted to what the language can express, and C# does not have a good way of expressing wildcard matches. The same query can be expressed a bit more naturally in the LightSpeed core API: uow.Find<M>(Entity.Attribute("Addresses.Suburb").Lower().Like("%this%is%less%annoying%")); |
|
|
Thanks for your help Ivan, I think i'm going to go with the first option. I don't like the Entity.Attribute concept as your tightly coupling your data layer to the db/model which to my mind is the whole point of using the light speed diagram. For example if somone changes the name of a feild for example I only want to update the diagram not sift through the code to find other reference points. I think if you using the Entity.Attribute you might as well be writting SQL in your code, which incodently would have been allot easier than this little learning curve. Thanks Again, Josh |
|