CODEWITHSUNDEEP
c#asp.net-coreentity-framework-core
Brian
Brian

Reputation: 33

How to use parameters in Linq FromSQL Query

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

Upvotes: 3

Views: 897

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205629

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}

Upvotes: 4

Related Questions