I feel like a LINQ based monkey boy at the moment.
I am building a library of extensions on top of LINQ using Keith Farmer’s TableView class to create a SecureDataContext. In Keith’s example he uses a simple GetQuery function that does a simple select case switch to return a filter on each table.
This is fine for a small database, but my database has well over 100 tables and the switch statement would be terrible. In addition, most of my security is dynamic, stored in tables, and specific to each user. To explain: All customers have a parent table Partners, and each partner entry has a parent Company (based on legal operating entities).
Our security allows me to say things like "User A has only access to Customer #1", and "User B has access to Customer #2, 3 and 4, plus any customer that is Partner #7". We even have "User C has access to Company #6" – which means all partners where CompanyID=6 and all the customers below this as well.
I am also adding the capability in this release to secure on fields other than primary keys. I might have a salesman who I want to restrict to seeing his own customers, so I might say "User D has access to any customer where field SalesPerson=’D’. "
In addition to direct security on each table, I have a structure metadata that defines how the security relations work. For example, the Bills table is a child of the Customers table. In the metadata I specify that access to Bills is controlled by it’s parent table Customers. This extends down the relationship hierarchy – so if I want all the CallRecords (this is a telco app) for User A, I have to create the filter:
from r in db.CallRecords where r.BillItem.Bill.Customer.CustomerID ==1 select r;
To do this meant writing a predicate builder. I abstracted this through an interface IQueryBuilder in SecureDataContext, so that I could write a TestQueryBuilder instance, as well as my live version that runs off the database.
This has to iterate from the current table both up and down the structure, to find access permission for the current user, either in the entity/table, or in a related one.
So far so good and after a few bugs I had a working secure datacontext whenever I did a query up the hierarchy. The problems started when I had to go down the tree.
Example: user A above only has access to Customer #1 – nothing else. So if I were to need to build a list of Partners for that customer, for a listbox, what happens? Well what should happen is that I’ve said in my security structure that the child entity "Customers" in partner can give access upward. In this case I want to be able to give user A access to just it’s parent partner. In SQL this is an easy query to write:
SELECT * FROM Partners WHERE PartnerID IN
(SELECT DISTINCT PartnerID FROM Customers WHERE CustomerID =1)
I did try to write this as a LINQ expression tree but it didn’t work – the problem here is that the sub-select isn’t a function of Partner, and my expression builder is only able to create an Expression<Func<Partner,bool>> lambda function. In LINQ the expression looks like this:
from p in Partners where p.Customers.Select( c => c.PartnerID).Contains(p.PartnerID);
This is an Expression<Func<Partner,bool><Func<Customer,int>>> and won’t work.
I then tried to think of an alternative approach, and thought that this might work:
from p in Partners where p.Customers.Count>0 select p;
In this case I was relying on p.Customers to be filtered automatically through my secure datacontext, thus automatically expanding my query:
from p in Partners where p.Customers.Where( c=> c.CustomerID==1).Count>0 select p;
Sadly the LINQ to SQL expression evaluator does not do this and instead does the sub-select unfiltered.
Having failed with two types of expression, I’m not sure I can figure out a way of doing this which is purely expression-based. So instead I have decided to ‘cheat’ and perform the sub-expression inside the query builder first, getting it to return a value list. I can then return an expression on this value list. Thus for user A:
select p from db.Partners select p;
The query builder finds that Partner has access based on Customers child list. It creates an expression:
var list = from c in db.Customers where c.CustomerID==1 select c.PartnerID)
This is then returned, lets say that customerID=1 has PartnerID=2, so the actual query returned is:
select p from db.Partners where p.PartnerID==2 select p;
Nasty but for the moment the only way I can think of.