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:
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
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
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:
Normally, EF scans the entity and merges metadata from the C# class, any other configuration data provided, say through model builder provided in
The controller is a bit more complex because we have some “search” requirements to meet:
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
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!
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:
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; }
}
/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();
}
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("%", ".*") + "$";
}
}
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!
