faking a view in an odata data source

The current Dynamcis Customer Experience virtual entity capability requires an odata source. So I was fine creating a Virtual Entity but my client also needed the ability to efficiently grab a a set of data that require a few joins across the source database. What to do?
Creating an odata source is easy. I needed to do this and followed the instructions for using the odata server for aspnetcore 2.1 and entityframeworkcore:

• http://odata.github.io/WebApi/ (OData)
• https://docs.microsoft.com/en-us/ef/core/index (EFCore)

Since this was all dotnet core, I was using my linux laptop and visual studio code as my editor.
It was all straight forward but I ran into an issue where I needed to efficiently pull some “people” data from this virtual entity but the entity framework queries were not efficient. What I really wanted to do was pull a very specific query, joined to two other tables that returned exactly the data that I needed using the odata protocol. While you could easily run a query in entity framework that returns exactly the data you need using FromQuery and a Select statement, having to use Include in my entityframework query forced my query to pull too much data from the database and my odata server needed to be small so it could run on a cheap azure appservice plan.
It was clear that what I really needed was a simple way to pull from a view. EFCore supports this but there was one problem, there was no database view defined for what I wanted. Ugh! I needed to fake a view.
I really needed a new entity that would like a real entity to serialize in the OData layer but not be mapped in the EFCore layer and I needed to mimic a “full text search” type of pattern matching capability. That’s actually simple to do using a [NotMapped] attribute on my class and a regex. I needed this “view” to filter itself using more advanced filters than a contains method in Linq hence the need for regexs This allows the user to use wildcards.
I knew this “search” requirement would force computation to occur outside the database and on my server but for the data that I was working with this iterable approach was efficient enough for me on my tiny azure server. I was not able to use azure’s scalable search solution since that would mean I would need to setup a process to scan the database and push search data to azure–something that was too much for what I needed. Linq knows when you are using functions that it cannot push down optimize and directs the processing to pull the table from the database and process the rest of the functions locally. All of this was Ok for what I needed.
So here’s the key parts:
The object that will be serialized in the OData framework back to my client:

[NotMapped]
public  class  PeopleHomeView
{
 [Key]
 public  Guid  Id { get; set; }
 public  String  LastName { get; set; }
 public  String  FirstName { get; set; }
 public  DateTime? CreatedOn { get; set; }
 public  String  Nickname { get; set; }
 public  Guid? CompanyId { get; set; }
 public  String  CompanyName { get; set; }
}

Here’s the registration of a function on this entity during the EDM model builder callback. You have to use a function because the convention oriented approach and odata specification says that you cannot really add parameters to what essentially is a “get” request. Check out the conventions as described in the OData link above and review the routing section. So we need to use a “function”. Using a function means that the call will be /PeopleHomeView/NS.Find(text='Some%Name, orderBy=‘CreatedOn’)/$top=1000`

private static IEdmModel GetEdmModel() {
 ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
 builder.Namespace  =  "NS";
 //...
 var  phvb  =  builder.EntitySet<PeopleHomeView>("PeopleHomeViews");
 var  phvFunc  =  phvb
  .EntityType.Collection
  .Function("Find")
  .ReturnsFromEntitySet<PeopleHomeView>("PeopleHomeViews");
 phvFunc.Parameter<string>("text");
 phvFunc.Parameter<string>("orderBy");
 return builder.GetEdmModel();
}

The parameter text holds the filter criteria and orderBy is the criteria that I use to sort. I need to tell the view how to sort because if I use $top (which I do to limit results and avoid pulling the entire dataset) then I need to sort before the top is applied.
Normally, EF scans the entity and merges metadata from the C# class, any other configuration data provided, say through model builder provided in OnModelCreating, or any other approach you have to configuring EntityFramework. Previously, in the non-core version of EF, it would then run a “model check” against the database to see if an update should occur. EFCore does not do the check anymore so the fact that the entity does not exist in the source database does not trigger an error or the desire to run a database migration. Since our entity is not mapped, it does not try to do anything with it.
The controller is a bit more complex because we have some “search” requirements to meet:

[EnableQuery(MaxTop = 5000)]
public class PeopleHomeViewsController: ODataController {
 EC _db;
 public PeopleHomeViewsController(EC context) => _db = context;
 protected static String viewQuery = @ "
 select tp.guid as Id, LastName, FirstName, Nickname,
  tp.TimeEntered,
  tc.companyname, tc.guid as companyguid
 from tpeople tp
 left outer join texperience te on te.guid = tp.currentexperienceguid
 left outer join tcompany tc on tc.guid = te.companyguid ";

 [HttpGet]
 public IOrderedQueryable < PeopleHomeView > Find(
  [FromODataUri] string text, [FromODataUri] string orderBy) {
  if (orderBy == null || orderBy.Length == 0)
   orderBy = "CreatedOn DESC";
  if (text == null || text.Length == 0)
   return
  _db.PeopleHomeView.FromSql(viewQuery)
   .OrderBy(orderBy);
  var r = Utils.LikeToRegular(text);
  return _db.PeopleHomeView
   .FromSql(viewQuery)
   .Where(x => Regex.IsMatch(x.LastName ? ? "", r) ||
    Regex.IsMatch(x.FirstName ? ? "", r, RegexOptions.IgnoreCase) ||
    Regex.IsMatch(x.Nickname ? ? "", r, RegexOptions.IgnoreCase) ||
    Regex.IsMatch($ "{x.FirstName} {x.LastName}", r,
     RegexOptions.IgnoreCase))
   .OrderBy(orderBy);
 }
}
public static class Utils {
 public static String LikeToRegular(String value) {
  return "^" + Regex.Escape(value)
   .Replace("_", ".")
   .Replace("%", ".*") + "$";
 }
}

The controller runs a sql query, very efficiently, the continues with building an IQueryable return value. We want an IQueryable return value because the OData framework can further process the results based on odata filter query parameters such as $top.
Our controller does what we expected though, which is, it pulls the entire table and processes the attributes, hardcoded, that represent what we want to search on. In this case, the requirement was to search on the last, first, nick and full name of the person to limit results. The code supports empty orderBys and if no filter criteria is provided, it returns the entire dataset. By providing LikeToRegular we translate a % or _ symbol into regex language.
Now I could provide the standard odata methods needed by my Virtual Entity but where I needed better select performance, use a function that allowed me to get exactly what I wanted much more efficiently.
That’s it!