Migration from Lightswitch


#1

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.


#2

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


#3

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?


#4

Thanks @joshbooker!

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


#5

@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


#6

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


Defaulting logged username and current timestamp in forms
#7

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);
    }
}

#8

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.


#9

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


#10

@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}


#11

@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.


#12

@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!


#13

@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.

image

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


#14

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?


#15

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.


#16

@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.


#17

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.


#18

@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


#19

@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.


Idea: optionally create server tier for non-SQL data sources
#20

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?