0

I have a working query that returns 140 rows from the db:

string tag = "New York";

string sql = "select   " +
"    T.Id       TagId, " +
"    PM.Id      MachineId, " +
$"    '{tag}'     TagSiteName, " +
"    T.Name     TagCpeMachineLine, " +
"    PM.Plant, " +
"    PM.Line, " +
"    PM.Type " +
"from Tags T " +
"inner join PaperMachines PM " +
"    ON PM.Id = T.PaperMachineId " +
$"WHERE T.Name LIKE ( '{tag}' ) ";

var result = db.Database.SqlQuery<TagsMachines>(sql);

When I try to parameterize it, I get 'Enumeration yielded no results' from the result.

string tag = "New York";

string sql = "select   " +
"    T.Id       TagId, " +
"    PM.Id      MachineId, " +
"    '@tag'     TagSiteName, " +
"    T.Name     TagCpeMachineLine, " +
"    PM.Plant, " +
"    PM.Line, " +
"    PM.Type " +
"from Tags T " +
"inner join PaperMachines PM " +
"    ON PM.Id = T.PaperMachineId " +
"WHERE T.Name LIKE ( '@tag' ) ";

var result = db.Database.SqlQuery<TagsMachines>(sql, new SqlParameter("@tag", tag));

I'm not sure what's going on here. Any ideas?

dave111
  • 173
  • 1
  • 2
  • 13
  • 1
    Remove the quotes around all instances of the parameter. It should just be `@tag`, never `'@tag'` unless you want the verbatim string value `'@tag'` used/returned. – Igor Mar 31 '20 at 18:53

1 Answers1

1

Remove the @tag from top of your Query and at the bottom of your Query make it T.Name = @tag or T.Name like ('%' + @tag +'%') if you want like contains.
Finally at the end of the result line add a .FirstOrDefault().

AbhiN
  • 642
  • 4
  • 18
Lynyrd
  • 76
  • 6
  • I figured it likely had to do with the single quotes. I did remove them around the tag parameter, but the T.Name like ('%' + @tag +'%') did not work. I looked at another thread: https://stackoverflow.com/questions/2589283/how-to-get-like-clause-to-work-in-ado-net-and-sql-server and added the wildcard % in the actual sql parameter value: var result2 = db.Database.SqlQuery(sql2, new SqlParameter("@tag", $"{tag}%")).ToList(); – dave111 Mar 31 '20 at 20:40
  • Yeah should have been double quotes instead of single like "%" + @tag + "%" or you could instead omit the entire SqlParameter off and instead Use a filter after your query something like .Where(i=>TagSiteName.Contains(tag)).ToList() now of course this would most likely have the performance hit of returning too much to be filtered afterwards – Lynyrd Mar 31 '20 at 20:50