DataContext. Is there a single method where I can add an extra WHERE?

Question

So basically I have an application that works with just one user, but I'd like to make it multi-user. This would involve me logging a user in, and keeping the user ID in session, plus adding a user id column to my database tables. no biggy.

I'd like to change my dbml, or use it's partial, so that any query I throw through it also gets

.WHERE(user=>user.id=MYPARAM)

added to it, to return just records for the logged in user.

Is there a quick win to be had? A single place where I can specify this in the .dbml?

Answer 1

Hi,

I would perhaps create a lambda expression dynamically.

The Where clause takes an object of type Expression>

You can then create an expression using the following.

Expression<Func<User,bool>> func = null;

Then

ParameterExpression xParam = Expression.Parameter(typeof(User), "u");

You would then create an expression for u.id = "test" by using a binary expression.

BinaryExpression uidTest = Expression.Equal(Expression.Property(xParam, "id"),
                                                               Expression.Constant("test");

and then attaching it to the Expression as follows:

func = Expression.Lambda<Func<User, bool>>(uidTest, xParam)

In effect this is building a lambda expression u=> u.id = "test"

The func object can then be used in the .Where as follows: .Where(func)

Obviously, you can dynamically build this to any criteria you need at any time in your application.

Answer 2

I am not sure if there is something like that.

If there isn't, you can expose already filtered properties for all the tables in the partial class. You would then receive the user id as a parameter to the data context.

You can combine that with a dynamic expression so that those properties are as simple as possible to define.

Answer 3

If you are still having trouble implementing this functionality using LINQ to SQL, then you may want to solve the problem at the database level using row level security. Essentially you could wrap each table with a view which implements a dynamic predicate:

WHERE user_id = SUSER_SNAME()

This requires you to dynamically set each user's connection properties before a connection is made to the database (i.e. each database user has a specific SQL username and password). If this not a viable solution, you may want to consider building a framework around the L2S context so that you can inject your own logic. If you would like further inspiration, havea read here: http://www.west-wind.com/WebLog/posts/160237.aspx