Expressions Expressed!

Since my last post on Expressions I have finally managed to write the code to handle child-based security, where access to one or more of a child table gives access to a parent table.

I eventually realised that I could write my iterative code to drop down the relationship path e.g. Company.Partners.Customers and then build a .Count() sub-select for each one.

So for access to customer #1 I say:

inner query:  .Customers.Where(p1 => p1.CustomerID==1)

outer query 1: .Partners.Where( p2 => p2.Customers.Where(p1=> p1.CustomerID==1))

outer query 2: .Companies.Where( p3 => p3.Partners.Where(
                            p2 => p2.Customers.Where(p1=> p1.CustomerID==1)))

Writing this code required an interative function but it worked. My only kludge in this code was when I had to obtain the .Select() method (by using .First() !).

Music for Coding To

There ought to be a new album compilation launched, "Music for Coding To"

After all, there is "Music for Babies" (a Classic FM album), and others like driving, relaxing, etc. Of course everyone has their own preferences – some people like loud, fast music to work against, and others like gentle slow tunes.

I prefer both at different times – but currently I’m listening to some Ludovico Einaudi – a terrific find if you like classical pianoforte. I also listen to Groove Salad a lot as I use WinAmp, and Massive Attack, Air or even Jon and Vangelis.

Any other suggestions and comments welcomed.

Expressions Expressions Expressions

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’. "

Structure

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.

Problem

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.

Current Approach

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)
                    .Distinct().ToList();

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.

New Blog

Just what the world needs.. yet another blog. In fact about the third I’ve set up as I could not be bothered to update the old ones (Blogger.com is just too awkward).

However I like the idea of using Live Writer – no more accidentally changing page and losing the contents you’ve been working on. Let’s see how it goes!