We're having some issues implementing soft delete functionality with entity framework. The idea is to use a repository which is aware of the EF context. On the level of the repository we implemented a plugin system, these plugins get executed whenever an action is done on the repository. For example when we call Repository.GetQuery<Relation>() the plugins get executed. One of the plugins is a LogicalDeletePlugin, this plugin should add a Where(x => x.IsDeleted) statement to each table which is in the select. The idea was to implement this IsDeleted plugin using an ExpressionVisitor which visits the linq expression and finds all the "table" select statements and add the IsDeleted condition.
To clarify the question/problem I'll explain the issue using some code examples.
void Main()
{
var options = new ReadonlyRepositoryOptions() { ConnectionStringDelegate = () => Connection.ConnectionString };
using (var context = new ReadonlyObjectContextRepository<PFishEntities>(options))
{
var query = context.GetQuery<Relation>()
.Select(x => new {
Test = x.Bonus,
TestWorks = x.Bonus.Where(y => y.bonID == 100)
});
query.InterceptWith(new TestVisitor()).ToList();
}
}
public class TestVisitor : ExpressionVisitor {
private ParameterExpression Parameter { get; set; }
protected override Expression VisitBinary(BinaryExpression node) {
"VisitBinary".Dump();
Expression left = this.Visit(node.Left);
Expression right = this.Visit(node.Right);
var newParams = new[] { Parameter };
var condition = (LambdaExpression)new LogicalDeletePlugin().QueryConditionals.First().Conditional;
var paramMap = condition.Parameters.Select((original, i) => new { original, replacement = newParams[i] }).ToDictionary(p => p.original, p => p.replacement);
var fixedBody = ParameterRebinder.ReplaceParameters(paramMap, condition.Body);
return Expression.MakeBinary(ExpressionType.AndAlso, node, fixedBody, node.IsLiftedToNull, node.Method);
}
protected override Expression VisitParameter(ParameterExpression expr)
{
Parameter = expr;
return base.VisitParameter(expr);
}
}
void Main()
{
var options = new ReadonlyRepositoryOptions() { ConnectionStringDelegate = () => Connection.ConnectionString };
using (var context = new ReadonlyObjectContextRepository<PFishEntities>(options))
{
var query = context.GetQuery<Relation>()
.Select(x => new {
Test = x.Bonus,
TestWorks = x.Bonus.Where(y => y.bonID == 100)
});
query.InterceptWith(new TestVisitor()).ToList();
}
}
public class TestVisitor : ExpressionVisitor {
private ParameterExpression Parameter { get; set; }
protected override Expression VisitBinary(BinaryExpression node) {
"VisitBinary".Dump();
Expression left = this.Visit(node.Left);
Expression right = this.Visit(node.Right);
var newParams = new[] { Parameter };
var condition = (LambdaExpression)new LogicalDeletePlugin().QueryConditionals.First().Conditional;
var paramMap = condition.Parameters.Select((original, i) => new { original, replacement = newParams[i] }).ToDictionary(p => p.original, p => p.replacement);
var fixedBody = ParameterRebinder.ReplaceParameters(paramMap, condition.Body);
return Expression.MakeBinary(ExpressionType.AndAlso, node, fixedBody, node.IsLiftedToNull, node.Method);
}
protected override Expression VisitParameter(ParameterExpression expr)
{
Parameter = expr;
return base.VisitParameter(expr);
}
}
The above c# code will result in the following SQL code :
SELECT
[UnionAll1].[relID] AS [C1],
[UnionAll1].[C2] AS [C2],
[UnionAll1].[C1] AS [C3],
[UnionAll1].[bonID] AS [C4],
[UnionAll1].[bonCUSTOMERID] AS [C5],
[UnionAll1].[bonRELATIONARTICLEBONUSID] AS [C6],
[UnionAll1].[bonINVOICEID] AS [C7],
[UnionAll1].[bonSALEROWID] AS [C8],
[UnionAll1].[bonVALUE] AS [C9],
[UnionAll1].[bonPERCENTAGE] AS [C10],
[UnionAll1].[bonMANUAL] AS [C11],
[UnionAll1].[bonPAID] AS [C12],
[UnionAll1].[IsDeleted] AS [C13],
[UnionAll1].[InternalReference] AS [C14],
[UnionAll1].[ConcurrencyToken] AS [C15],
[UnionAll1].[Created] AS [C16],
[UnionAll1].[CreatedBy] AS [C17],
[UnionAll1].[Updated] AS [C18],
[UnionAll1].[UpdatedBy] AS [C19],
[UnionAll1].[DisplayMember] AS [C20],
[UnionAll1].[ValueMember] AS [C21],
[UnionAll1].[SearchField] AS [C22],
[UnionAll1].[CreateDate] AS [C23],
[UnionAll1].[C3] AS [C24],
[UnionAll1].[C4] AS [C25],
[UnionAll1].[C5] AS [C26],
[UnionAll1].[C6] AS [C27],
[UnionAll1].[C7] AS [C28],
[UnionAll1].[C8] AS [C29],
[UnionAll1].[C9] AS [C30],
[UnionAll1].[C10] AS [C31],
[UnionAll1].[C11] AS [C32],
[UnionAll1].[C12] AS [C33],
[UnionAll1].[C13] AS [C34],
[UnionAll1].[C14] AS [C35],
[UnionAll1].[C15] AS [C36],
[UnionAll1].[C16] AS [C37],
[UnionAll1].[C17] AS [C38],
[UnionAll1].[C18] AS [C39],
[UnionAll1].[C19] AS [C40],
[UnionAll1].[C20] AS [C41],
[UnionAll1].[C21] AS [C42],
[UnionAll1].[C22] AS [C43]
FROM (SELECT
CASE WHEN ([Extent2].[bonID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1],
[Extent1].[relID] AS [relID],
1 AS [C2],
[Extent2].[bonID] AS [bonID],
[Extent2].[bonCUSTOMERID] AS [bonCUSTOMERID],
[Extent2].[bonRELATIONARTICLEBONUSID] AS [bonRELATIONARTICLEBONUSID],
[Extent2].[bonINVOICEID] AS [bonINVOICEID],
[Extent2].[bonSALEROWID] AS [bonSALEROWID],
[Extent2].[bonVALUE] AS [bonVALUE],
[Extent2].[bonPERCENTAGE] AS [bonPERCENTAGE],
[Extent2].[bonMANUAL] AS [bonMANUAL],
[Extent2].[bonPAID] AS [bonPAID],
[Extent2].[IsDeleted] AS [IsDeleted],
[Extent2].[InternalReference] AS [InternalReference],
[Extent2].[ConcurrencyToken] AS [ConcurrencyToken],
[Extent2].[Created] AS [Created],
[Extent2].[CreatedBy] AS [CreatedBy],
[Extent2].[Updated] AS [Updated],
[Extent2].[UpdatedBy] AS [UpdatedBy],
[Extent2].[DisplayMember] AS [DisplayMember],
[Extent2].[ValueMember] AS [ValueMember],
[Extent2].[SearchField] AS [SearchField],
[Extent2].[CreateDate] AS [CreateDate],
CAST(NULL AS bigint) AS [C3],
CAST(NULL AS bigint) AS [C4],
CAST(NULL AS bigint) AS [C5],
CAST(NULL AS bigint) AS [C6],
CAST(NULL AS bigint) AS [C7],
CAST(NULL AS decimal(20,4)) AS [C8],
CAST(NULL AS decimal(20,4)) AS [C9],
CAST(NULL AS bit) AS [C10],
CAST(NULL AS decimal(20,4)) AS [C11],
CAST(NULL AS bit) AS [C12],
CAST(NULL AS varchar(1)) AS [C13],
CAST(NULL AS varbinary(1)) AS [C14],
CAST(NULL AS datetimeoffset) AS [C15],
CAST(NULL AS varchar(1)) AS [C16],
CAST(NULL AS datetimeoffset) AS [C17],
CAST(NULL AS varchar(1)) AS [C18],
CAST(NULL AS varchar(1)) AS [C19],
CAST(NULL AS varchar(1)) AS [C20],
CAST(NULL AS varchar(1)) AS [C21],
CAST(NULL AS datetime2) AS [C22]
FROM [dbo].[Relation] AS [Extent1]
LEFT OUTER JOIN [dbo].[Bonus] AS [Extent2] ON [Extent1].[relID] = [Extent2].[bonCUSTOMERID]
UNION ALL
SELECT
2 AS [C1],
[Extent3].[relID] AS [relID],
2 AS [C2],
CAST(NULL AS bigint) AS [C3],
CAST(NULL AS bigint) AS [C4],
CAST(NULL AS bigint) AS [C5],
CAST(NULL AS bigint) AS [C6],
CAST(NULL AS bigint) AS [C7],
CAST(NULL AS decimal(20,4)) AS [C8],
CAST(NULL AS decimal(20,4)) AS [C9],
CAST(NULL AS bit) AS [C10],
CAST(NULL AS decimal(20,4)) AS [C11],
CAST(NULL AS bit) AS [C12],
CAST(NULL AS varchar(1)) AS [C13],
CAST(NULL AS varbinary(1)) AS [C14],
CAST(NULL AS datetimeoffset) AS [C15],
CAST(NULL AS varchar(1)) AS [C16],
CAST(NULL AS datetimeoffset) AS [C17],
CAST(NULL AS varchar(1)) AS [C18],
CAST(NULL AS varchar(1)) AS [C19],
CAST(NULL AS varchar(1)) AS [C20],
CAST(NULL AS varchar(1)) AS [C21],
CAST(NULL AS datetime2) AS [C22],
[Extent4].[bonID] AS [bonID],
[Extent4].[bonCUSTOMERID] AS [bonCUSTOMERID],
[Extent4].[bonRELATIONARTICLEBONUSID] AS [bonRELATIONARTICLEBONUSID],
[Extent4].[bonINVOICEID] AS [bonINVOICEID],
[Extent4].[bonSALEROWID] AS [bonSALEROWID],
[Extent4].[bonVALUE] AS [bonVALUE],
[Extent4].[bonPERCENTAGE] AS [bonPERCENTAGE],
[Extent4].[bonMANUAL] AS [bonMANUAL],
[Extent4].[bonPAID] AS [bonPAID],
[Extent4].[IsDeleted] AS [IsDeleted],
[Extent4].[InternalReference] AS [InternalReference],
[Extent4].[ConcurrencyToken] AS [ConcurrencyToken],
[Extent4].[Created] AS [Created],
[Extent4].[CreatedBy] AS [CreatedBy],
[Extent4].[Updated] AS [Updated],
[Extent4].[UpdatedBy] AS [UpdatedBy],
[Extent4].[DisplayMember] AS [DisplayMember],
[Extent4].[ValueMember] AS [ValueMember],
[Extent4].[SearchField] AS [SearchField],
[Extent4].[CreateDate] AS [CreateDate]
FROM [dbo].[Relation] AS [Extent3]
INNER JOIN [dbo].[Bonus] AS [Extent4] ON ([Extent3].[relID] = [Extent4].[bonCUSTOMERID]) AND (100 = [Extent4].[bonID]) AND ([Extent4].[IsDeleted] <> cast(1 as bit))) AS [UnionAll1]
ORDER BY [UnionAll1].[relID] ASC, [UnionAll1].[C1] ASC
As you can see in the resulting SQL query the IsDeleted statements gets added to the TestWorks = x.Bonus.Where(y => !y.IsDeleted) "select" code. That's what the TestVisitor is currently doing. But the question is now how we can also implement this on the other selects, the x => !x.IsDeleted doesn't get added on the Test = x.Bonus part.
Is the ExpressionVisitor the correct approach to get this done or should I go with another solution? All help is appreciated! If the explanation wasn't clear enough just let me know and I'll try to give some additional info!
Edit:
protected override Expression VisitMember(MemberExpression node)
{
var test = typeof(bool);
if (node.Type != test && node.Type != typeof(string))
{
var type = typeof(ArticleVat);
var condition = (LambdaExpression)Condition;
var newParams = new[] { Expression.Parameter(type, "x") };
var paramMap = condition.Parameters.Select((original, i) => new { original, replacement = newParams[i] }).ToDictionary(p => p.original, p => p.replacement);
var fixedBody = ParameterRebinder.ReplaceParameters(paramMap, condition.Body);
condition = Expression.Lambda(fixedBody, newParams);
var whereM = whereMethod.MakeGenericMethod(new [] { type });
var expr = Expression.Property(node.Expression, "ArticleVat");
var whereExpr = Expression.Call(whereM, expr, condition);
// whereExpr.Dump();
node.Dump();
// return Expression.MakeMemberAccess(whereExpr, node.Expression.Type.GetMember(node.Member.Name).Single());
// return Expression.MakeMemberAccess(
// whereExpr,
// node.Expression.Type.GetMember(node.Member.Name).Single());
}
return base.VisitMember(node);
}
The above is what I've added to the ExpressionVisitor. Now when I uncomment the return Expression.MamkeMemberaccess code an exception is thrown because it doesn't expect a MemberExpression or something.
Following is the solution I came up with :
/// <summary>
/// This visitor will append a .Where(QueryCondition) clause for a given Condition to each Navigation property
/// </summary>
public class InjectConditio
