1

I'm publishing an OData service. I have two models that need to be exposed to the OData endpoint. I've abstracted my model to easy to visualize Blog/Posts model below. This service works well when both are stored in Views in the DB. In that case, when someone uses $expand to include the Posts property, the tables are joined and unflattened automatically. I use EF's Include method to eagerly load the Posts.

Now my DBA wants to use Table Valued Functions to access these two sets in order to apply permission screening based on the querying user. The interface would be something like this:

IQueryable<Blog> Blogs(int userId);
IQueryable<Post> Posts(int userId);

I can access these individually and even join one View to a TVF but the problem is that I can no longer use EF's Include method to eagerly populate the Posts collection when they are both backed by TVFs.

Problem: I need to be able to query (JOIN) the two sets and reassemble (unflatten) the result set into Blogs that contain a collection of Posts. The solution also needs to avoid the N+1 Selects Issue.

I realize the use of Functions is uncommon with EF but has anyone come across this issue? Is there a way to do this using Linq to Entities, Automapper, Store Functions, or any other way?

I am using Web API with EF Code First, Automapper's Queryable Extensions, and the Store Functions library to enable TVFs in EF queries.

public class Blog 
{
  public Blog()
  {
    Posts = new HashSet<Post>();
  }
  public int Id { get; set; }
  public string Name { get; set; }
  public virtual ICollection<Post> Posts { get; set; }
}

public class Post
{
  public int Id { get; set; }
  public int BlogId { get; set; }
  public string Title { get; set; }
  public string Content { get; set; }

  public virtual Blog Blog { get; set; }
}
Community
  • 1
  • 1
Ben Ripley
  • 2,115
  • 21
  • 33

0 Answers0