How to use parameters in Linq FromSQL Query

asp.net-core c# entity-framework-core

Question

I am trying to get a list from Locations table and exclude the Locations already assigned to a user. I am trying to using Linq FromSQL. If I hard code the user.Id parameter the query works, when I use the user.Id variable it does not work. Query returns all records. When I run the debugger, the user.Id variable is correct. Here is my code...

ApplicationUser user = await _userManager.FindByNameAsync(Id);

var unassignedLocations = await _context.Location
       .FromSql($"SELECT * FROM Locations WHERE Id NOT IN (SELECT LocationId FROM UserLocations WHERE UserID='{user.Id}')")
       .OrderBy(l => l.Name)
       .ToListAsync();

Locations = new SelectList(unassignedLocations, "Id", "Name");
1
2
2/27/2019 7:30:10 AM

Accepted Answer

The problem are the enclosing quotes around the value

UserID='{user.Id}'

When using FromSql method overload with interpolated SQL string, rather than embedding the value inside the SQL string (which would allow SQL injection attacks), EF Core will bind a parameter for every placeholder inside the string - in this case, single parameter with value = user.Id.

Hence you have to access the value directly w/o enclosing it with quotes like constant:

WHERE UserID={user.Id}
3
2/27/2019 7:09:58 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow