Migration from Lightswitch

gjross · October 16, 2017, 4:39pm

I am currently evaluating solutions to enable me to migrate from MS Lightswitch; I would like to know whether it is possible to build screens from a query on the underlying data?

In LS, you create the query on the table and build the screen upon this query; there is no way the end user can modify the query to gain access to the other (hidden) records in the table.

For example, I want to give a customer read/write access to a set of records rather than the whole dataset - is this possible? I don’t want to use a SQL query as this would not allow the ability to write to the record.

enchev · October 16, 2017, 6:45pm

Hi @gjross,

With Radzen you can limit the API access to desired by filtering entities during infer. For example you can check only Orders and Products to be available:

Later you can create screens only for inferred entities (in this case only these two tables).

Best Regards,
Vladimir

joshbooker · October 16, 2017, 7:44pm

I think what @gjross is asking about is a way to filter an entity set based on a parameter such as UserId for example.

LightSwitch had a way to model parameter queries on top of entity sets including a server-side PreProcessQuery 'interceptor' method to enable filtering and sorting.

In Radzen you can do something similar using partial classes and methods:

I'm not sure how to filter in the OnCategoriesRead method as how can you supply a parameter value to that method?

enchev · October 16, 2017, 8:33pm

Thanks @joshbooker!

Using Radzen security you can even define who can access particular page:

joshbooker · October 18, 2017, 3:55pm

@enchev

Thanks for your reply. I think there is a difference between client-side authorization at the page level and server-side authorization at the data level. In LightSwitch, if a server-side filter is defined, there is no way for the user to hack the OData calls and get data from the backend which doesn’t meet this filter. This is especially helpful with Multi-tenant apps which share a database.

How would we implement a server-side filter in radzen?

Something like the following, but how do we send the currentUserID parameter to the server without modifying generated code?

public partial class CustomersController
{
    partial void OnCustomersRead(currentUserID string, ref IQueryable<Category> items)
    {
      items = items.Where(i => i.SalespersonID = CurrentUserID);
    }
}

It would be really swell if radzen had a way to model queries on top of Entitysets. This would enable parameter filters and sorting

enchev · October 19, 2017, 5:28am

Hi @joshbooker,

You can get the current user server-side like this:

var user = this.HttpContext.User.FindFirst(System.Security.Claims.ClaimTypes.Name).Value;

Best Regards,
Vladimir

korchev · October 19, 2017, 6:48am

To add to what @enchev said one can use the Authorize attribute to restrict access to certain controllers only to certain roles. Here is how this is done for the Users controller:

[Authorize(Roles="Administrator", ActiveAuthenticationSchemes="Bearer")]
public class UsersController : Controller
{
}

You can do it for any controller generated by Radzen:

[Authorize(Roles="Administrator", ActiveAuthenticationSchemes="Bearer")]
public partial class CustomersController
{
    partial void OnCustomersRead(currentUserID string, ref IQueryable<Category> items)
    {
      items = items.Where(i => i.SalespersonID = CurrentUserID);
    }
}

joshbooker · October 19, 2017, 1:10pm

Thanks guys.

Perhaps my userID example isn’t the best to illustrate the suggestion.

I understand the ability to authorize on a controller level but I’m talking about the need to FILTER an entity set server-side based on a parameter (not necessarily based on user). So in this case the user would be authorized to access the controller, but it would apply a .Where condition as in the above code.

Another example, say I have a multi-tenant app sharing the same database, tenancy is a field in the Customer Table and want Customer Controller to return only those records assigned to a certain tenancy.

public partial class CustomersController
{
    partial void OnCustomersRead(tenantID string, ref IQueryable<Category> items)
    {
      items = items.Where(i => i.tenantID = tenantID);
    }
}

The missing bit is how to get the generated code to include the tenantID parameter. If radzen had the ability to ‘model’ queries on top of entity sets we could achieve this.

Thanks for listening.

gjross · October 19, 2017, 1:26pm

Thank you Joshbooker - I think you completely understand what I need to achieve!

korchev · October 20, 2017, 7:23pm

@joshbooker I don’t think OData (which we use exclusively) allows for passing custom parameters to entity query methods like that. Can you use the standard $filter operation to achieve the same? You can keep the tenant id as a page property and then call all methods with $filter=TenantID eq ${tenantID}

joshbooker · October 20, 2017, 10:18pm

@korchev thanks for your reply. $filter is insecure because a crafty user could hit the endpoint without a filter. The idea is to have the filter happen on the server transparent to the client.

Thus the suggestion to enhance radzen to enable modeling of simple queries at designtime. The query would generate a parameterized get method in the server side controller for the base entity class. Similar to how you gen for stored procs. Except a query is updatable because it’s an extension method of the base entity get method.

This would also make the middle tier more capable of being THE place for server side business logic.

korchev · October 21, 2017, 7:12am

@joshbooker how did LightSwitch handle this case? Where did it get the input parameters e.g. TenantID? Are such parameters hardcoded values or retrieved from the database somehow? Thanks!

joshbooker · October 21, 2017, 1:37pm

@korchev thanks for your reply. In LightSwitch queries could be modeled on top of entities having Filters, Sorts and Parameters. At design time a filter is added by selecting the property, comparison operator (eq, gt, contains, etc), the 'value type' and the value.

The value type can be either Literal, Parameter, Property, or Global.

Literal value is hard-coded
Parameter value generates the getter method with a parameter arg (as-in TenanID above)
Property value compares one property to another (ie: Customer.ModifiedOn >= Customer.CreatedOn)
Global is a seldom used option similar to Parameter but global in scope.

On the server side, three 'queries' are generated for every entity. All(), Single(id), SingleOrDefault(). Queries are composable so every query including singletons are composed on top of the All() query.

All modeled queries take a filter expression of the following type which evaluates to a boolean.

System.Linq.Expressions.Expression<System.Func<Customer, bool>>

Which is interpreted in the query method like so .Where(customer.tenantID == myTenantParam);

When you design a page based on a query you can optionally bind the parameter value to screen properties such as an input box value.

thanks for listening

korchev · October 21, 2017, 8:26pm

Thank you for explaining this! I have a few more questions:

Are those parameters mandatory? What happens if the user doesn’t specify a value in the input box?
Are those parameters passed from the client-side to the server side?

I am asking because if the value is not mandatory or passed from the client to the server-side a crafty user can still hit the endpoint without a filter. Or pass a different tenant id and see data for another user. This is probably not the case but it isn’t clear to me how LightSwitch queries mitigate that security risk. Getting the tenant id server-side based on the current user looks most secure.

Could you please clarify?

gjross · October 21, 2017, 8:44pm

In LS, the query may have fixed parameters which the end user has no control, hence the ability to give the user access to specific rows rather than the whole table contents.

I will get a screenshot to help explain.

joshbooker · October 23, 2017, 11:49am

@korchev. Literal parameters are not passed the values are specified at designtime and generated hardcoded serverside. CurrentUser and tenantID would be examples of a Global parameter the value of which is known server side so it is not passed from the client. So generated code would include getting that value similar to the current user code by @enchev above. Named parameters are passed from the client and can be made optional at designtime. When optional, the generated code implements OR conditions to return results when a param value is missing.

gjross · October 24, 2017, 3:31pm

As can be seen above, in LS, I created a fixed parameter query (note: on the linked table - clientdetail) called OCSdata. There is no input required by the end user. The screen creation wizard uses this query, which in turn, retrieves the rows from the table which match the parameter thereby restricting the user to seeing only those records. The user cannot alter the query parameter.

Whilst I see the usefulness of linking the query to the userid, I build different apps (layout, functionality, customer branding etc) based upon the users needs. Therefore, I restrict access to the app to the userid instead.

joshbooker · October 31, 2017, 10:55am

@korchev I like the addition of parsing bound functions as described here

https://www.radzen.com/documentation/odata/#metadata

Would this be a step in the direction of adding designtime queries? Now if there was a way to design the filters and then generate the functions.

Thanks

korchev · October 31, 2017, 11:08am

@joshbooker This was needed to expose as much of the MSGraph API surface as possible. However it would definitely be useful for “designed” queries. We are already using something similar when exposing MSSQL stored procedures.

joshbooker · October 31, 2017, 11:14am

Would it make sense to add a refresh data source operation which, instead of full infer from sql, would get the OData meta from the dot net backend. So we could add functions in controller code and then refresh to get them in the designer meta?