OData service without writing code

One of the most important aspects of software development is the rapid prototyping. Most services require at least some CRUD operations, and most applications can be described as data-driven applications. The APIs I write mostly take data from the database and return it to the client as JSON. OdataToEntity is a tool that generates an API from a database and eliminates the need to write a separate REST API.

In this article, I will show how OdataToEntity can help eliminate the boring work of writing CRUD methods. In the last article I described how to create an OData service with minimal coding, in this article I will show how to do it without writing code at all.

This functionality is available in the OdataToEntity.EfCore.DynamicDataContext project, which is part of the OdataToEntity library. An example HTTP server is implemented as a console program that accepts a database connection string as input. Supported databases: MySql, PostgreSql, Sql Server. In addition to tables and CRUD operations on them, views, stored procedures and functions are available.

HTTP server description

The source code for the server is available on GitHub .

The server is configured through the configuration file . This is the standard Asp .net core file in which the OdataToEntity key is added.

"OdataToEntity": { "BasePath" : "api", "Provider": "sqlserver", "ConnectionString": "Server=.\\sqlexpress;Initial Catalog=OdataToEntity;Trusted_Connection=Yes;", "UseRelationalNulls": true, "InformationSchemaMappingFileName": "InformationSchemaMapping.json" } 

"BasePath" is the base path in the server URL.

"Provider" - database type, possible values ​​mysql, postgresql, sqlserver.

"ConnectionString" - string to connect to the database.

"UseRelationalNulls" - indicates whether to use the relational database semantics

when comparing zero values.

"InformationSchemaMappingFileName" is an additional setting for displaying the database in the API.

The program automatically detects procedures, functions, relationships between database tables, checking their foreign keys. I use this to embed relationships in an OData service schema. For additional customization of names, the InformationSchemaMapping.json file is a serialized class InformationSchemaMapping .

The key "Operations" describes the stored procedures and functions, "Tables" - tables and views. Properties "DbName" is the name in the database, "EdmName" is the name in the service, "Exclude" excludes the base and service object. If the stored procedure / function returns a table, then the name of the table must be set in the "ResultTableDbName" property. To change the name of the navigation property, you need to use the "Navigations" key, where the "TargetTableName" property points to the target table of the navigation property, and "NavigationName" is its name. If the table contains several foreign keys on one and the same table, then to distinguish these navigation properties, instead of "TargetTableName", you must specify "ConstraintName" - the name of the foreign key of the database. For the many-to-many property, you need to specify "ManyToManyTarget" —the name of the target table (for more information about the many-to-many implementation, see this link ).

Sample code

If you need to use this functionality in your code, add a link to the OdataToEntity.EfCore.DynamicDataContext project

 //Load our schema mappings (optional) InformationSchemaMapping informationSchemaMapping = GetMappings(); //Configure context var optionsBuilder = new DbContextOptionsBuilder<DynamicDbContext>(); optionsBuilder = optionsBuilder.UseSqlServer("Server=.\\sqlexpress;Initial Catalog=OdataToEntity;Trusted_Connection=Yes;"); IEdmModel dynamicEdmModel; //create database schema using (ProviderSpecificSchema providerSchema = new SqlServerSchema(optionsBuilder.Options)) using (var metadataProvider = providerSchema.CreateMetadataProvider(informationSchemaMapping)) { //create ef entity types manager DynamicTypeDefinitionManager typeDefinitionManager = DynamicTypeDefinitionManager.Create(metadataProvider); //Create adapter data access var dataAdapter = new DynamicDataAdapter(typeDefinitionManager); //Build OData edm model dynamicEdmModel = dataAdapter.BuildEdmModel(metadataProvider); } //Create query parser var parser = new OeParser(new Uri("http://dummy"), dynamicEdmModel); //Query var uri = new Uri("http://dummy/Orders?$expand=Customer,Items&$orderby=Id"); //The result of the query var stream = new MemoryStream(); //Execute query await parser.ExecuteGetAsync(uri, OeRequestHeaders.JsonDefault, stream, CancellationToken.None); stream.Position = 0; //Get result as string Console.WriteLine(new StreamReader(stream).ReadToEnd()); 

How it works

According to the information_schema views, the Entity Framework context is built. Context entities are descendants of the DynamicType abstract class. This class imposes restrictions on the total number of columns in the table; there should not be more than 50. The number of navigation properties should not exceed 50 for the properties from the primary key, 30 from the foreign key.

The total number of tables and views is limited to 110, this number is limited to implementations of the DynamicType class . You can increase the number of properties or classes by adding them to the source code.

The context of the Entity Framework builds an OData schema, as already described in my previous article . This schema is needed to translate a query into an expression tree (expression tree), which is passed to the EntityFramework context.

Source code structure

Solution - sln \ OdataToEntity.Test.DynamicDataContext.sln

Project - source \ OdataToEntity.EfCore.DynamicDataContext

HTTP server - test \ OdataToEntity.Test.DynamicDataContext.AspServer

Tests - OdataToEntity.Test.DynamicDataContext

Sql test database scripts - test \ sql_scripts