OData Web API Data Service using native SQL when Entity Framework cannot be used

Question

Q&A Format:

Context: Sometimes we need to expose a data table which is NOT compatible with Entity framework via OData. Sometimes the environment constraints prevent us from modifying the table schema. For example SAP data dump tables maintained by another party.

Requirements: We could wrap the database table using Entity framework this would be a simple task to generate an Odata Web ApI end point out of the box. In this context we need to translate the ODATA select query options into native SQL select statements.

Question: How do we write an SQL Builder to translate the OData request into SQL and return a recordset

tcwicks tcwicks · Accepted Answer · 2016-10-30T12:02:27

Usage scenario: Create a "Web API 2 OData V3 Controller with read/write actions" and bind it to the routing as per normal. Next add your data select methods by modifying the following template to fit your scenario:

public IHttpActionResult GetEntitySomeEntityType(ODataQueryOptions<EntityPMDVendorLookup> queryOptions)
{
    // validate the query.
    try
    {
        queryOptions.Validate(_validationSettings);
    }
    catch (ODataException ex)
    {
        return BadRequest(ex.Message);
    }
    SQLODataBuilder<SomeEntityType, SomeEntityType_Column> SQLBuilder;
    SQLBuilder = new SQLODataBuilder<Models.PMD.Lookup.SomeEntityType, Models.PMD.Lookup.SomeEntityType_Column>(queryOptions, SomeEntityType_Column.VendorNumber, true);
    SQLBuilder.DefaultSortColumn = SomeEntityType_Column.VendorNumber;
    SQLBuilder.DefaultSortAscending = true;
    SQLBuilder.UseSelectDistinct = true;
    List<SomeEntityType> ResultList;
    ResultList = new List<Models.PMD.Lookup.SomeEntityType>();
    ResultList = SQLBuilder.ExecuteQuery(@"TableName", System.Configuration.ConfigurationManager.ConnectionStrings[@"ConnectionStringName"].ConnectionString);
    return Ok<IEnumerable<SomeEntityType>>(ResultList);
}

The Data Entity Class: Create a data entity class that supports ILoadFromDataRow Interface below and provides an Enum for the columns:

Interface ILoadFromDataRow:

public interface ILoadFromDataRow
{
    bool LoadFromDataRow(System.Data.DataRow DR, IEnumerable<string> SelectedTableColumnList);
}

Entity Property Enum:

public enum SomeEntityType_Column
{
    [Description("Some Field")]
    SomeField = 0, // This is the Primary Key field value = 0
    [Description("Another Field")]
    AnotherField,
    [Description("Yet Another Field")]
    YetAnotherField,
}

Entity Data Class:

public class SomeEntityType : ILoadFromDataRow
{
    public SomeEntityType()
    {

    }
    public bool LoadFromDataRow(System.Data.DataRow DR, IEnumerable<string> SelectedTableColumnList)
    {
        bool Success;
        int FieldCount;
        Success = true;
        FieldCount = 0;
        try
        {
            foreach (string DBColumn in SelectedTableColumnList)
            {
                FieldCount += 1;
                switch (DBColumn)
                {
                    case @"Some Field":
                        SomeField = DR[@"FunkyDBFieldName"].ToString();
                        break;
                    case @"Another Field":
                        AnotherField = DR[@"Even More Funky"].ToString();
                        break;
                    case @"Yet Another Field":
                        YetAnotherField = DR[@"Very bad fielD NaMe With SpaCes"].ToString();
                        break;
                }
            }
            //Conditional Logic depending on your scenario
            if (string.IsNullOrEmpty(SomeField))
            {
                if (FieldCount == 1)
                {
                    // Some Logic to calculate stuff
                }
            }
        }
        catch
        {
            Success = false;
        }
        return Success;
    }

    public string SomeField { get; set; }
    public string AnotherField { get; set; }
    public string YetAnotherField { get; set; }
}

The full SQL Builder class exceeds the 30000 character limit allows for posts on Stack Overflow. Therefore I've posted the code on my own blog.

Entire code implementation is available here:

http://www.tcwicks.com/software-development/custom-dot-net-mvc-odata-web-api-provider-via-native-sql-when-entity-framework-is-not-feasible

Hope this helps.

OData Web API Data Service using native SQL when Entity Framework cannot be used

1 Answers