SQL Compact and The Entity Framework


Linq to SQL Vs Entity Framework

Linq to SQL is not supported using SQL Compact, although it is possible to use Linq to SQL using SQLMetal.exe, I have a post here showing how this can be done. The Entity Framework however, is fully supported in SQL Compact CE.

Personally speaking, if I was to create a large application that is data access heavy (especially if it is multi-user), then I would still opt for ADO.NET datasets and stored procedures. There are quite a few applications that don’t require the the fuss of creating and maintaining stored procedures, and in those cases, I definitely prefer using an Object Relational Mapping tool (ORM).

Linq to SQL Development Terminated

Microsoft announced that they would stop any further development of Linq to SQL. This (as you might guess) was not received at all well in the developer community – just read the comments in the announcement – because this little ORM tool gained phenomenal popularity very quickly, mostly because it was very lightweight though still very powerful.

The Entity Framework has not been received as favourably, with many dissatisfied by the complexity and ultimately the cogency of the object model. For me, however, the Entity Framework is just an ORM tool like Linq to SQL, and contains pretty much all the functionality, so I see no point in not using it, especially since resources are continuing to be poured into it, and the same Linq queries you ran in Linq to SQL are the same you run as Linq to Entities.

Master Details Demo

For this demo (note this demo is in both C# and Visual Basic), I am going to create a simple master/details form that collects data from the Northwind database, make some changes and save those changes. This is very typical for any type of application that would use a compact database.

Create a new .NET 3.5 windows forms project and call it NorthwindTraders.

Add a new item to the project

AddNew

Choose the ADO.NET Entity Data Model and name it NorthwindModel and clock add

NorthwindModel

You should now have the Entity Data Model Wizard, choose “Generate From Database” and click “Next”

Wizard

Here you can choose the database type which can be SQL 2008, Express or SQL Compact. Since this demo is about SQL compact, change the datasource by clicking the change button

DataBaseType

And choose SQL Compact and click OK

SQLCompact

When you click “Browse to locate” a database, you should automatically open the Northwind.sdf located at C:Program FilesMicrosoft SQL Server Compact Editionv3.5Samples

BrowseForDB

I never run as administrator when developing in general (on Vista/Wiindows 7) so you will need to move this sample database to a folder where you can access it without running as administrator. I have copied the database to a folder on my C drive, select the database there and click OK.

You should now have the connection string, and set the connection string to NorthwindEntities and click “Next”.

ConnectionString

Choose the “Customers” and “Orders” tables and click “Finish”

Model

IF you double click the NorthwindModel.edmx in Solution Explorer you should have the ORM in the designer.

ModelInDesigner

Note: The designer has a slight bug in that if the scroll bar is right at the top (see red arrow above), then you cannot see the mappings, to correct this, just move the scrollbar down a little

From the Data menu, add a data source

AddDataSource

Select “Object” and select “”Next

Customers

Click “Finish” and you should now have the datasource showing

Add a SplitContainer to the form and set the orientation to “horizontal”. Drag a DataGrid from the Customers Node into the first split panel, and do the same with the related orders

MasterDetails

You should now have the two datagrids, the two binding sources and a binding navigator. Set the save button in the navigator to “Enabled”. Double click the form to create a Form_Load event and also double click the save button to create an event handler

Enabled

In the code behind of the form (note Visual Basic code is beneath)

C#

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

 

namespace NorthwindTraders

{

    public partial class Form1 : Form

    {

        NorthwindEntities context;

 

        public Form1()

        {

            InitializeComponent();

        }

        /// <summary>

        /// When the form loads we instantiate the NorthwindEntities context and perform

        /// a simple Linq query that returns all the customers in London

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void Form1_Load(object sender, EventArgs e)

        {

            context = new NorthwindEntities();

 

            var customers = from c in context.Customers.Include("Orders")

                            where c.City == "London"

                            select c;

 

            this.customersBindingSource.DataSource = customers;

        }

 

        /// <summary>

        /// Save the changes made in both DataGridviews

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void customersBindingNavigatorSaveItem_Click(object sender, EventArgs e)

        {

            this.context.SaveChanges();

 

        }

 

 

    }

}

Visual Basic

Public Class Form1

 

    Private context As NorthwindEntities

 

    ”’ <summary>

    ”’ When the form loads we instantiate the NorthwindEntities context and perform

    ”’ a simple Linq query that returns all the customers in London

    ”’ </summary>

    ”’ <param name="sender"></param>

    ”’ <param name="e"></param>

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        context = New NorthwindEntities()

 

        Dim customers = From c In context.Customers.Include("Orders") _

            Where c.City = "London" _

            Select c

 

        Me.CustomersBindingSource.DataSource = customers

    End Sub

 

    ”’ <summary>

    ”’ Save the changes made in both DataGridviews

    ”’ </summary>

    ”’ <param name="sender"></param>

    ”’ <param name="e"></param>

    Private Sub CustomersBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CustomersBindingNavigatorSaveItem.Click

        Me.context.SaveChanges()

    End Sub

End Class

As you can see, it really is quite easy to use the EntityFramework, and pretty much the same functionality in Linq to SQL is available here.

3 thoughts on “SQL Compact and The Entity Framework

  1. Dear Sir,
    Thank you for an excellent tutorial on the Enitity Framework. I am a elderly potential hobbyist programmer using a standard installation of Visual Basic Express 2010 who has been reseaching to use Linq to Sql Compact 3.5 for the last ten days, then I found your Entities Framework on the web. I was disappointed to read that MS is not keeping Linq to Sql. However, I decided to attempt to create from your guidelines the above program. I was extremely happy to find that it processed quite easily and except for some reformating of the code worked beautifully.
    As I am new to this could you possibly advise any tutorials and or Getting Started/Beginners Books on the Entitiy Framework for use with VBE2010.

  2. Hello Ken,

    Even though Microsoft stopped the development of Linq to SQL, it still contains a lot of functionality that you need, so I still use it because it is lightweight, and designed far much better than the Entity Framework. It is only when I need really advanced features that I use the Entity Framework, though if I am completely honest, I would rather use nHibernate for .NET.

    The only downside to using Linq to SQL with SQL compact, is that Visual Studio does not create the ORM for you and you have to manually create the file. I would recommend this book by Julia Lerman however (I think only the first edition is available in Visual Basic but the second edition is C# only). Julia also maintains an excellent blog as well.

    Regards,

    Ira

  3. Ira,
    Thank you kindly for your response, i think I will at this stage of my development stay with Linq to Sql using Sql Server Compact as I’ve mastered creating DBML’s.

    Regards

    Ken

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s