0

Hi,

If I run the in my database :

SELECT *
FROM 
    Ads AS a 
INNER JOIN AdCategories AS ac 
    on (ac.Id = a.CategoryId) 
INNER JOIN Location AS l 
    on (l.Id = a.UserLocationId) 
WHERE 
    (l.LevelOrder LIKE '2/3/5/%') AND 
    (a.Title LIKE '%' OR a.Description LIKE '%') AND 
    a.InactivatedDate IS NULL AND 
    a.PublishedDate IS NOT NULL AND 
    a.EndDate >= '2012-02-04 17:01:37' AND 
    a.EndDate >= '2011-11-04 16:55:26' AND 
    ac.LevelOrder Like '7/15/33/36_%'

This will return 2 rows but when running the same command with Entity Framework

context.CreateQuery<Ad>(sqlCommand, parameters.ToArray()); 
output = ads.ToList(); 

I get nothing?

This is how the SQL statement looks like :

SELECT VALUE a 
FROM 
    Ads AS a 
INNER JOIN AdCategories AS ac 
    on (ac.Id = a.CategoryId) 
INNER JOIN Location AS l 
    on (l.Id = a.UserLocationId) 
WHERE 
    (l.LevelOrder LIKE @locationLevelOrder0) AND 
    (a.Title LIKE @searchS OR a.Description LIKE @searchS) AND 
    a.InactivatedDate IS NULL AND 
    a.PublishedDate IS NOT NULL AND 
    a.EndDate >= @CurrentTime AND 
    a.EndDate >= @fetchAdsTo AND 
    ac.LevelOrder Like @categoryLevelOrder

The parameters looks like this :

  1. LocationLevelOrder0 = 2/3/5/%
  2. searchS = %
  3. CurrentTime = {2012-02-04 17:31:34}
  4. fetchAdsTo = {2011-11-04 17:31:34}
  5. categoryLevelOrder = 7/15/33/36/_%

Why do I not get any results with the Entity Framework while I do in SQL manager? And what does the select value a stand for? If I remove it there will be thrown an exception?

0

1 Answer 1

1

In first your query you compare LevelOrder with '7/15/33/36_%' and in the second one with 7/15/33/36/_% that is a different string. Is this a transcription mistake or is the issue?

Sign up to request clarification or add additional context in comments.

3 Comments

Thanks, you are correct, If I run 7/15/33/36/_% I will not get any hits anymore. The string I try to mach is "7/15/33/36/X" where X my be any string, maby "7/15/33/36/69/78/99" or "7/15/33/36/100/152/158". Do the _& have a special meaning in SQL? Else It should only be to remove the _?
Yes, in SQL _ is a wildcard for only one character in LIKE operator.
Okay, so with this 7/15/33/36/_% I say match 7/15/33/36/ + one wildcard at least but also grant strings with more wildcards? I will try to exclude the _ and see if that works in the long run. Thanks for helping!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.