0

We are migration from DataBase first approach to Code first for Entity Framework 6 We have model 

public class Parent
{
    public virtual  int Id { get; set; }
    public virtual DateTimeOffset DateFrom { get; set; }
    public virtual DateTimeOffset DateTo { get; set; }
    //navigation property
    public virtual ICollection<Child> Cildren { get; set; }
}

public class Child {
    public int Id { get; set; }
    public DateTimeOffset DateFrom { get; set; }
    public DateTimeOffset DateTo { get; set; }
    //navigation property
    public virtual Parent Parent { get; set; }
}

We have several functions in edmx like this (but a little complicated, functions call each other ) 

    <Function Name="IsParentActive" ReturnType="Edm.Boolean">
      <Parameter Name="Parent" Type="MyContext.Parent" />
      <Parameter Name="time" Type="Edm.DateTimeOffset" Nullable="false" />
      <DefiningExpression>
        (Parent.DateFrom &lt;= time) AND (Parent.DateTo &gt; time)
      </DefiningExpression>
    </Function>

   <Function Name="IsChildActive" ReturnType="Edm.Boolean">
      <Parameter Name="Child" Type="MyContext.Child" />
      <Parameter Name="time" Type="Edm.DateTimeOffset" Nullable="false" />
      <DefiningExpression>
        (Child.DateFrom &lt;= time) AND (Child.DateTo &gt; time) AND
           MyContext.IsParentActive(Child.Parent, time)
      </DefiningExpression>
    </Function>

in C# code we have next mapping 

[DbFunction("MyContext", "IsParentActive")]
public static bool IsActive(this Parent parent, DateTimeOffset time) {
  return time >= parent.DateFrom && time < parent.DateTo
}

[DbFunction("MyContext", "IsChildActive")]
public static bool IsActive(this Child child, DateTimeOffset time) {
  return time >= child.DateFrom && time < child.DateTo
     && child.Parent.IsActive(time);
}

And We can use it in IQueryable in any place

var result = context.Parents.Where(p => p.IsActive(time));
var result = context.Parents.Where(p => p.Cildren.Any(c => !c.IsActive(time));
var result = context.Cildren.Where(p => p.Parent.IsActive(time));

Now I can't find good way to replace this approach.

I tried use C# functions with expression but They not applicable in subqueries (context.Cildren.Where(p => p.Parent.IsActive(time)))

Sergey Chernov
  • 1
  • Why do you say it's not applicable in subqueries? It should work. Have you tried? If yes, what error is returned? – Arnaud F. Sep 28 '18 at 05:35
  • I tried to replece DBFunction to c# Expretion function, Expression> IsChildActive(DateTimeOffset time) { var exprParent = IsParentActive(time); Expression> result = child => time >= child.DateFrom && time < child.DateTo && IsParentActive.Invoke(child.Parent); return result.Expand(); } – Sergey Chernov Sep 28 '18 at 07:18
  • Did you consider adding computed columns to the database tables, so you don't have to call these functions client-side? – Gert Arnold Sep 28 '18 at 07:22
  • Use computed columns is not a option, because some times we use it client side and sometimes in IQueryable – Sergey Chernov Sep 28 '18 at 07:26

0 Answers0