Linq, Lambda and Expressions

Introduction

So, I worked on this multi-company project a while ago, where I had to filter every single table by the Company of the current user and its subsidiaries. I wanted to create a single generic function for it. And Linq not supporting Invoke took me through a lot of twists and turns to get that done (or at least functional).

Writing a set of functions that will filter the user's data isn't quite the problem. If it was customized for every single table, it would have been a single expression (no sub-expression, no Invoke). But I'm too lazy to create a Repo for EVERY SINGLE TABLE. And even though I already created them (in curtsy to the project's deadline) , I never liked the fact that they existed. And have been trying to optimize them out ever since and until yesterday when I resolved myself to do it.

Problem

The problem is simple. We need a code that will get a filtered list of TModel from the database.
Something like:

(from m in ctx.Set<TModel>()
 where subCompaniesIds.Contains(m/*.UnknownChain*/.CompanyId)
 select m)

But we don't know how to get CompanyId from the m, since it depends on what TModel is. So, I created a Dictionary from TModel to the Expression that gets the CompanyId from it.

public static Dictionary<Type, Expression<Func<object, 
	int>>> CompanyIdExtractors = new Dictionary<Type, 
	Expression<Func<object, int>>>
{
	{ typeof(Company), e => ((Company)e).Id },
	{ typeof(Job), e => ((Job)e).CompanyId },
	{ typeof(Employee), e => ((Employee)e).Job.CompanyId },
};

That also wasn't the way to go. Since I can't call the expression on m to get the CompanyId. And stripping the Expression part will get me: "The LINQ expression node type 'Invoke' is not supported in LINQ to Entities".

So I decided to go around the whole problem and start my tree from the Company then work my way down. I used another Dictionary from TModel to the Expression that will navigate from Company to TModel. Here is what it looked like:

public static Dictionary<Type, Expression<Func<Company, IEnumerable<object>>>> 
CompanyConnections = new Dictionary<Type, 
Expression<Func<Company, IEnumerable<object>>>>
{
	{ typeof(Company), cmp => cmp },
	{ typeof(Job), cmp => cmp.Jobs },
	{ typeof(Employee), cmp => cmp.Jobs.SelectMany(j=> j.Employees) },
};
.
.
.
var xp = CompanyConnections[typeof(T)];
(from c in ctx.Companies
where subIds.Contains(c.Id)
select xp(c)).SelectMany().Cast<TModel>();

And guess what I got. "The LINQ expression node type 'Invoke' is not supported in LINQ to Entities".

Solution

In the end, I decided to go with an Expression<Func<TModel, bool>>. I need to include the subCompanyIds so it will need to be customized. So I went with yet another Dictionary (I love those) from TModel to Func<int[], Expression<Func<TModel, bool>>>. Basically, a function that returns an Expression with the int[] subCompanyIds in mind.

And it went like this:

public static Dictionary<Type, Func<int[], Expression<Func<object, 
bool>>>> CmpComparisonGenerators = new Dictionary<Type, Func<int[], 
Expression<Func<object, bool>>>>
{
    { typeof(Company), subIds => c=> subIds.Contains(((Company)c).Id) },
    { typeof(Job), subIds=> j=> subIds.Contains(((Job)j).CompanyId) },
    { typeof(Employee), subIds=> e=> subIds.Contains(((Employee)e).Job.CompanyId) },
};
.
.
.
var predicate = CmpComparisonGenerators[typeof(T)](GetCMPIds(ctx));
var q = ctx.Set<T>().Where(predicate).Cast<T>();

And I finally got it to work.

The Point

Linq to SQL doesn't support Invoke. But that doesn't mean it doesn't support its result.

History

• 12-12-15: Initial version posted