Possible ways of data model creation and loading

As we know, data model is a core part of any project that uses EasyQuery framework.

There are several options on how we can create it and then load when necessary.

From DbContext

If you your project uses Entity Framework (Core) then the simplest way to get the model is to load it directly from your DbContext object. To make this option available you need to reference Korzh.EasyQuery.EntityFrameworkCore.Relational (in case of EF Core) or Korzh.EasyQuery.EntityFramework6 (for Entity Framework 6).

For desktop projects (WPF or WinForms) you can simply call model.LoadFromDbContext(myDbContext) extension function.

In ASP.NET (Core) you will need to add UseDbContext call on initialization of EasyQuery middleware (in ASP.NET Core) or API Controller (in ASP.NET):

app.UseEasyQuery(options => {
    .    .    .    .
	options.UseDbContext<MyDbContextType>();
});

From DbConnection

It's possible to create a model based on information from database meta-data using some DbGate component and a special method of DbModel class: LoadFromConnection. This method maps all database tables to the model entities (and table fields to the attributes of those entities) and makes some smart tuning: hide ID fields, setup some value editors for foreign key fields, adjust the names of entities and attributes.

To make it possible you will need to perform the following 2 steps:

Step 1: Register DbGate for your database

EasyQuery library contains several DbGate components. Each such DbGate "knows" how to read meta-data for a particular database type (SQL Server, MySQL, PostreSQL, etc). So, before calling LoadFromConnection method you will need to reference some DbGate package in your project and register the corresponding DbGate class. For example, to support SQL Server you will need to reference Korzh.EasyQuery.MsSqlGate and add the following intialization code somewhere at your application start:

Korzh.EasyQuery.DbGates.SqlClientGate.Register();

In case of ASP.NET Core applicaiton you can also use RegisterDbGate method on EasyQuery services initialization in Startup.ConfigureServices method:

services.AddEasyQuery()
    .UseSqlManager()
    .AddDefaultExporters()
    .RegisterDbGate<SqlClientGate>();  //<--- this line will register DB Gate for MS SQL Server

Step 2: Load your model from some DbConnection object

As in previous case (loading model from a DbContext) for desktop projects (WPF or WinForms) you can simply call when necessary (on form initialization usually):

(model as DbModel).LoadFromConnection(myConnection);

In ASP.NET (Core) you will need to add UseDbConnectionModelLoader call on initialization of EasyQuery middleware (in ASP.NET Core) or API Controller (in ASP.NET):


    options.ConnectionString = Configuration.GetConnectionString("DefaultConnection");
    options.UseDbConnection<SqlConnection>();
	
    options.UseDbConnectionModelLoader(loaderOptions =>
        loaderOptions.IgnoreViews()
			.AddTableFilter(tableInfo => !(tableInfo.Name.StartsWith("Asp") || tableInfo.Name.StartsWith("__EF")))
    );

As you can see from the example above, you can also set up some options for the model loader. In this case our loader will ignore the views (only DB tables will be processed) and will not include the tables which names are started with Asp or __EF.

Creating model with Data Model Editor tool

Finally, you can create your model "manually" using our Data Model Editor (DME) utility, save it to some file and then load that file on your program start.

Here is the link to the setup file for DME.

After installing and running DME you need to create a new model, set up a connection to some database (use your connection string for that) and then follow the instructions that appear.

After that you can manually add/delete entities and attributes (fields), change their captions, descriptions, hide some attributes, set their value editors and make any other possible changes in your model.

As we said above, with this approach, the model is usually stored in an XML or JSON file, which is loaded when you start your application using the LoadFromXmlFile or LoadFromJsonFile (in desktop projects). For ASP.NET (Core) applications you just need to setup FileModelLoader in controller (middleware) initialization:

    options.UseModelLoader((_) => new FileModelLoader("App_Data"));

here App_Data is the path to the folder where you model XML or JSON files are stored. The loader will look for the file with a "Model ID" name and .xml or .json extension.

If the structure of your database does not change often, we recommend you use this approach because it provides greater control over the presentation of the data to the end-user. Some fields can be hidden, for others, you can change the name or assign an individual "value editor" that allows users to choose a value from some list instead of entering it manually.