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
Choose the ADO.NET Entity Data Model and name it NorthwindModel and clock add
You should now have the Entity Data Model Wizard, choose “Generate From Database” and click “Next”
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
And choose SQL Compact and click OK
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
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”.
Choose the “Customers” and “Orders” tables and click “Finish”
IF you double click the NorthwindModel.edmx in Solution Explorer you should have the ORM in the designer.
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
Select “Object” and select “”Next
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
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
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.
Ira Lukhezo's blog 