Skip to main content
Previous section   

Using the Caché Entity Framework Provider

Entity Framework is an object-relational mapper that enables .NET developers to work with relational data using domain-specific objects. It eliminates the need for most of the data-access code that developers usually need to write. Caché includes the Caché Entity Framework Provider which enables you to use Entity Framework 6 technology to access a Caché database. (If you are using Entity Framework 5, ask your Intersystems representative for instructions.) For more information on the .NET Entity Framework, see http://www.asp.net/entity-framework.

This chapter contains the following sections:

This section describes three approaches to getting started with Entity Framework.

Setting Up Caché Entity Framework Provider

Follow the instructions in this section to install and set up Caché Entity Framework Provider.

System Requirements

To use Caché Entity Framework Provider, you need the following software:

  • Visual Studio 2013 (Professional or Ultimate) with Update 5 (or later update)

  • Caché Entity Framework Provider distribution, located in the directory installdir\dev\dotnet\bin\v4.0.30309. This directory includes the following files, which you use in the setup instructions:

    • CacheEF.zip, installation files.

    • CacheEF.zip\setup.cmd, which installs the DLLs InterSystems.Data.CacheClient.dll and InterSystems.Data.CacheVSTools.dll.

    • CacheEF.zip\Nuget\InterSystems.Data.Entity6.4.5.0.0.nupkg which installs Caché Entity Framework Provider.

    • CacheEF.zip\CreateNorthwindEFDB.sql which is used to create a sample database.

Run Setup and Set Variables

Follow these steps:

  1. Extract the contents of installdir\dev\dotnet\bin\v4.0.30309\CacheEF.zip into a folder (in the same directory as the zip file) called CacheEF. Run \CacheEF\setup.cmd.

  2. In Windows, select All Programs > Visual Studio 2013 > Visual Studio Tools.

  3. In the displayed Windows Explorer folder, right-click Developer Command Prompt for VS2013 > Run as Administrator and enter:

    devenv /setup
    Copy code to clipboard

    This command repopulates this key from HKEY_LOCAL_MACHINE:

    HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\12.0_Config //VS 2013

Copy Files to Visual Studio

Copy the two files from the CacheEF directory to Visual Studio:

  • SSDLToCacheSQL.tt

  • GenerateCacheSQL.Utility.ttinclude

from the directory <cacheinstalldir>\dev\dotnet\bin\v4.0.30319\CacheEF\Templates to the directory C:\Program Files (x86)\Microsoft Visual Studio 1x.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen

Connect Visual Studio to Caché Server

To connect Visual Studio to a Caché instance, follow the steps below:

  1. Open Visual Studio and select View > Server Explorer.

  2. Right-click Data Connections and select Add Connection. In the Add Connection Dialog:

    1. Select Data source as Cache Data Source (.Net Framework Data Provider for Cache)

    2. Select Server

    3. Enter Username and password. Click Connect.

    4. Select a namespace from the list. Click OK.

Configure Caché Nuget Local Repository

Follow these steps to configure the Package Manager to find the local Nuget repository:

  1. Create a directory as a Nuget repository. You might call it Nuget Repository. You could put it anywhere – a good choice might be <yourdoclibraryVS2013>\Projects (where Visual Studio stores projects by default).

  2. Copy the file <installdir>\dev\dotnet\bin\v4.0.30319\CacheEF\Nuget\InterSystems.Data.Entity6.4.5.0.0.nupkg and paste it into your Nuget repository directory. Click OK.

  3. In Visual Studio, select Project > Manage Nuget Packages > Settings > Package Manager > Package Sources.

  4. Click the plus sign+. Enter the path that contains InterSystems.Data.Entity6.4.5.0.0.nupkg. Click OK

Getting Started with Entity Framework

This section describes three approaches to getting started with Entity Framework.

  • Code First Start by defining data classes and generate a database from the class properties.

  • Database FirstDatabase First — Start with an existing database. You can set one up by following the steps in the section Set Up a Sample Database. Then use Entity Framework to generate code for a web application based on the fields of that database.

  • Model First Model First — Start by creating a database model showing entities and relationships. Then generate a database from the model.

The sections below show examples of each of these approaches.

Code First

This section shows an example of how to write code to define data classes and then generate tables from the class properties. The example in this section is based on the Entity Framework Tutorial from EntityFrameworkTutorial.net (http://www.entityframeworktutorial.net/code-first/simple-code-first-example.aspx).

  1. Create a new project in Visual Studio 2013 with FILE > New > Project. With a Template of Visual C# and Console Application highlighted, enter a name for your project, such as CodeStudents. Click OK

  2. Add Caché Entity Framework Provider to the project: Click TOOLS > Nuget Package Manager > Manage Nuget Packages for Solution. Expand Online > Package Source. Caché Entity Framework Provider 6 is displayed. Click Install > Ok > I Accept. Wait for the installation to complete and then click Close.

  3. Compile the project with Build > Build Solution.

  4. Tell the project which system to connect to by identifying it in the App.config file as follows. From the Solution Explorer window, open the App.config file. Add a <connectionStrings> section (like the example shown here) as the last section in the <configuration> section after the <entityFramework> section.

    Note:

    Check that the server, port, namespace, username, and password are correct for your configuration.

    <connectionStrings>
       <add 
          name="SchoolDBConnectionString" 
          connectionString="SERVER = localhost;
             NAMESPACE = USER;
             port=1972;
             METADATAFORMAT = mssql;
             USER = _SYSTEM;
             password = SYS;
             LOGFILE = C:\\Users\\Public\\logs\\cprovider.log;
             SQLDIALECT = cache;"
          providerName="InterSystems.Data.CacheClient"
       />
    </connectionStrings>
    
    Copy code to clipboard
  5. In the Program.cs file, add

    using System.Data.Entity;
    using System.Data.Entity.Validation;
    using System.Data.Entity.Infrastructure;
    Copy code to clipboard
  6. Define classes:

    public class Student
    {
       public Student()
       {
       }
       public int StudentID { get; set; }
       public string StudentName { get; set; }
       public DateTime? DateOfBirth { get; set; }
       public byte[] Photo { get; set; }
       public decimal Height { get; set; }
       public float Weight { get; set; }
       public Standard Standard { get; set; }
    }
    Copy code to clipboard
    public class Standard
    {
       public Standard()
       {
       }
       public int StandardId { get; set; }
       public string StandardName { get; set; }
       public ICollection<Student> Students { get; set; }
    }
    Copy code to clipboard
    public class SchoolContext : DbContext
    {
       public SchoolContext() : base("name=SchoolDBConnectionString")
       {
       }
       public DbSet<Student> Students { get; set; }
       public DbSet<Standard> Standards { get; set; }
    }
    Copy code to clipboard

    Check that class SchoolContext points to your connection in App.config.

  7. Add code to Main.

    using (var ctx = new SchoolContext())
    {
       Student stud = new Student() { StudentName = "New Student" };
       ctx.Students.Add(stud);
       ctx.SaveChanges();
    }
    Copy code to clipboard
  8. Compile and run.

Check the namespace (USER in this case). You see three tables created: dbo.Standards, dbo.Students (which has a new student added), and dbo._MigrationHistory (which holds information about table creation).

Set Up a Sample Database

If you want to set up a sample database for use with the Database First section, follow the steps in this section. These steps set up and load the sample database CreateNorthwindEFDB.sql.

  1. In the Management Portal, select System > Configuration > Namespaces and click Create New Namespace.

  2. Name your namespace NORTHWINDEF.

    1. For Select an Existing Database for Globals, click Create New Database. Enter NORTHWINDEF as the database and <installdir>\mgr\EFdatabase as the directory. Click Next and Finish

    2. For Select an Existing Database for Routines, select NORTHWINDEF from the dropdown list.

    3. Click Save.

  3. In the Management Portal, select System > Configuration > SQL and Object Settings > General SQL Settings.

    1. In the SQL tab, enter the Default SQL Schema Name as dbo.

    2. In the SQL tab, select Support Delimited Identifiers (default is on)

    3. In the DDL tab, select all items.

    4. Click Save.

  4. Select System > Configuration > SQL and Object Settings > TSQL Compatability Settings

    1. Set the DIALECT to MSSQL.

    2. Set QUOTED_IDENTIFIER to ON.

    3. Click Save.

  5. In a Terminal window, change to your new namespace with

    zn “NORTHWINDEF”
    Copy code to clipboard
  6. If this is not the first time you are setting up the database, purge existing data with:

    do $SYSTEM.OBJ.DeleteAll("e") d Purge^%apiSQL()
    Copy code to clipboard
  7. If you have not already done so, using an unzip program, extract files from installdir\dev\dotnet\bin\v4.0.30319\CacheEF.zip to a folder called CacheEF.

  8. To load the ddl, enter

    do $SYSTEM.SQL.DDLImport("MSSQL","_system","<installdir>\dev\dotnet\bin\v4.0.30319\CacheEF\CreateNorthwindEFDB.sql")
    Copy code to clipboard

In the Server Explorer window, you can expand the Caché server entry to view NorthwindEF database elements: Tables, Views, Function, Procedures. You can examine each element, retrieve Data for Tables and Views, Execute Functions and Procedures. If you right-click an element and select Edit, Studio opens showing corresponding class and position on requested element if applicable.

Database First

To use the database first approach, start with an existing database and use Entity Framemaker to generate code for a web application based on the fields of that database.

  1. Create a new project in Visual Studio 2013 with FILE > New > Project of type Visual C# > Console Application > OK.

  2. Click TOOLS > Nuget Package Manager > Manage Nuget Packages for Solution. Expand Online > Package Source, which lists Caché Entity Framework Provider 6. Click Install > Ok > Accept the license > Close.

  3. Compile the project with Build > Build Solution.

  4. Select PROJECT > Add New Item > Visual C# Items > Ado.NET Entity Data Model. You can give your model a name. Here we use the default of Model1. Click Add.

  5. In the Entity Data Model Wizard:

    1. Select EF Designer from database > Next

    2. In the Choose Your Data Connection screen, the data connection field should already be to your Northwind database. It doesn’t matter whether you select Yes, Include or No, exclude to the sensitive data question.

    3. On the bottom of screen you can define a connection settings name. The default is localhostEntities. This name is used later on.

    4. In the Choose Your Database Objects and Settings screen, answer the question Which Database objects do you want to include in your model? by selecting all objects: Tables, Views, and Stored Procedures and Functions. This includes all Northwind tables.

    5. Click Finish.

    6. In several seconds, you’ll see a Security Warning. Click OK to run the template.

    7. Visual Studio may display an Error List with many warnings. You can ignore these.

  6. For a model name of Model1, Visual Studio generates multiple files under Model1.edmx – including a UI diagram as Model1.edmx itself, classes representing tables under Model1.tt, and context class localhostEntities in Model1.Context.tt->Model1.Context.cs.

    In the Solution Explorer window, you can inspect Model1.Context.cs. The constructor Constructer public localhostEntities() : base("name=localhostEntities") points to App.Config connection string:

    <connectionStrings>
       <add 
          name="localhostEntities"
          connectionString="metadata=res://*/Model1.csdl|
                res://*/Model1.ssdl|
                res://*/Model1.msl;provider=InterSystems.Data.CacheClient;
             provider connection string=&quot;
             ApplicationName=devenv.exe;
             ConnectionLifetime=0;
             ConnectionTimeout=30;
             ConnectionReset=False;
             Server=localhost;
             Namespace=NORTHWINDEF;
             IsolationLevel=ReadUncommitted;
             LogFile=C:\Users\Public\logs\cprovider.log;
             MetaDataFormat=mssql;
             MinPoolSize=0;
             MaxPoolSize=100;
             Pooling=True;
             PacketSize=1024;
             Password=SYS;
             Port=1972;
             PreparseCacheSize=200;
             SQLDialect=cache;
             Ssl=False;
             SoSndBuf=0;
             SoRcvBuf=0;
             StreamPrefetch=0;
             TcpNoDelay=True;
             User=_SYSTEM;
             WorkstationId=DMITRY1&quot;" 
          providerName="System.Data.EntityClient" 
       />
    </connectionStrings>
    
    Copy code to clipboard
  7. Compile your project with BUILD > Build Solution.

Below are two program samples that you can paste into Main() in Program.cs:

You can traverse a list of customers using:

using (var context = new localhostEntities()) {
   var customers = context.Customers;
   foreach (var customer in customers) {
      string s = customer.CustomerID + '\t' + customer.ContactName;
   }
}
Copy code to clipboard

You can get a list of orders for CustomerID using:

using (var context = new localhostEntities()) {
   var customerOrders = from c in context.Customers
      where (c.CustomerID == CustomerID)
         select new { c, c.Orders };

   foreach (var order in customerOrders) {
      for (int i = 0 ; i < order.Orders.Count; i++) {
         var orderElement = order.Orders.ElementAt(i);
         string sProduct = "";
         //Product names from OrderDetails table 
         for (int j = 0; j < orderElement.OrderDetails.Count; j++)  
         {
            var product = orderElement.OrderDetails.ElementAt(j);
            sProduct += product.Product.ProductName;
            sProduct += ",";
         }
         string date = orderElement.OrderDate.ToString();
      }
   }
}
Copy code to clipboard

Model First

Use the model first approach by generating a database model basd on the diagram you created in the Database First section. Then generate a database from the model.

This example shows you how to create a database that contains two entities,

  1. Look at the Entity Framework UI edmx diagram Model1.edmx. In a blank area of the diagram, right-click and select Properties.

  2. Change DDL Generation Template to SSDTLtoCacheSQL.tt.

  3. Compile Project.

  4. In a blank area of the diagram, right-click and select Generate Database From Model. After the DDL is generated, click Finish.

  5. Studio creates and opens the file Model1.edmx.sql.

  6. Import your table definitions into Caché by executing the following command in a terminal:

      do $SYSTEM.SQL.DDLImport("MSSQL","_system","C:\\<myPath>\\Model1.edmx.sql")
    Copy code to clipboard