How to use parameters in Linq FromSQL Query

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");

Ivan Stoev · Accepted Answer · 2019-02-27 07:09:58Z

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}

Collectives™ on Stack Overflow

How to use parameters in Linq FromSQL Query

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related