Creating a .NET Salesforce OData Service

Salesforce provides online enterprise systems such as customer relationship management & analytical systems among others. It provides web services for external applications so that they can access its entities. External applications call these web services for reports, analytics, data syncing, or updating purposes. So, why do we need another service to access Salesforce data?

The access to these services is not uncontrolled or unlimited, Salesforce enforces throttling and request limits techniques to balance the load on the servers. Many external applications can be accessing these services and their requests could be denied if limits are reached.

This issue can be managed though not completely eliminated by using a service that makes requests to Salesforce on part of external applications. This service should cache data so that repeated requests are not made to Salesforce for the same record set. OData is a widely used REST-based protocol and hence a natural choice for such a service.

Now that we know why we may need an OData Service over Salesforce, let's dive into creating one in ASP.NET Core 3.1. We will also see how to access this OData feed in external apps like Microsoft Excel and also in a WinForms application. On the way, we will see how ComponentOne DataConnector libraries make it easy to achieve all the above.

Setting Up an OData Service Project

Pre-requisites

Salesforce supports OAuth authentication for external applications that wish to connect to its REST API. This document explains how to set up an app to enable OAuth.

The following information is required to connect to Salesforce:

• Token endpoint
• API URL
• Client ID
• Client secret
• Salesforce username and password
• Security token

Setup Project

• In Visual Studio 2019, Create a new Web API App.
• Add the following NuGet packages from nuget.org.
  • Microsoft.AspNetCore.OData(version-7.5.7)
  • C1.EntityFramework.Salesforce
  • Microsoft.EntityFrameworkCore.Relational(version-3.1.13)

Scaffold Salesforce Entity Model

Open NuGet package manager console from Tools→NuGet Package Manager→Package Manager Console menu. Type in the following Entity Framework scaffolding command. Make sure to replace XXXX with the pre-requisite credentials to generate DbContext class and Models for Opportunity & Account entities.

Scaffold-DbContext "Username=xxxx;Password=xxxx;Security Token=xxxx;OAuth Client Id=xxxx; OAuth Client Secret=xxxx; OAuth Token Endpoint=https://ap16.salesforce.com/services/oauth2/token; Url=https://ap16.salesforce.com/services/data/v45.0” C1.EntityFrameworkCore.Salesforce -OutputDir “Model” -Tables Opportunity, Account

Enable Caching

In the Connection string, we can enable caching by setting Use Cache to "true", a value for Cache Tolerance, and Cache Location as below. For security reasons, the connection string should be moved to an appsetting.json file and its value should be read in Startup.cs using Configuration.

"Username=user@email.com;Password= xxxx;Security Token=xxxx;OAuth Client Id=xxx; OAuth Client Secret=xxxx; OAuth Token Endpoint=https://ap16.salesforce.com/services/oauth2/token; Url=https://ap16.salesforce.com/services/data/v45.0; Use Cache=true; Cache Tolerance=500; Cache Location='C:\Temp\salesforce.db'"

This will save us network round trips for the same record set thus enhancing performance and data availability. You can also configure incremental caching where data that is updated\inserted in Salesforce is only updated\added to cache instead of a full refresh. This will enhance the performance of the service over time.

This feature can be enabled by setting IncrementalUpdate to "true" and setting the Default Time Stamp column to "LastModifiedDate" in the connection string. To know more refer to the documentation

Create EDM Model

OData uses the Entity Data Model (EDM) to describe the structure of data. In ASP.NET Core OData, it’s easy to build the EDM Model based on the above types. So, add the following private static method at the end of class “Startup”.

 public static IEdmModel GetEdmModel()  
        {  
            ODataConventionModelBuilder builder = new ODataConventionModelBuilder();  
            builder.EntitySet<Account>("Accounts");  
            builder.EntitySet<Opportunity>("Opportunities");  
            return builder.GetEdmModel();  
        }  

Register OData Service

ASP.NET Core OData requires some services registered. The library provides an extension method called “AddOData()” to register the required OData services through the built-in dependency injection. So, add the following codes into “ConfigureServices” method of the “Startup” class:

 public void ConfigureServices(IServiceCollection services)  
        {  
            services.AddControllers().SetCompatibilityVersion(CompatibilityVersion.Version_3_0);  
            services.AddDbContext<MainContext>();  
            services.AddOData();  
        }

Register OData Endpoint

We also need to add an OData route to register the OData endpoint. Add an OData route named “odata” with the “odata” prefix to the OData routes, and call the “GetEdmModel()” to bind the Edm model to the endpoint.

Change the “Configure()” method of the “Startup” class as:

 public void Configure(IApplicationBuilder app, IWebHostEnvironment env)  
        {  
            if (env.IsDevelopment())  
            {  
                app.UseDeveloperExceptionPage();  
            }

            app.UseRouting();

            app.UseAuthorization();

            app.UseEndpoints(endpoints =>  
            {  
                endpoints.MapODataRoute("odata","odata",GetEdmModel());  
            });  
        }

Create Controller

• Right-click Controllers folder and select Add → New Scaffolded Item
• From Add New Scaffolded Item Window, select API in the left pane and select API-Controller-Empty from the right pane

Click add. In the next Add New Item Window, set the name of the controller to "OpportunitiesController". This will add an Opportunities Controller to the project. In the code below, we inherit The OpportunitiesController from ODataController and initialize an instance of MainContext that we had scaffolded earlier. We also add a Get method with the EnableQuery attribute. The Get method returns Opportunities. We could easily configure paging by adding a Page Size to the Enable Query attribute.

 public class OpportunitiesController : ODataController  
    {  
        MainContext ctx;

        public OpportunitiesController(MainContext context)  
        {  
            ctx = context;  
        }

        [EnableQuery]  
        public IActionResult Get()  
        {  
            return Ok(ctx.Opportunity);  
        }     

    }

Similar to OpportunitiesController, add an AccountsController:

 public class AccountsController : ODataController  
    {  
        private MainContext ctx;

        public AccountsController(MainContext context)  
        {  
            ctx = context;  
            System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.InvariantCulture;  
        }

        [EnableQuery(PageSize=10)]  
        public IActionResult Get()  
        {  
            return Ok(ctx.Account);  
        }

        [ODataRoute]  
        [EnableQuery]  
        public IActionResult Get(string key)  
        {  
            return Ok(ctx.Account.FirstOrDefault(c => c.Id == key));  
        }

   }

Running the OData Service

The OData service project is now ready. If we run the app and navigate to ~/odata/opportunities we should be able to get the result data.

We can even apply filtering by taking queries on this data:

Access Salesforce Data in OData Client Apps

Analyze Opportunity Data in Microsoft Excel

Excel can connect to OData and get data. You can use its rich analytical tools to analyze Salesforce data.

Open Excel and create a blank workbook. Go to the Data tab, click the GetData button. From the "From Other Sources" menu, click "OData feed". A dialog opens for entering the OData Feed. Enter the service URL we created and click OK.

In the next window and click "Load". This will load data to Excel. In the Home tab, click Analyze Data button to analyze the data, this will show Analyze Data (ideas) in the right pane. You could also add pivot tables and charts to analyze the Opportunities data

Analyzing Opportunities Data in WinForms Applications

We can analyze the Opportunities data in a WinForms application using ComponentOne FlexPivot. We simply have to bind the FlexPivot control to the Odata feed and users can get started with analyzing the data. To show how Opportunities data can be analyzed in WinForms app using FlexPivot control, you can use the DataConnectorExplorer application available under: ~\Documents\ComponentOne Samples\ServiceComponents\DataConnector\CS\Win\DataConnectorExplorer\DataConnectorExplorer.

Open the application and run it. When the app is open, it appears as:

In the left pane, select OData in the drop-down. Clear the values in the property grid then enter the OData feed we created above in the Url field, it would be something like(depending on the port your app is using): https://localhost:44312/odata/. Click Connect. The app shows the available tables in the OData feed.

In the SQL box, enter the SQL Query as follows:

• Select * from Opportunities
• Click on the arrow button on the top-right

This will populate the FlexPivot with fields of Opportunities table.

Let's start analyzing Opportunities data. Click on the Chart tab. We want to know the lead generation activity over each stage.

• Select Amount field in the Values, StageName in Rows, and LeadSource in Filter. Right-click on LeadSource and select FieldSettings
• Unselect all and then select "External", "Partners", "PhoneEnquiry", "TradeShow", "Web", from the FieldSettings window (The chart will plot the stage and amount based on the LeadSource)
• Click OK

WinForms App

There are a couple of ways to work with OData feed in a WinForms application.

1. Using the Service reference way as explained in this Microsoft document
2. Using ComponentOne OData data connector

The advantage with ComponentOne ODataConnector is that you can use either ADO.NET or EntityFrameworkCore to work with OData feeds. Here is an example code of binding the FlexGrid control to the OData feed using ADO.NET.

 private void Form1_Load(object sender, EventArgs e)  
        {  
            DataTable dt=new DataTable();  
            var connectionString="Url=[https://localhost:44312/odata/](https://localhost:44312/odata/)";  
            C1ODataConnection con = new C1ODataConnection(connectionString);  
            con.Open();  
            string sql = "select * from Accounts";  
            using (C1ODataDataAdapter da=new C1ODataDataAdapter(con,sql))  
            {  
                da.Fill(dt);  
            }  
            con.Close();  
            this.c1FlexGrid1.DataSource = dt;  
        }

The grid now shows account data grouped by type.

In this blog, we demonstrated how an OData feed over Salesforce can enhance the experience of external applications. The ComponentOne DataConnectors offer great connectivity, performance, Querying, and CRUD features that simplify developers' effort to create such applications.