Creating an Entity Framework Application (SQL Server Compact)

Creating an Entity Framework Applic...

Collapse All

This topic provides step-by-step instructions on how to create Entity Framework applications that use the SQL Server Compact database as a data source.

To create a new Entity Framework application

In Visual Studio, on the File menu, point to New, and then select Project.
In the Project Types list of the New Project dialog box, expand the programming language you will use and select Visual C# or Visual Basic.
In the Templates list, select Console Application.
Provide a name (such as SQLCompactEDMProject) and location for your project, and then click OK.
To generate the Entity Data Model for the Northwind.sdf, copy the Northwind.sdf from the folder %ProgramFiles%\Microsoft SQL Server Compact Edition\v3.5\Samples to the folder that your project exists.
On the Project menu, click Add new item.
In the Templates pane, select ADO.NET Entity Data Model.
Type Northwind.edmx for the model name and then click Add.
The first page of the Entity Data Model Wizard is displayed.
In the Choose Model Contents dialog box, select Generate from database. Then click Next.
Click the New Connection button.
In the Connection Properties dialog box, click the Change button in the Data Source. Select Microsoft SQL Server Compact 3.5, browse to the Northwind.sdf and then click OK.
The Choose Your Data Connection dialog box is updated with your database connection settings.
Ensure that the Save entity connection settings in App.Config as: checkbox is checked and the value is set to NorthwindEntities. Then click Next.
In the Choose Your Database Objects dialog box, clear all objects, expand Tables, and select the Customers as a table object.
Type NorthwindModel for the Model Namespace.
Click Finish to complete the wizard.
The wizard does the following:
1. Adds references to the System.Data.Entity.dll, System.Runtime.Serialization.dll, and System.Security.dll assemblies.
2. Generates the Northwind.edmx file that defines the EDM.
3. Creates a source code file that contains the classes that were generated based on the EDM. You can view the source code file by expanding the .edmx file in Solution Explorer.
4. Creates an App.Config file.
Double-click the project's application configuration file (app.config) and make sure that provider=System.Data.SqlServerCe.3.5 in the connection string.
In the code page for your application, add the following using statements:
C#:
```
using NorthwindModel;
```
Visual Basic:
```
Imports SQLCompactEDM.NorthwindModel
```
Note that the name of the model corresponds to the value of the namespace specified in the Northwind.edmx file.
Copy the following code example to your code file. Then, compile and run.


The System.Data.Entity.dll assembly is a part of the SP1 release of .NET Framework version 3.5. Managed reference topics for the System.Data.Entity assembly are available in the Entity Framework documentation.

Example

The following code example provides three simple Entity Framework queries against the SQL Server Compact Northwind.sdf database:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.EntityClient;
using System.Data;
using NorthwindModel;

namespace SQLCompactEDMProject
{
  class SSC_EntityFrameworkExample
  {
    static void Main(string[] args)
    {
      Console.WriteLine(
        "SQL Server Compact and Northwind Entity Framework Sample:");
      try
      {
        // Establish a connection to the underlying data provider by 
        // using the connection string specified in the config file.
        using (EntityConnection connection = 
             new EntityConnection("Name = NorthwindEntities"))
        {
          // Open the connection.
          connection.Open();

          // A simple query that demonstrates 
          // how to use Entity SQL for entities.
          Console.WriteLine("\nEntity SQL Query:");
          ESQL_Query(connection);

          // A simple query that demonstrates 
          // how to use LINQ to Entities.
          // A new connection is established by using the connection 
          // string in the App.Config file.
          Console.WriteLine("\nLINQ To Entity Query:");
          LINQ_To_Entity_Query();

          // A simple query that demonstrates how to use ObjectContext
          // on data objects with an existing connection.
          Console.WriteLine("\nObject Query:");
          ObjectQuery(connection);

          // Close the connection.
          connection.Close();
       }
     }
     catch (Exception ex)
     {
       Console.WriteLine(ex.Message);
     }
   }

  // A simple query that demonstrates how to use 
  // Entity SQL query for entities.
  private static void ESQL_Query(EntityConnection entityConnection)
  {
    EntityCommand entityCommand = entityConnection.CreateCommand();
    entityCommand.CommandText =
      @"Select Cust.Customer_Id as Id from NorthwindEntities.Customers as Cust order by Cust.Customer_Id";

     EntityDataReader entityDataReader =
     entityCommand.ExecuteReader(CommandBehavior.SequentialAccess);

     while (entityDataReader.Read())
     {
       for (int i = 0; i < entityDataReader.FieldCount; i++)
         Console.Write(entityDataReader[i].ToString() + "\t");
       Console.WriteLine();
     }
  }

  // A simple LINQ query on the Customers entity set in the Northwind 
  // Context.
  // The code example creates a new Northwind Context based on the 
  // settings provided in the App.Config File.
  private static void LINQ_To_Entity_Query()
  {
    using (NorthwindEntities nwEntities = new NorthwindEntities())
    {
      IQueryable<string> customers =
         from c in nwEntities.Customers select c.Company_Name;

      foreach (String c in customers)
      {
        Console.WriteLine(c);
      }
   }
  }

  // The ObjectContext provides access to the collections of data used 
  // by applications. 
  // The following code shows how to open a connection to the 
  // ObjectContext named NorthwindEntities. 
  // With the application configuration file in scope the connection 
  // can be opened with one line of code: 
  // NorthwindEntities nwEntities = new NorthwindEntities(entityConnection).
  private static void ObjectQuery(EntityConnection entityConnection)
  {
     using (NorthwindEntities nwEntities = new 
                  NorthwindEntities(entityConnection))
     {
        foreach (Customers c in nwEntities.Customers)
        {
          Console.WriteLine(c.Company_Name);
        }
      }
  }
  }
}

Visual Basic

Imports System.Data.EntityClient
Imports SQLCompactEDM.NorthwindModel

Module Module1
    Sub Main()
        Console.WriteLine("SQL Server Compact and Northwind Entity Framework Sample:")
        Try
            Using connection As EntityConnection = _
                New EntityConnection("Name = NorthwindEntities")
                ' Open the connection.
                connection.Open()

                ' A simple query that demonstrates how to use ESQL for entities.
                Console.WriteLine(vbNewLine & "ESQL Query:")
                ESQL_Query(connection)

                ' A simple query that demonstrates how to use LINQ to Entities.
                ' A new connection is established by using the
                ' connection string in the App.Config file.
                Console.WriteLine(vbNewLine & "LINQ To Entity Query:")
                LINQ_To_Entity_Query()

                ' A simple query that demonstrates how to use ObjectContext
                ' on data objects with an existing connection.
                Console.WriteLine(vbNewLine & "Object Query:")
                ObjectQuery(connection)

                ' Close the connection.
                connection.Close()
            End Using
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
    End Sub

    ' A simple query that demonstrates how to use ESQL for entities.
    Private Sub ESQL_Query(ByVal entityConnection As EntityConnection)
        Dim entityCommand As EntityCommand = entityConnection.CreateCommand
        entityCommand.CommandText = _
            "Select Cust.Customer_Id as Id from NorthwindEntities.Customers as Cust order by Cust.Customer_Id"

        Dim entityDataReader As EntityDataReader = _
            entityCommand.ExecuteReader(CommandBehavior.SequentialAccess)

        Do While entityDataReader.Read
            Dim i As Integer
            For i = 0 To entityDataReader.FieldCount - 1
                Console.Write((entityDataReader.Item(i).ToString & vbTab))
            Next i
            Console.WriteLine()
        Loop
    End Sub

    ' A simple LINQ query on the Customers entity set in the Northwind Context.
    ' The code example creates a new Northwind Context based on the 
    ' settings provided in the App.Config File.
    Private Sub LINQ_To_Entity_Query()
        Using nwEntities As NorthwindEntities = New NorthwindEntities
            Dim customers = From c In nwEntities.Customers Select c.Company_Name

            For Each c In customers
                Console.WriteLine(c)
            Next
        End Using
    End Sub

    ' The ObjectContext provides access to the collections of data used by applications. 
    ' The following code shows how to open a connection to the ObjectContext named NorthwindEntities. 
    ' With the application configuration file in scope the connection can be opened with one line of code: 
    ' NorthwindEntities nwEntities = new NorthwindEntities(entityConnection).
    Private Sub ObjectQuery(ByVal entityConnection As EntityConnection)
        Using nwEntities As NorthwindEntities = New NorthwindEntities(entityConnection)
            Dim c As Customers
            For Each c In nwEntities.Customers
                Console.WriteLine(c.Company_Name)
            Next
        End Using
    End Sub
End Module

Concepts

Entity Framework (SQL Server Compact)