Building dynamic queries with EasyQuery and Entity Framework

1. Problem overview

Object-relational mapping (ORM) solutions have become a standard for database-related software projects last years. Entities Framework (EF) is an obvious ORM choice in .NET world since it is distributed with .NET framework so all applications built with Entity Framework can run on any computer on which the .NET Framework (starting with version 3.5 SP1) is installed.

EasyQuery suits quite well for any project which use Entity Framework. There are some peculiar features in such “collaboration” which we are going to discuss further. So, to use EasyQuery components with Entity Framework you need to solve only two tasks:

• Fill your EasyQuery data model by your Entity Framework model.
• Generate query statement which can be executed over your EF model.

Let's look at each issue separately.

2. Model loading

There are two possible ways to load your EF model into DataModel (DbModel) object.

2.1 Loading model from .edmx file

If you still use Entity Data Model (EDM) stored in some .edmx file (so-called "database first" approach) - you can use LoadFromEdmx() method of DataModel class to fill it by EDM.

Here is an example:

DbModel myModel = new DbModel();
myModel.LoadFromEdmx(“MyEntityFrameworkModel.edmx”);
DbQuery query = new DbQuery();
query.Model = myModel;

2.2. Loading model directly from DbContext.

With new “code first” style of Entity Framework you don’t have any .edmx files in your project. In such case you can load your model directly from DbContext class. Here is a code sample:

using Korzh.EasyQuery.EF;
. . . . . . . .
DbModel myModel = new DbModel();
 
var dbContext = new NorthwindContext()
myModel.LoadFromDBContext(dbContext);

So you just need to pass the type of your DbContext object as first parameter and some options in the second one (IncludeComplexTypesInParentEntity option used in the sample is quite self-descriptive).

We should note also that the second method of model loading can be used for the projects which use old EDM approach as well. You can use the DbContext class auto-generated for .edmx file.

3. Generating SQL

While EasyQuery allows you to generated Entity SQL or query via LINQ but the most efficient way will be to use good old T-SQL. Just create an instance of SqlQueryBuilder class, set default formats for generated statements (MsSqlServer in our case) and call BuildSQL method to get the result.

Code:

DbQuery dbQuery = new DbQuery();
dbQuery.Model = myModel;
  . . . . . . . . . .
SqlQueryBuilder builder = new SqlQueryBuilder(dbQuery);
builder.Formats.SetDefaultFormats(FormatType.MsSqlServer);
string sql = builder.BuildSQL();

Finally here is an example how to execute generated SQL statement over your DbContext (this task is not related directly to EasyQuery functionality but it's worth mentioning in this article):

  try {
  DataSet resultDataSet = new DataSet("myDataSet");
  DataTable resultTable = new DataTable("Result");
  resultDataSet.Tables.Add(resultTable);
  List columnNames = new List();
  using (NorthwindContext context = new NorthwindContext()) {
  var connection = context.Database.Connection;
  if (connection.State == ConnectionState.Closed)
  connection.Open();
  var command = connection.CreateCommand();
  command.CommandText = sql;
  command.CommandType = CommandType.Text;
  resultTable.Load(command.ExecuteReader());
  }
  //now you can use resultDataSet to show data in a GridView control 
  //or pass them into some report
  }
  catch (Exception e) {
  // Something wrong happened
  }