Entity Framework Query Samples

Devart offers you to try Entity Framework functionality of dotConnect for MySQL with Entity Framework Query Samples.

The Entity Framework Query Samples application is a test framework for different databases and data providers within ADO.NET Entity Framework. Its goal is to help you learn the features of the different query languages available in the Entity Framework, and to let you visualize how the results and the translated store queries look like.

This package is based on the original work by Microsoft available here: http://code.msdn.microsoft.com/EFQuerySamples.

Requirements

MySQL 5.0 or higher is required for Entity Framework Query Samples. To connect to MySQL you will need a Trial or Professional version of the corresponding dotConnect for MySQL installed on your system. ADO.NET Entity Framework requires .NET Framework 3.5 Service Pack 1 installed. Visual Studio 2008 Service Pack 1 is required to compile the sample.

Installation and Configuration

First, create a new copy of NortwhindEF database. The sample contains folder DB, which includes subfolders for every database with scripts or ready-to-use database files.

Second, open the App.config file and adjust connection strings for the databases you are going to use.

After that you can compile and run the sample.

Running Samples

There are 2 ways to run samples: interactive mode and batch mode.

To run samples in an interactive mode, compile and run the project without any command-line arguments. Using the tree on the left hand side, locate the interesting sample and click Run. You can run samples against different providers, by selecting them from the drop-down menu.

To run samples in the batch mode, run "SampleQueries.exe /runall /pause". It will open the console where each sample is executed sequentially and any exceptions are reported.

You can also pass "/log filename.txt" which will save results of all samples to a file. To specify different provider, pass "/connectionString cs" where "cs" is the name of the connection string defined in "SampleQueries.exe.config"

For example: 

SampleQueries.exe /log log.txt /connectionString "NorthwindEF (MySQL)" /runall /pause

MySQL Server does not support some keywords in SQL statements, such as EXCEPT and MINUS. Therefore some queries that are translated to SQL with usage of these clauses fail (some tests for relational and set operators).

Creating Entity Framework Query Samples Model with Entity Developer

You may build the same model yourself with our product Entity Developer. Here all steps are described, that you should perform to get the same model from a database.

Generating model

After you have created needed database, run Entity Developer and import the database to the model. To do it, perform the following steps.

  1. Select Create From Database from the File menu.
  2. Select Entity Framework and click Next.
    Welcome page, Database Reverse Engineering Wizard
  3. Choose database provider in the Provider drop-down list and set the required connection parameters, then click the Next button.
  4. Select the check box of the Tables node to select all tables and check boxes of EmployeesInsert, EmployeesUpdate, and EmployeesDelete functions, then click the Next button.
    Choose database objects page, Database Reverse Engineering Wizard
  5. Select Unchanged in the both Case drop-down list for classes, methods, and their members and click the Next button.
    Setup naming rules page, Database Reverse Engineering Wizard
  6. Enter namespace (for example, NorthwindEFModel), to place the model in and its name and click the Next button.
    Model properties page, Database Reverse Engineering Wizard
  7. Click the Finish button.

Autogenerated model

Now we have the model, but it is still not the same as Entity Framework Query Samples Model. It is not possible to create inheritances and complex types automatically from database, we should create them manually.

Creating Complex Type

Lets create complex type CommonAddress instead of Address, City, Region, PostalCode, and Country properties. To do this perform the following actions:

  1. Click the Address property in the Employee class, then press SHIFT and click the Country property holding the SHIFT key.
  2. Drag the selected properties out of the Employee class and drop them on the empty diagram space.
    Dragging properties.
  3. In the appeared Choose Action dialog box click New complex type and then click OK.
    Choose Action dialog box.
  4. Double-click the complex type, appeared on the diagram.
  5. In the appeared editor enter CommonAddress in the Name field and click OK.
    Complex type editor.

After this we should remap these properties in the Customer, Supplier, Order, and PreviousEmployee classes to the CommonAddress complex type. Perform the following actions:

  1. Click the Address property in the Customer class, then press SHIFT and click the Country property holding the SHIFT key.
  2. Right-click the selection and choose Migrate from the popup menu.
  3. In the appeared Choose Action dialog box click Existing complex type and then click OK.
    Choose Action dialog box.
  4. Right-click the appeared property "CommonAddress" in the Customer class and choose Edit from the popup menu.
  5. In the appeared editor enter Address in the Name field and click OK.
  6. Repeat steps 1-5 for Supplier and PreviousEmployee classes.

Inheritances

Now let's create inheritance relationships like in the Entity Framework Query Samples Model. This model contains three types of inheritances: Table Per Type with Order and InternationalOrder classes, Table Per Hierarchy with Product and DiscontinuedProduct classes, and Table Per Concrete type with Employee, CurrentEmployee, and PreviousEmployee classes.

Table Per Concrete Type hierarchy

In Table Per Concrete type hierarchy each non-abstract hierarchy class is mapped to one table. CurrentEmployee is mapped to the Employee table, PreviousEmployee class is mapped to the PreviousEmployee table. They have the same parent abstract class Employee. To create Table Per Concrete type hierarchy perform the folowing steps:

  1. Double-click the Employee class
  2. In the appeared editor choose Abstract in the Inheritance Modifier drop-down list and click OK.
    Choose Action dialog box.
  3. Click the PreviousEmployee class on the diagram and press the DELETE key.
  4. Click the Add Class to Context button on the Entity Framework Project toolbar or press ALT+C.
  5. Right-click the appeared class and choose Rename from the popup menu.
  6. Enter "PreviousEmployee" and press ENTER.
  7. Click the Employee class and then PreviousEmployee class while holding down the SHIFT key.
  8. Click the Add Inheritance to ContextModel button on the Entity Framework Project toolbar or press ALT+I
  9. Choose Table Per Concrete Type in the Type drop-down list and click OK.
    Inheritance editor.
  10. Click the Add Class to Context button on the Entity Framework Project toolbar or press ALT+C.
  11. Right-click the appeared class and choose Rename from the popup menu.
  12. Enter "CurrentEmployee" and press ENTER.
  13. Click the Employee class and then CurrentEmployee class while holding down the SHIFT key.
  14. Click the Add Inheritance to ContextModel button on the Entity Framework Project toolbar or press ALT+I
  15. Choose Table Per Concrete Type in the Type drop-down list and click OK.
  16. Right-click the Employee class and choose Mapping Details from the popup menu.
  17. In the appeared Mapping Details dialog box select <Delete> from the drop-down list in the first row of the Storage Entities grid and click OK.
  18. Right-click the CurrentEmployee class and choose Mapping Details from the popup menu.
  19. In the appeared Mapping Details dialog box select Employee from the drop-down list in the first row of the Storage Entities grid and click OK.
  20. Right-click the PreviousEmployee class and choose Mapping Details from the popup menu.
  21. In the appeared Mapping Details dialog box select PreviousEmployee from the drop-down list in the first row of the Storage Entities grid and click OK.

    22. Table per Concrete Type hierarchy.

  22. Drop the association between Employee and Territory entities. Select it and click DELETE button.
  23. Select a table EmployeesTerritories in the Project Explorer (Storage model node). Right-click it and choose Add class from the popup menu. The new association will be created.

Storage node

Table Per Type hierarchy

In Table Per Type hierarchy each hierarchy class is mapped to one table. Order class is mapped to the Order table, InternationalOrder class is mapped to the InternationalOrder table. To create Table Per Type hierarchy perform the folowing steps:

  1. Click the Add Inheritance to ContextModel button on the Entity Framework Project toolbar or press ALT+I
  2. In the appeared editor choose Order in the Base Class drop-down list and InternationalOrder in the Derived Class drop-down list click OK.
    Inheritance editor.
  3. Choose Table Per Type in the Type drop-down list and click OK
  4. Remove an association between Order and InternationalOrder classes. This association becomes redundant after inheritance releationship is defined.

    5. Invalid association deleting
  5. Drop the OrderId field from the InternationalOrder class.
  6. Right-click the InternationalOrder class and choose Mapping Details from the popup menu.
  7. Map the OrderId column to the following property.

    8. OrderID firled mapping

Table Per Hierarchy hierarchy

In Table Per Hierarchy hierarchy all hierarchy classes are mapped to the same table. To create Table Per Hierarchy hierarchy perform the folowing steps:

  1. Click the Discontinued property in the Product class and press the DELETE key.
  2. Drag the DiscontinuedDate property out of the Product class and drop it on the empty diagram space.
  3. In the appeared Choose Action dialog box click New derived class and then click OK.
  4. Double-click the class, appeared on the diagram.
  5. In the appeared editor enter DiscontinuedProduct in the Name field and click OK.
  6. Right-click the Product class and choose Mapping Details from the popup menu.
  7. In the appeared Mapping Details dialog box select Discontinued in the drop-down list of the Column column in the Conditions grid.
  8. Enter "false" into the Value column of the Conditions grid and click OK.
    Setting mapping.
  9. Right-click the DiscontinuedProduct class and choose Mapping Details from the popup menu.
  10. In the appeared Mapping Details dialog box select Discontinued in the drop-down list of the Column column in the Conditions grid.
  11. Enter "true" into the Value column of the Conditions grid and click OK.

Configuring behavior

Now we have almost the same model as Entity Framework Query Samples Model. All we need to change is to assign these methods to Employee insert, update, and delete actions. Perform the following steps to complete Entity Framework Query Samples Model:

  1. Right-click the CurrentEmployee class and choose Configure behavior from the popup menu.
  2. Choose Insert in the Behavior drop-down list.
  3. Choose the EmployeesInsert stored procedure in the Stored Procedure drop-down list.
  4. Assign the corresponding CurrentEmployee properties to the parameters in the grid below by selecting the corresponding property from the drop-down list in the Property column.
    Configuring behaviour.
  5. Choose Update in the Behavior drop-down list.
  6. Repeat the steps 3 - 4 with the EmployeesUpdate procedure.
  7. Choose Delete in the Behavior drop-down list.
  8. Choose the EmployeesDelete stored procedure in the Stored Procedure drop-down list.
  9. Assign Employeeid property to the only parameter in the grid below by selecting the corresponding property from the drop-down list in the Property column.
  10. Click OK.

Now look at the Error List window. It displays error messages and warnings of your model. After we configured behavior of the CurrentEmployee class, the following error appeared: "If an EntitySet mapping includes a function binding, function bindings must be included for all types. The following types do not have function bindings: NorthwindEFModel.PreviousEmployee." This error means that if we assigned functions to insert, update, and delete operations for one hierarchy class, we should do the same for all non-abstract classes in hierarchy.

So we will create the stub function and assign it to insert, update, and delete the operations of the PreviousEmployee class.

  1. Right-click the Stored Procedures node in the Project Explorer and choose Add Procedure from the popup menu
  2. Enter "NullFunction" into the Name box and click OK.
    Stored procedure editor.
  3. Right-click the PreviousEmployee class and choose Configure behavior from the popup menu.
  4. Choose Insert in the Behavior drop-down list.
  5. Choose the NullFunction stored procedure in the Stored Procedure drop-down list.
  6. Repeat the steps 4-5 for Update and Delete operations and click OK.

Generating code

The model is complete now. Let's generate code for it. Perform the following actions to generate the code.

  1. Double-click the NorthwindEFModel node in the Project Explorer.
  2. In the appeared Project Properties dialog box switch to the Generation tab.
    Projecr Properties dialog box.
  3. Specify the template for the code generation and folder to place generated files to.
  4. Click the Generate Code for Context Model button on the Project toolbar. A Generation Progress dialog appears, displaying the progress of the code generation.
    Generation progress.

Code is generated to the specified folder in a file with the same name as Entity Container. To add it to your Visual Studio project perform the following actions. In the Solution Explorer right-click the project, point to Add and click Existing Item. Then select the generated file in the Add Existing Item dialog.