Working with data model in EasyQuery

Data model - is a user-friendly representation of your database which is used by different EasyQuery components to show available entities, their attributes, conditional operators and the lists of available values during the query building process. Before starting to add EasyQuery visual controls/widgets to your application - you will need to choose the you load the data model for your DB.

There are several different options available:

  1. If you your project uses Entity Framework (Core) then the best option for you will be to load the model directly from your DbContext object. For the legacy projects with the database first approach you can also load your model from a .edmx file.
  2. There is a possibility to load the model directly from the DB meta-data (with LoadFromConnection method)
  3. An option for LINQ query builders only: you can load the data model either from some context class or from a separate model class (for data filtering scenarios)
  4. You can create your model "manually" using Data Model Editor utility and save it to some XML or JSON file. After that you can load your model in your application using either LoadFromXmlFile or LoadFromJsonFile methods.

Below we will discuss all these options more in details.

1. Load your model from DbContext

This is the most preferable and the most easy way to deal with a data model if your project uses Entity Framework (Core). There are two simple steps:

1.1 Install EasyQuery package for EF (Core)

For .NET Core project it will be Korzh.EasyQuery.EntityFrameworkCore.Relational package.

For .NET Framework 4 use Korzh.EasyQuery.EntityFramework6 pacage

1.2/1 LoadFromDbContext (for desktop applications)

If your project is a desktop application for Windows Forms or WPF then you just need to call LoadFromDbContext(yourDbContextObject) somewhere on intialization stage of your app. Of course, you will need to initialize your DbContext object before that.

1.2/2 UseDbContext extension method for ASP.NET 4.x projects

If you work with ASP.NET 4.x project you can call UseDbContext method for EasyQueryOptions object in ConfigureEasyQueryOptions method of your API Controller. Example:

protected override void ConfigureEasyQueryOptions(EasyQueryOptions options)
{
    options.UseManager<EasyQueryManagerSql>();
        .    .    .    .    .  

    var dbContext = ApplicationDbContext.Create();
    options.UseDbContext(dbContext);
}

1.2/3 UseDbContext extension method for ASP.NET Core projects

In case of ASP.NET Core projects it's even simplier. You just need to add UseDbContext<DbContextType> call right during the initialization of EasyQuery middleware in Startup.Configure:

public void Configure(IApplicationBuilder app, IHostingEnvironment env)
{
        .    .    .    .    .  
    
	app.UseEasyQuery(options => {
        .    .    .    .    .
        options.UseDbContext<AppDbContext>();
    });
	
        .    .    .    .    .  
}

Additionally, there is an overload of this method which automatically excludes all ASP.NET Core Identity tables from the data model:

 app.UseEasyQuery(options => {
     .    .    .    .    .
     options.UseDbContextWithoutIdentity<AppDbContext>();
 });

2. Load model from a DB connection

This method fills your model by the meta data of your database.

2.1 Installing the packages

To use this approach you will need to packages:

2.2 Register DbGate class

First of all you need to register the DB Gate of your choice first somewhere at application's start.

SqlClientGate.Register();

This method is static so it can be callled only once in the static constructor of your form/page class (where LoadFromConnection method will be used).

2.3/1 LoadFromConnection method (desktop projects)

For WPF and Windows Forms project just call LoadFromConnection method of DbModel class somewhere at your form initialization code. Here is an example:


.    .    .    .

model.LoadFromConnection(yourConnectionObject);

2.3/2 UseDbConnectionLoader for ASP.NET (Core) projects

For ASP.NET (not Core) project you will need to call UseDbConnectionModelLoader in ConfigureEasyQueryOptions method of your API Controller.

protected override void ConfigureEasyQueryOptions(EasyQueryOptions options)
{
    options.UseManager<EasyQueryManagerSql>();
        .    .    .    .    .  

    options.ConnectionString = ConfigurationManagerWrapper.GetConnectionString("DefaultConncetion");
    options.UseDbConnection<SqlConnection>();
    options.UseDbConnectionModelLoader();
}

For ASP.NET Core projects you use the same piece of code but in EasyQuery middleware intialization:

 app.UseEasyQuery(options => {
     .    .    .    .    .
    options.ConnectionString = Configuration.GetConnectionString("DefaultConncetion");
    options.UseDbConnection<SqlConnection>();
    options.UseDbConnectionModelLoader();
 });

NB: Please note that loading the model directly from DB meta data can be quite time consuming operation. If you don't plan to modify the structure of your DB a lot - it's better to create your model file using DME and then load the model from that file.

3. Loading data model from entity types

This method is useful for data-fitlering scenarios first of all (like the one in our demo project) when you need to give your users a possibility to filter the records taken from one table via DynamicQuery extension method.

So, this way of model loading is used together with LinqQueryBuilder (for desktop applications) or EasyQueryManagerLinq (for ASP.NET or ASP.NET Core). All this functionality is provided with Korzh.EasyQuery.Linq NuGet package so you need to install it first.

You can find an example of such approach on GitHub (this is ASP.NET Core demo but it looks very similar for ASP.NET MVC application as well).

For desktop project you just need to use LoadFromEntityType extension method for your model and then call DynamicQuery method for the IQueryable for specified entity:

.   .   .   .   .
//on model initialization
model.LoadFromEntityType(typeof(Order));

.    .    .   .
//later, when we want to apply the query
var orders = dbContext.Orders
        .OrderBy(o => o.Id)
		.DynamicQuery<Order>(query);
		

4. Creating data model with Data Model Editor (DME)

This option gives you the most control over the model's content. As usual, this control comes with a price, which in this case is flexability - once created it will be quite difficult to update your data model accoring to the changes in your database. So, we suggest you to avoid this approach especially if you are using Entity Framework (Core) for all database-related operations.

Here is what you need to do to create your model with DME:

4.1. Install and run Data Model Editor

Here is the link for DME setup file. Just click on it to initiate the download process and then, when finished, open dme_setup.exe to start the installation process.

After the installation you can run DME through the Start menu. For Windows 7 you find it in All Programs | Korzh EasyQuery | Data Model Editor.

For Windows 10 just press Windows button on your keyboard and start to type “Data Model Editor”.

4.2. Create new model and establish a database connection

  • Use “Model | New..” menu item
  • Type the name of your model (e.g. “MyModel”)
  • Choose the type of database connection (e.g. “MS SQL Server (native)”)
  • Enter the connection string. You can use “Build connection string” to construct the connection string visually.
  • Test your database connection using “Test connection” button.
  • Press OK to finish model creating

Data Model Editor - new model

4.3. Fill the list of tables

After pressing OK and closing the previous dialog you will be asked to add database tables into your model. Click “Yes” to agree and the Select Tables dialog will appear.

Data Model Editor - fill tables list

Select the tables you want to add into your model (select all of them if you don't have any limitations) and press OK to add them.

DME will offer you to add the links automatically using information about foreign keys or (if foreign keys are not defined in you DB) a special algorithm which searches for field pairs in all added tables (e.g. table1.customerID → table2.customerID). Select “Yes” to start that process. When it is finished you can check added links for each table in “Links” list box.

4.5. Save your data model into XML

That's all. The initial setup has been finished. Use “Model | Save..” menu item to save your model into an XML file or JSON file.

Data Model Editor - save model

After that you can load this XML or JSON file in your application with LoadFromXmlFile() or LoadFromJsonFile() methods of DbModel class.