Intersoft Community - Crosslight - How to Use Crosslight Enterprise Framework and Related Tables?

How to Use Crosslight Enterprise Framework and Related Tables?

technical@intersoftpt.com — Wed, 28 Jan 2015 11:15:55 GMT

Hello Jimmy,

I was previously assuming that you already have a custom way to call the SP. Our best practice is usually generating the functions automatically from the EDMX to avoid mistakes in parameters definition, which is also goodfor maintenance. When the specific SP is added to the EDMX, Intersoft Entity Designer will automatically generate the code in both EntityContext and in the Controller. Technically, the underlying code in the EntityContext actually use "Context" to perform the SP call which is completely fine since it routes the call to SP directly without requiring LINQ.

You can then simply call the SP in your query method such as shown in the simple code snippet below:



[HttpGet]public QueryResult ItemsByName(string name, ODataQueryOptions<Item> options)
{
    return new QueryResult()
    {
        Results = this.GetItemsByName(name), 
        InlineCount = this.Request.GetInlineCount() 
    };
}

In the above example, the query calls the GetItemsByName method which was an auto-generated method fromthe Intersoft Entity Designer.

However, please note that the InlineCount might not work using the built-in Request.GetInlineCount method since the actual query is performed with a custom SP. In that case, you'll need to come up with another SP that returns the total number of rows of the query. But, if the app doesn't require paging, then InlineCount is not required.

Hope this helps!

How to Use Crosslight Enterprise Framework and Related Tables?

jtungol@silentpartnersoft.com — Wed, 28 Jan 2015 06:57:33 GMT

Hmmm... how do you call an SP without executing the query using db.Context? When calling the desired SP and passing the needed parameters manually, isn't it that db.Context is still required? A code snippet would be very much appreciated. There may be several ways of calling an SP, but could you please post a code that demonstrates your feedback?

We can think of using db.Context.Database.SqlQuery<TModel>("GetCategoryByLocations", params[]) based on your statement. But even that makes use of the db.Context. So please... pretty please confirm or provide a code snippet regarding your latest post.

Much appreciated. Many thanks!

How to Use Crosslight Enterprise Framework and Related Tables?

technical@intersoftpt.com — Tue, 27 Jan 2015 17:29:57 GMT

Hello Jimmy,

The incremental loading, filtering and paging are actually features that were built into the EntityController. Behind the scene, the options passed from the client is transformed into LINQ expression which is then sent to theserver for execution.

Per our experience, it will be quite tedious to achieve these functions at SP level, since you need to parse each option and apply it to your query subsequently.

However, let's say you already managed to implement the functions at SP, you can continue using QueryResult using the same method signature. But, instead of executing the query using db.Context, you call the desired SP and pass the needed parameters manually. Once your query returns, pass the result (should be either IList orIEnumerable) to the Results property of the QueryResult. This should allow the method to be still compatible withe client request, while at the same time enable you to use custom SP call in the server.

Hope this helps.

How to Use Crosslight Enterprise Framework and Related Tables?

jtungol@silentpartnersoft.com — Fri, 23 Jan 2015 22:20:51 GMT

We believe item #2 is supported, and have made it work. However, please do advise follow-up queries regarding item #1, as to how we can use ObjectResult<TEntityModel> GetCategoryByLocations(string location). We can extend this Crosslight-generated function to QueryResult using a partial class, but that breaks the paging or incremental loading, because the controller does not recognize the query string syntax (see attached screenshot). We might prefer to use stored procedures often for complex queries with EnableIncrementalLoading, EnableRefresh, EnableAsyncFilter, and ExitEditModeOnDelete set to true. So please, kindly advise what we need to do next or provide a link that specifically demonstrate how to use custom functions and stored procedures (if any). If not, code snippets would work as well.

Thanks!

How to Use Crosslight Enterprise Framework and Related Tables?

jtungol@silentpartnersoft.com — Fri, 23 Jan 2015 10:04:37 GMT

Hi Yudi,

Thanks for your feedback... and we appreciate the samples you have given. However there are still unasnwered queries regarding this matter.

I'm not sure we were able to get answers with item #2 (Display text from related Entity). Did you get a chance to review it? Kindly confirm if that's possible at all. Will the navigation work for related entities in this case, cosidering the sample tables/entities we provided?

In addition to item #1. Does the return type needs to be specified of type QueryResult? What about those that were auto-generated by Crosslight to use stored procedures such as ObjectResult<Category> GetCategoryByLocations(string location)? How do we take advantage of this instead of the custom or extended method you've shown?

Please advise. Thanks!

How to Use Crosslight Enterprise Framework and Related Tables?

yudi — Mon, 12 Jan 2015 10:11:22 GMT

Sorry for the delay in sending this.

Please obtain the sample in here and let us know whether it helps or not.

How to Use Crosslight Enterprise Framework and Related Tables?

jtungol@silentpartnersoft.com — Thu, 08 Jan 2015 00:31:23 GMT

Hi Yudi,

Thanks for your feedback. Please do provide the sample solution for this sample.

How to Use Crosslight Enterprise Framework and Related Tables?

yudi — Tue, 06 Jan 2015 11:18:14 GMT

First, Sorry for the delay in sending this.

To filter data using subquery should be done in server. We need to:

create/modify a method in server and do subquery
using ServiceDescriptor in Core project (client) to construct the request

In my end, I create a project using Business project template and filter Category data using subquery with a simple condition: the app will list categories in a selection list where location is equal to "My Room". Location in my project is a field from Items table (related table)

Following snippet is added into the Controller class.

[HttpGet]
public QueryResult CategoriesByLocation(string location, ODataQueryOptions<Category> options)
{
        EntityContextProvider<InventoryEntities> db2 = new EntityContextProvider<InventoryEntities>();
        List<Item> items = db2.Context.Items.Where(o => o.Location == location).ToList();

    //List<int> categories = items.Select(o => o.CategoryID).Distinct().ToList();

    List<int> categories = new List<int>();
    foreach (var item in items)
    {
        if (!categories.Contains(item.CategoryID))
            categories.Add(item.CategoryID);
    }


    var query = db.Context.Categories.Where(o => categories.Contains(o.CategoryID));
    IQueryable results = options.ApplyTo(query);
    return new QueryResult { Results = results, InlineCount = this.Request.GetInlineCount() };
}

The subquery is done in here. This request: http://[server url]/data/inventory/categoriesbylocation?location=My%20Room will be handled by CategoriesByLocation().

In client, we can use ServiceDescriptor to contruct the request. Simply add following snippet code in ViewModel.

public CategoryListViewModel()
{
    this.ServiceDescriptor = new ServiceDescriptor();
    this.ServiceDescriptor.Parameters.Add("location", "My Room");
    this.ServiceDescriptor.ServiceName = "CategoriesByLocation";
    this.ServiceDescriptor.HttpMethod = HttpMethod.GET;
}

Hope this help. I will prepare the sample that I used in my local end to be uploaded soon.

How to Use Crosslight Enterprise Framework and Related Tables?

jtungol@silentpartnersoft.com — Mon, 29 Dec 2014 17:13:00 GMT

Hi Guys,

How do use Crosslight's Enterprise Framework to display text from a related table, and filter data using a subquery? Say for instance, we have a main table or entity called Location with fields such as LocationID, LocationName, Address, City, State, and Zip. Another entity is called Tickets, which has fields called TicketID, LocationID, UserID, Commission, Tax, Expense, Gross, DateCollected, and CollectionDate, where LocationID is a foreign key related to Location entity with one to many relationship... and UserID is a foreign key related to the User entity that was adopted from "User Management ang Authentication".

Filter Data Using a Subquery. Once the app loads, it will load a list of Tickets that are not yet collected (DateCollected is null) and UserID is equal to the logged user. This should be straight forward using the QueryDefition. Now, when you add a new Ticket the user needs to select a Location (much like the Category selection list from your samples). The issue that we are facing is... the app should only list Locations in a selection list that do not have uncollected Tickets. The query looks like this... SELECT * FROM Location WHERE LocationID NOT IN (SELECT LocationID FROM Ticket WHERE DateCollected IS NULL AND UserID = @UserID). How do we implement this type of filters, while the EditableEntityRepository that was generated by the Intersoft loads all location?
Display Text from Related Entity. We believe this is supported... but not sure sure which sample can we use as reference to achieve this. When the list of Tickets is loaded by the application using DataListViewModelBase, we need to display the LocationName as the DisplayMemberPath, and concatenated (Address, City, State, and Zip) or FullAddress as the DetailMemberPath. Looking at some samples, we understand that we can use Location partial class to implement the FullAddress, but will it work if we specify DisplayMemberPath = "Location.LocationName" and DetailMemberPath = "Location.FullAddress" in an ItemBindingDescription object for the TicketListBindingProvider?

Please advise. A sample for these kinds of scenarios would be nice. Thanks!