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