Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Barry's answer provides a working solution to the question posed by the original poster. Thanks to both of those individuals for asking and answering.</p> <p>I found this thread as I was trying to devise a solution to a quite similar problem: programmatically creating an expression tree that includes a call to the Any() method. As an additional constraint, however, the <strong>ultimate goal</strong> of my solution was to pass such a dynamically-created expression through Linq-to-SQL so that the work of the Any() evaluation is actually performed in the DB itself. </p> <p>Unfortunately, the solution as discussed so far is not something that Linq-to-SQL can handle.</p> <p>Operating under the assumption that this might be a pretty popular reason for wanting to build a dynamic expression tree, I decided to augment the thread with my findings.</p> <p>When I attempted to use the result of Barry's CallAny() as an expression in a Linq-to-SQL Where() clause, I received an InvalidOperationException with the following properties:</p> <ul> <li>HResult=-2146233079</li> <li>Message="Internal .NET Framework Data Provider error 1025"</li> <li>Source=System.Data.Entity</li> </ul> <p>After comparing a hard-coded expression tree to the dynamically-created one using CallAny(), I found that the core problem was due to the Compile() of the predicate expression and the attempt to invoke the resulting delegate in the CallAny(). Without digging deep into Linq-to-SQL implementation details, it seemed reasonable to me that Linq-to-SQL wouldn't know what to do with such a structure.</p> <p>Therefore, after some experimentation, I was able to achieve my desired goal by slightly revising the suggested CallAny() implementation to take a predicateExpression rather than a delegate for the Any() predicate logic. </p> <p>My revised method is:</p> <pre><code>static Expression CallAny(Expression collection, Expression predicateExpression) { Type cType = GetIEnumerableImpl(collection.Type); collection = Expression.Convert(collection, cType); // (see "NOTE" below) Type elemType = cType.GetGenericArguments()[0]; Type predType = typeof(Func&lt;,&gt;).MakeGenericType(elemType, typeof(bool)); // Enumerable.Any&lt;T&gt;(IEnumerable&lt;T&gt;, Func&lt;T,bool&gt;) MethodInfo anyMethod = (MethodInfo) GetGenericMethod(typeof(Enumerable), "Any", new[] { elemType }, new[] { cType, predType }, BindingFlags.Static); return Expression.Call( anyMethod, collection, predicateExpression); } </code></pre> <p>Now I will demonstrate its usage with EF. For clarity I should first show the toy domain model &amp; EF context I am using. Basically my model is a simplistic Blogs &amp; Posts domain ... where a blog has multiple posts and each post has a date:</p> <pre><code>public class Blog { public int BlogId { get; set; } public string Name { get; set; } public virtual List&lt;Post&gt; Posts { get; set; } } public class Post { public int PostId { get; set; } public string Title { get; set; } public DateTime Date { get; set; } public int BlogId { get; set; } public virtual Blog Blog { get; set; } } public class BloggingContext : DbContext { public DbSet&lt;Blog&gt; Blogs { get; set; } public DbSet&lt;Post&gt; Posts { get; set; } } </code></pre> <p>With that domain established, here is my code to ultimately exercise the revised CallAny() and make Linq-to-SQL do the work of evaluating the Any(). My particular example will focus on returning all Blogs which have at least one Post that is newer than a specified cutoff date.</p> <pre><code>static void Main() { Database.SetInitializer&lt;BloggingContext&gt;( new DropCreateDatabaseAlways&lt;BloggingContext&gt;()); using (var ctx = new BloggingContext()) { // insert some data var blog = new Blog(){Name = "blog"}; blog.Posts = new List&lt;Post&gt;() { new Post() { Title = "p1", Date = DateTime.Parse("01/01/2001") } }; blog.Posts = new List&lt;Post&gt;() { new Post() { Title = "p2", Date = DateTime.Parse("01/01/2002") } }; blog.Posts = new List&lt;Post&gt;() { new Post() { Title = "p3", Date = DateTime.Parse("01/01/2003") } }; ctx.Blogs.Add(blog); blog = new Blog() { Name = "blog 2" }; blog.Posts = new List&lt;Post&gt;() { new Post() { Title = "p1", Date = DateTime.Parse("01/01/2001") } }; ctx.Blogs.Add(blog); ctx.SaveChanges(); // first, do a hard-coded Where() with Any(), to demonstrate that // Linq-to-SQL can handle it var cutoffDateTime = DateTime.Parse("12/31/2001"); var hardCodedResult = ctx.Blogs.Where((b) =&gt; b.Posts.Any((p) =&gt; p.Date &gt; cutoffDateTime)); var hardCodedResultCount = hardCodedResult.ToList().Count; Debug.Assert(hardCodedResultCount &gt; 0); // now do a logically equivalent Where() with Any(), but programmatically // build the expression tree var blogsWithRecentPostsExpression = BuildExpressionForBlogsWithRecentPosts(cutoffDateTime); var dynamicExpressionResult = ctx.Blogs.Where(blogsWithRecentPostsExpression); var dynamicExpressionResultCount = dynamicExpressionResult.ToList().Count; Debug.Assert(dynamicExpressionResultCount &gt; 0); Debug.Assert(dynamicExpressionResultCount == hardCodedResultCount); } } </code></pre> <p>Where BuildExpressionForBlogsWithRecentPosts() is a helper function that uses CallAny() as follows:</p> <pre><code>private Expression&lt;Func&lt;Blog, Boolean&gt;&gt; BuildExpressionForBlogsWithRecentPosts( DateTime cutoffDateTime) { var blogParam = Expression.Parameter(typeof(Blog), "b"); var postParam = Expression.Parameter(typeof(Post), "p"); // (p) =&gt; p.Date &gt; cutoffDateTime var left = Expression.Property(postParam, "Date"); var right = Expression.Constant(cutoffDateTime); var dateGreaterThanCutoffExpression = Expression.GreaterThan(left, right); var lambdaForTheAnyCallPredicate = Expression.Lambda&lt;Func&lt;Post, Boolean&gt;&gt;(dateGreaterThanCutoffExpression, postParam); // (b) =&gt; b.Posts.Any((p) =&gt; p.Date &gt; cutoffDateTime)) var collectionProperty = Expression.Property(blogParam, "Posts"); var resultExpression = CallAny(collectionProperty, lambdaForTheAnyCallPredicate); return Expression.Lambda&lt;Func&lt;Blog, Boolean&gt;&gt;(resultExpression, blogParam); } </code></pre> <p>NOTE: I found one other seemingly unimportant delta between the hard-coded and dynamically-built expressions. The dynamically-built one has an "extra" convert call in it that the hard-coded version doesn't seem to have (or need?). The conversion is introduced in the CallAny() implementation. Linq-to-SQL seems to be ok with it so I left it in place (although it was unnecessary). I was not entirely certain if this conversion might be needed in some more robust usages than my toy sample. </p>
    singulars
    1. This table or related slice is empty.
    1. POHow do I create an expression tree calling IEnumerable<TSource>.Any(...)?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USAaron Heusser
    1. USAaron Heusser
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    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. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

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