Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I saw you commented on my answer on another question, so i thought i'd reply here also. I've made some modifications and improvements to the code ( support for compiled queries and custom extension method to expression substitutions ).</p> <p>This might serve as an answer:</p> <pre><code>/// &lt;summary&gt; /// Type helpers /// &lt;/summary&gt; internal static class TypeSystem { private static Type FindIEnumerable(Type seqType) { Type type; if (seqType == null || seqType == typeof(string) || seqType == typeof(byte[])) { return null; } else { if (!seqType.IsArray) { if (seqType.IsGenericType) { Type[] genericArguments = seqType.GetGenericArguments(); int num = 0; while (num &lt; (int)genericArguments.Length) { Type type1 = genericArguments[num]; Type[] typeArray = new Type[1]; typeArray[0] = type1; Type type2 = typeof(IEnumerable&lt;&gt;).MakeGenericType(typeArray); if (!type2.IsAssignableFrom(seqType)) { num++; } else { type = type2; return type; } } } Type[] interfaces = seqType.GetInterfaces(); if (interfaces != null &amp;&amp; (int)interfaces.Length &gt; 0) { Type[] typeArray1 = interfaces; int num1 = 0; while (num1 &lt; (int)typeArray1.Length) { Type type3 = typeArray1[num1]; Type type4 = TypeSystem.FindIEnumerable(type3); if (type4 == null) { num1++; } else { type = type4; return type; } } } if (!(seqType.BaseType != null) || !(seqType.BaseType != typeof(object))) { return null; } else { return TypeSystem.FindIEnumerable(seqType.BaseType); } } else { Type[] elementType = new Type[1]; elementType[0] = seqType.GetElementType(); return typeof(IEnumerable&lt;&gt;).MakeGenericType(elementType); } } } internal static Type GetElementType(Type seqType) { Type type = TypeSystem.FindIEnumerable(seqType); if (type != null) { return type.GetGenericArguments()[0]; } else { return seqType; } } } /// &lt;summary&gt; /// Marks an extension as compatible for custom linq expression expansion /// Optionally if you can not write the extension method to fit your needs, you can provide a /// expression id constant for a registered expression. /// &lt;/summary&gt; [AttributeUsage(AttributeTargets.Method, AllowMultiple= false, Inherited = false)] class ExpandableQueryMethodAttribute : Attribute { public ExpandableQueryMethodAttribute() { } public ExpandableQueryMethodAttribute(string expressionId) { _expressionId = expressionId; } private string _expressionId; public LambdaExpression TranslationExpression { get { return _expressionId != null ? QueryMethodTranslationExpressions.GetRegistered(_expressionId) : null; } } } /// &lt;summary&gt; /// Used to register expressions for extension method to expression substitutions /// &lt;/summary&gt; static class QueryMethodTranslationExpressions { private static Dictionary&lt;string, LambdaExpression&gt; expressionList = new Dictionary&lt;string, LambdaExpression&gt;(); /// &lt;summary&gt; /// Register expression /// &lt;/summary&gt; /// &lt;typeparam name="TFunc"&gt;type of expression delegate&lt;/typeparam&gt; /// &lt;param name="id"&gt;id constant for use with ExpandableQueryMethodAttribute&lt;/param&gt; /// &lt;param name="expr"&gt;expression&lt;/param&gt; public static void RegisterExpression&lt;TFunc&gt;(string id, Expression&lt;TFunc&gt; expr) { expressionList.Add(id, expr); } public static LambdaExpression GetRegistered(string id) { //Extensions; return expressionList[id]; } } static class Extensions { /// &lt;summary&gt; /// Use on object sets before using custom extension methods, except inside compiled queries /// &lt;/summary&gt; public static IQueryable&lt;T&gt; AsExtendable&lt;T&gt;(this IQueryable&lt;T&gt; source) { if (source is ExtendableQuery&lt;T&gt;) { return (ExtendableQuery&lt;T&gt;)source; } return new ExtendableQueryProvider(source.Provider).CreateQuery&lt;T&gt;(source.Expression); } } /// &lt;summary&gt; /// Provides PlaceHolderQuery /// /// No other functionality /// &lt;/summary&gt; public class PlaceHolderQueryProvider : IQueryProvider { public PlaceHolderQueryProvider() { } public IQueryable&lt;TElement&gt; CreateQuery&lt;TElement&gt;(Expression expression) { return new PlaceHolderQuery&lt;TElement&gt;(this, expression); } public IQueryable CreateQuery(Expression expression) { Type elementType = TypeSystem.GetElementType(expression.Type); try { return (IQueryable)Activator.CreateInstance(typeof(PlaceHolderQuery&lt;&gt;).MakeGenericType(elementType), new object[] { this, expression }); } catch (System.Reflection.TargetInvocationException tie) { throw tie.InnerException; } } public TResult Execute&lt;TResult&gt;(Expression expression) { throw new NotImplementedException(); } public object Execute(Expression expression) { throw new NotImplementedException(); } } /// &lt;summary&gt; /// Does nothing /// /// Acts only as a holder for expression /// &lt;/summary&gt; public class PlaceHolderQuery&lt;T&gt; : IQueryable&lt;T&gt;, IOrderedQueryable&lt;T&gt; { private Expression _expression; private PlaceHolderQueryProvider _provider; public PlaceHolderQuery(PlaceHolderQueryProvider provider, Expression expression) { _provider = provider; _expression = expression; } public IEnumerator&lt;T&gt; GetEnumerator() { throw new NotImplementedException(); } System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() { throw new NotImplementedException(); } public Type ElementType { get { return typeof(T); } } public Expression Expression { get { return _expression; } } public IQueryProvider Provider { get { return _provider; } } } /// &lt;summary&gt; /// Walks the expression tree and invokes custom extension methods ( to expand them ) or substitutes them with custom expressions /// &lt;/summary&gt; class ExtendableVisitor : ExpressionVisitor { class ExpandingVisitor : ExpressionVisitor { private Dictionary&lt;ParameterExpression, Expression&gt; _substitutionDictionary; public ExpandingVisitor(Dictionary&lt;ParameterExpression, Expression&gt; subDict) { _substitutionDictionary = subDict; } protected override Expression VisitParameter(ParameterExpression node) { if (_substitutionDictionary != null &amp;&amp; _substitutionDictionary.ContainsKey(node)) return _substitutionDictionary[node]; else return base.VisitParameter(node); } } IQueryProvider _provider; internal ExtendableVisitor() { _provider = new PlaceHolderQueryProvider(); } protected override Expression VisitMethodCall(MethodCallExpression node) { ExpandableQueryMethodAttribute attrib = (ExpandableQueryMethodAttribute)node.Method.GetCustomAttributes(typeof(ExpandableQueryMethodAttribute), false).FirstOrDefault(); if (attrib != null &amp;&amp; node.Method.IsStatic) { if (attrib.TranslationExpression != null &amp;&amp; attrib.TranslationExpression.Parameters.Count == node.Arguments.Count) { Dictionary&lt;ParameterExpression, Expression&gt; subDict = new Dictionary&lt;ParameterExpression,Expression&gt;(); for (int i = 0; i &lt; attrib.TranslationExpression.Parameters.Count; i++) { subDict.Add(attrib.TranslationExpression.Parameters[i], node.Arguments[i]); } ExpandingVisitor expander = new ExpandingVisitor(subDict); Expression exp = expander.Visit(attrib.TranslationExpression.Body); return exp; } else if (typeof(IQueryable).IsAssignableFrom(node.Method.ReturnType)) { object[] args = new object[node.Arguments.Count]; args[0] = _provider.CreateQuery(node.Arguments[0]); for (int i = 1; i &lt; node.Arguments.Count; i++) { Expression arg = node.Arguments[i]; args[i] = (arg.NodeType == ExpressionType.Constant) ? ((ConstantExpression)arg).Value : arg; } Expression exp = ((IQueryable)node.Method.Invoke(null, args)).Expression; return exp; } } return base.VisitMethodCall(node); } } /// &lt;summary&gt; /// Used for query compilation /// /// If custom extension methods are used, the existing CompileQuery functions do not work, so I had to write this. /// &lt;/summary&gt; static class CompiledExtendableQuery { public static Func&lt;TContext, TResult&gt; Compile&lt;TContext, TResult&gt;( Expression&lt;Func&lt;TContext, TResult&gt;&gt; expr) where TContext : ObjectContext { return System.Data.Objects.CompiledQuery.Compile(expr.Update(new ExtendableVisitor().Visit(expr.Body), expr.Parameters)); } public static Func&lt;TContext, TArg0, TResult&gt; Compile&lt;TContext, TArg0, TResult&gt;( Expression&lt;Func&lt;TContext, TArg0, TResult&gt;&gt; expr) where TContext : ObjectContext { return System.Data.Objects.CompiledQuery.Compile(expr.Update(new ExtendableVisitor().Visit(expr.Body), expr.Parameters)); } public static Func&lt;TContext, TArg0, TArg1, TResult&gt; Compile&lt;TContext, TArg0, TArg1, TResult&gt; (Expression&lt;Func&lt;TContext, TArg0, TArg1, TResult&gt;&gt; expr) where TContext : ObjectContext { return System.Data.Objects.CompiledQuery.Compile(expr.Update(new ExtendableVisitor().Visit(expr.Body), expr.Parameters)); } public static Func&lt;TContext, TArg0, TArg1, TArg2, TResult&gt; Compile&lt;TContext, TArg0, TArg1, TArg2, TResult&gt;( Expression&lt;Func&lt;TContext, TArg0, TArg1, TArg2, TResult&gt;&gt; expr) where TContext : ObjectContext { return System.Data.Objects.CompiledQuery.Compile(expr.Update(new ExtendableVisitor().Visit(expr.Body), expr.Parameters)); } public static Func&lt;TContext, TArg0, TArg1, TArg2, TArg3, TResult&gt; Compile&lt;TContext, TArg0, TArg1, TArg2, TArg3, TResult&gt;( Expression&lt;Func&lt;TContext, TArg0, TArg1, TArg2, TArg3, TResult&gt;&gt; expr) where TContext : ObjectContext { return System.Data.Objects.CompiledQuery.Compile(expr.Update(new ExtendableVisitor().Visit(expr.Body), expr.Parameters)); } public static Func&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TResult&gt; Compile&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TResult&gt;( Expression&lt;Func&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TResult&gt;&gt; expr) where TContext : ObjectContext { return System.Data.Objects.CompiledQuery.Compile(expr.Update(new ExtendableVisitor().Visit(expr.Body), expr.Parameters)); } public static Func&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TResult&gt; Compile&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TResult&gt;( Expression&lt;Func&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TResult&gt;&gt; expr) where TContext : ObjectContext { return System.Data.Objects.CompiledQuery.Compile(expr.Update(new ExtendableVisitor().Visit(expr.Body), expr.Parameters)); } public static Func&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TResult&gt; Compile&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TResult&gt;( Expression&lt;Func&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TResult&gt;&gt; expr) where TContext : ObjectContext { return System.Data.Objects.CompiledQuery.Compile(expr.Update(new ExtendableVisitor().Visit(expr.Body), expr.Parameters)); } public static Func&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TResult&gt; Compile&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TResult&gt;( Expression&lt;Func&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TResult&gt;&gt; expr) where TContext : ObjectContext { return System.Data.Objects.CompiledQuery.Compile(expr.Update(new ExtendableVisitor().Visit(expr.Body), expr.Parameters)); } public static Func&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TResult&gt; Compile&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TResult&gt;( Expression&lt;Func&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TResult&gt;&gt; expr) where TContext : ObjectContext { return System.Data.Objects.CompiledQuery.Compile(expr.Update(new ExtendableVisitor().Visit(expr.Body), expr.Parameters)); } public static Func&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TResult&gt; Compile&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TResult&gt;( Expression&lt;Func&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TResult&gt;&gt; expr) where TContext : ObjectContext { return System.Data.Objects.CompiledQuery.Compile(expr.Update(new ExtendableVisitor().Visit(expr.Body), expr.Parameters)); } public static Func&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TResult&gt; Compile&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TResult&gt;( Expression&lt;Func&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TResult&gt;&gt; expr) where TContext : ObjectContext { return System.Data.Objects.CompiledQuery.Compile(expr.Update(new ExtendableVisitor().Visit(expr.Body), expr.Parameters)); } public static Func&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TArg11, TResult&gt; Compile&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TArg11, TResult&gt;( Expression&lt;Func&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TArg11, TResult&gt;&gt; expr) where TContext : ObjectContext { return System.Data.Objects.CompiledQuery.Compile(expr.Update(new ExtendableVisitor().Visit(expr.Body), expr.Parameters)); } public static Func&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TArg11, TArg12, TResult&gt; Compile&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TArg11, TArg12, TResult&gt;( Expression&lt;Func&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TArg11, TArg12, TResult&gt;&gt; expr) where TContext : ObjectContext { return System.Data.Objects.CompiledQuery.Compile(expr.Update(new ExtendableVisitor().Visit(expr.Body), expr.Parameters)); } public static Func&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TArg11, TArg12, TArg13, TResult&gt; Compile&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TArg11, TArg12, TArg13, TResult&gt;( Expression&lt;Func&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TArg11, TArg12, TArg13, TResult&gt;&gt; expr) where TContext : ObjectContext { return System.Data.Objects.CompiledQuery.Compile(expr.Update(new ExtendableVisitor().Visit(expr.Body), expr.Parameters)); } public static Func&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TArg11, TArg12, TArg13, TArg14, TResult&gt; Compile&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TArg11, TArg12, TArg13, TArg14, TResult&gt;( Expression&lt;Func&lt;TContext, TArg0, TArg1, TArg2, TArg3, TArg4, TArg5, TArg6, TArg7, TArg8, TArg9, TArg10, TArg11, TArg12, TArg13, TArg14, TResult&gt;&gt; expr) where TContext : ObjectContext { return System.Data.Objects.CompiledQuery.Compile(expr.Update(new ExtendableVisitor().Visit(expr.Body), expr.Parameters)); } } /// &lt;summary&gt; /// The query as it becomes when AsExtendable is called on it. /// &lt;/summary&gt; class ExtendableQuery&lt;T&gt; : IQueryable&lt;T&gt;, IOrderedQueryable&lt;T&gt; { ExtendableQueryProvider _provider; Expression _expression; public ExtendableQuery(ExtendableQueryProvider provider, Expression expression) { _provider = provider; _expression = expression; } public IEnumerator&lt;T&gt; GetEnumerator() { return _provider.ExecuteQuery&lt;T&gt;(_expression).GetEnumerator(); } System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() { return GetEnumerator(); } public Type ElementType { get { return typeof(T); } } public Expression Expression { get { return _expression; } } public IQueryProvider Provider { get { return _provider; } } } class ExtendableQueryProvider : IQueryProvider { IQueryProvider _underlyingQueryProvider; private ExtendableQueryProvider() { } internal ExtendableQueryProvider(IQueryProvider underlyingQueryProvider) { _underlyingQueryProvider = underlyingQueryProvider; } public IQueryable&lt;TElement&gt; CreateQuery&lt;TElement&gt;(Expression expression) { return new ExtendableQuery&lt;TElement&gt;(this, expression); } public IQueryable CreateQuery(Expression expression) { Type elementType = TypeSystem.GetElementType(expression.Type); try { return (IQueryable)Activator.CreateInstance(typeof(ExtendableQuery&lt;&gt;).MakeGenericType(elementType), new object[] { this, expression }); } catch (System.Reflection.TargetInvocationException tie) { throw tie.InnerException; } } internal IEnumerable&lt;T&gt; ExecuteQuery&lt;T&gt;(Expression expression) { return _underlyingQueryProvider.CreateQuery&lt;T&gt;(Visit(expression)).AsEnumerable(); } public TResult Execute&lt;TResult&gt;(Expression expression) { return _underlyingQueryProvider.Execute&lt;TResult&gt;(Visit(expression)); } public object Execute(Expression expression) { return _underlyingQueryProvider.Execute(Visit(expression)); } private Expression Visit(Expression exp) { ExtendableVisitor vstr = new ExtendableVisitor(); Expression visitedExp = vstr.Visit(exp); return visitedExp; } } </code></pre> <p>Sorry for the brevity of my reply, it's the middle of the night here and i've got to hurry as there is work to be done.</p> <p>I'll be happy to answer any questions you might have.</p>
    singulars
    1. This table or related slice is empty.
    1. POIQueryable extension method for linq2entities
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USLord Terabyte
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POIQueryable extension method for linq2entities
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTBountyClose
    1. COHave you used this with an extension method that returns a single item, the equivilant of .First or .Single? I was working to adapt your code to do this so that a method could provide its expression, but then I found the TranslationExpression which I think is exactly what I need. The problem is, I don't know what to pass to register expression, since the expression I have depends on certain parameters to be present: http://pastebin.com/CpY9Sg65
      singulars
      1. PO
      1. USAaronLS
    2. COP.S. This is what I have tried, but I get the error "Expression of type 'System.Linq.Expressions.MethodCallExpression' cannot be used for return type 'PropertyHistory'": http://pastebin.com/BzQWqJRY
      singulars
      1. PO
      1. USAaronLS
    3. CONevermind, I was making it more complicated than it needed to be. this works: `QueryMethodTranslationExpressions.RegisterExpression<Func<IQueryable<PropertyHistory>, PropertyHistory>>("1", source => source.FirstOrDefault(xp => xp.Ends > DateTime.Now && xp.Starts <= DateTime.Now) );`
      singulars
      1. PO
      1. USAaronLS
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload