Hello,
I have a situation where I need to join or "expand" to a table in a different database (Db2) than the table that provides the source for the current page. I'm able to add the data source and get Db2.Table data returned into a property, but couldn't figure out how to do the join between the two tables, to get the related data if possible?
Thanks,
Jatin
Hi Jatin,
You can extend your model with additional property. For example Order in Northwind with OrderDetails from our Sample database:
public partial class Order
{
[NotMapped]
public Sample.SampleOrderDetail SampleDetails
{
get;
set;
}
}
and populate this property on read from the other database/datacontext:
public partial class OrdersController
{
partial void OnOrdersRead(ref IQueryable<Models.Northwind.Order> items)
{
// Get SampleContext OrderDetails while reading Orders from NorthwindContext
var sampleContext = (SampleContext)HttpContext.RequestServices.GetService(typeof(SampleContext));
foreach(var item in items)
{
item.SampleDetails = sampleContext.SampleOrderDetails.Where(i => i.OrderId == item.OrderID).FirstOrDefault();
}
}
}
Best Regards,
Vladimir
I was able to use this method for joining. How would I show/data-bind a field from SampleDetails in Northwind's Order grid page? I was expecting SampleDetails to be returned back in the JSON along with the other properties of Order.
Thanks,
Hi Jatin,
There is one more step that I've forgot to paste it. Sorry for that!
You need to define the property in OData model as well to be part of the returned JSON:
public partial class Startup
{
partial void OnConfigureOData(ODataConventionModelBuilder builder)
{
var order = builder.EntitySet<Order>("Orders");
order.EntityType.ContainsOptional(i => i.SampleDetails);
}
}
Add the property to $expand parameter:
You can access the property in a DataGrid template like this:
${data.SampleDetails?.Quantity}
Best Regards,
Vladimir
Thanks Vladimir those additional steps worked!
I noticed filtering for the field brought in from the other database ("Quantity" in your case), is filtering case sensitive instead of case insensitive like other fields are filtering in the grid. Could this be because we implemented the "OnConfigureOData" function? Is there an easy way to get back the case insensitive filtering?
Thanks,
Hi Jatin,
Normally when bound to a database, filtering will use IQueryable (Linq to SQL) and the operation will be executed by the database server while in this case (not mapped property from other source) filtering will be server-side in-memory (Linq to Objects). In the first setup case sensitivity depends completely on the database settings while in the second setup case sensitivity depends on equality operators used by IComparer implementations.
The best way to handle this will be to create custom function for the filters client-side to force the case to be lower case for this column only before send it to the server:
import { Component, Injector } from '@angular/core';
import { OrdersGenerated } from './orders-generated.component';
@Component({
selector: 'page-orders',
templateUrl: './orders.component.html'
})
export class OrdersComponent extends OrdersGenerated {
constructor(injector: Injector) {
super(injector);
}
onFilter(filter) {
return filter
.replace(/SampleOrder\/UserName/g, 'tolower(SampleOrder/UserName)') // wrap the property with tolower() function
.replace(/'(.*?)'/, "tolower('$1')"); // wrap the value with tolower() function
}
}
Best Regards,
Vladimir
PS: I've used new Sample.Order.UserName property (instead Sample.OrderDetails.Quantity) from the other database just to illustrate string operations. Of course all these can be replaced by simply creating new SQL View where you can join the tables.
Hey Vladimir,
I have a NotMapped attribute for a string property, "DisplayName" in one of the models. Similar to the others, I add this property in OData model, so it can be part of returned JSON:
products.EntityType.ContainsOptional(i => i.DisplayName);
I get the following error on this line:
var model = oDataBuilder.GetEdmModel();
The property 'DisplayName' on type 'MyApp.Models.MyDB.Product' must be a Primitive property.
Not sure if there is another way to get it to return as part of JSON?
[NotMapped] public string DisplayName { get; set; }
Since this is simple property you can use this code:
Yep, I just realized that I could use that method for simple properties. Thanks!
Hello Vladimir,
When I update a row of a table/model with NotMapped properties the additional properties are not returned as part of the JSON from the PATCH request. How can I get it to return from this request?
Thanks,
Jatin
Hi Jatin,
There is a similar to OnOrdersRead partials method for update (OnXXXUdate) where you can populate the not mapped property.
Best Regards,
Vladimir
I have populated the Not Mapped properties in the OnXXXXUpdated implemented method and see via the debugger that the not mapped properties are in the "itemToReturn". However, they're not part of the JSON returned?
