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

4

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}

edited Feb 27, 2019 at 7:09

answered Feb 24, 2019 at 17:20

Ivan Stoev

208k17 gold badges356 silver badges376 bronze badges

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

1 Comment

Brian Over a year ago

That did it! Thank you Ivan.

Collectives™ on Stack Overflow

How to use parameters in Linq FromSQL Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related