I have 3 tables: the User table, the Records table and the UserRecords table
.
The columns are so:
UserTable
UserID | OtherUserDataFields
RecordsTable
RecordID | OtherRecordDataFields
UserRecords
UserID | RecordID
The UserRecords table tells me which users have authorization on which record. I have a function that updates the RecordsTable by receiving 2 paramaters: a record TheRecord (which contains a RecordID field) and a UserID.
I'm writing a query in the data context MyDC to fetch the record with the RecordID supplied in the parameter and test if the user is authorized on that record like this:
var RecordToUpdate = (
from r in MyDC.RecordsTable
from u in MyDC.UserRecords
where r.RecordID == TheRecord.RecordID && TheRecord.RecordID == u.RecordID
where u.UserID == TheUserID
select r).SingleOrDefault();
Will this ensure me that only records the user is authorized will be fetched? I want to avoid cases where users maliciously send a record they're not authorized on and make changes to these unauthorized records.
Thanks for your advice.
I agree with Anand, you will need a linq query:
var filterUserRecord = from u in MyDC.UserRecords
where u.UserID == TheUserID
select u;
var q1 = from r in MyDC.RecordsTable
where r.RecordID = TheRecordID
where filterUserRecord.Any(f => f.RecordID == r.RecordID)
select r;
This will be converted to the SQL query like the following:
SELECT * FROM RecordsTable rt WHERE rt.RecordID = TheRecordID AND EXISTS
(SELECT recordId FROM UserRecords ur WHERE ur.userId = TheUserID AND ur.recordID = rt.recordID)
Note that those are IQueryable<T>
s and linq queries on it will produce another IQueryable<T>
which will contain expressions to be wholly translated to SQL (or whatever the backend is) instead of naively evaluating it at client side.
Well I think your problem could be solved by subquery
sql:
select * from RecordsTable where recordId in
(select recordId from UserRecords where userId = @someUserId)
It could be represented in Linq as following
var filterUserRecord = from u in MyDC.UserRecords
where u.UserID == TheUserID
select u
var q1 = from r in MyDC.RecordsTable
where filterUserRecord.Any(f => f.RecordID == r.RecordID)
Details for subquery in Linq - Read from here
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With