Entity Framework Visual Designer

The visual designer is inside Visual Studio. It’s a tool that let you change the Data Model visually. To be able to take a tour of the it, lets create a new Console Project and add a new Entity Framework Data Model.

Once the Console project is created, add a new item to the project.

Entity Framework Data Model Wizard Dialog

Entity Framework Data Model Wizard Dialog, step 1

From there, the second wizard step will be to select what the model will contain. We can start from a blank model or from a database. Because we want to see some functionality of the Visual Designer, let select to load the Entity Model from an existing database. For this example, I will select the Microsoft’s AdventureWorks 2008R2 SR1 database.

Entity Framework Model Content Wizard Step

Entity Framework Model Step 2

For the purpose of this exercise, when the Wizard will ask you which table you desire to import into your Entity Model file, select them all. For the moment, lets just add tables and not Views or Stored Procedures. After that you will see a file in your project called “MyEFModel.edmx”. This file if you double click on it will opens the Visual Designer.

entityframeworkVisualDesigner

Visual Designer showing Entities

When the Visual Designer is open, it’s important to have the Properties windows (View>Properties Window / F4).

Entity Properties

Lets see available properties of an Entity. To see them, click on an Entity from the Visual Designer. You should see some properties like the screenshot below.

Entity Properties

Department's properties

The two most important properties are the Name and the Entity Set Name. The name represent the Entity single unit. It’s usually the table name. For the Department, the name is Department. So data that come from this Entity will be “Departement”. If a list of department is required it will be an IEnumerable<Department>. The Entity Set Name is the identified inside the Object Context to retrieve department. A metaphor could be with a bag of item. The bag name is the Entity Set Name and items inside that bag are the Name. So, if I want to have the 10 department with an unique identifier under 10, I will need to go in the bag called “DepartmentList” to get a collection of “Department”. By the way, the DepartmentList name is a modification of mine. Initially, the Entity Set Name was the same as the name : department.

Department List Example

All department with an unique identifier under 10.

The second line that has a variable named listOfDepartment is in fact a IQueryable collection where the T is Department (the Name of the Entity).

var db = new AdventureWorks_DataEntities();
            var listOfDepartment = db.DepartmentList.Where(dept => dept.DepartmentID <= 10);
            foreach (var d in listOfDepartment)
            {
                Console.WriteLine(string.Format("{0}:{1}",d.DepartmentID,d.Name));
            }

The line #2 could have been:

[/csharp]
var db = new AdventureWorks_DataEntities();
IQueryable listOfDepartement = db.DepartmentList.Where(dept => dept.DepartmentID <= 10); foreach (var d in listOfDepartement) { Console.WriteLine(string.Format("{0}:{1}",d.DepartmentID,d.Name)); } [/csharp]

Entity Attributes Properties

It’s also possible to set properties on attribute. When the model is generated from the database, all the hard work is already done. When you do it the other way, by creating the model objects first, you will need to create those attributes by hand and will be converted into table columns. Depending of the type of the attribute, property options will vary.

If the attribute is the Entity Key, this one will have special option like StoreGeneratedPattern.

If the attribute is a relationship attribute, called Navigation Property other properties like the association is required. Furthermore, the association’s multiplicity is set within this attribute.

An other type of attribute is a simple value. The Value Designer call them Scalar property. This kind of entity attribute contain value that will be later be a simple column without any key.

The last kind of attribute that an entity can have is Complexe Property. Complexe Property is a way to organize multiple attribute together. In fact, behind, it’s like having multiple attribute but from the object perspective, it will be inside a sub-object.

Association Properties

Association attribute contain the multiplicity of each endpoint, it contains also if cascade delete is required and so on.

This is for a small wrap-up of the Visual Designer of Entity Framework 4.0 🙂

Microsoft Entity Framework in theory

Microsoft Entity Framework (EF) is available with the Microsoft .Net Framework 3.5 since the first service pack. This is pretty interesting for WPF (Windows Presentation Foundation) form, Console or traditional Windows form application that does not need to add an additional assembly into their setup package.

What is Entity Framework in short?

In short, the Entity Framework is an ORM. An ORM is an Object Relational Mapping. The ORM goal is to map Business Logic (also know as Model classes) to the persistent storage. It lets the developer concentrate on model objects without caring about how to load/save them from the database. But, Entity Framework also can generate model objects from an existing database or on the other side can generate the database tables. This is very powerful and time saver.

How does Entity Framework works?

Entity Framework work under the hood with XML files. In fact, Visual Studio will hide you all the complexity of those XML files.

Communication

Entity Framework communicate to the database using multiple communication channel. This give some flexibility to the developer about how to access the persisted data. One way is directly to the EntityClient Provider using Entity Sql Query. While it’s very powerful, the two others way are simpler and are mostly used. The second one is by using Entity SQL Query. This is like SQL statement but for the Entity Framework. It’s also called ESQL. The third and last way to communicate is by using the Linq channel. Linq to Entities is very similar to Linq to Sql or Linq to Object but with some restrictions. So, the last two, HSQL and LinqToEntities pass through an additional layer called Object Service. This Object Service will be instanced in your code and queries will be done with it. The Object Service role is to translate the easy syntax into a Command Tree to be able to execute query via the ADO.NET Data provider. As you may already know, the Entity Framework is using ADO.NET technologies. So we went through the call from the code to the database. But, what happen once the database has executed the query? The data is sent back to the Object Service inside an EntityDataReader and the developer will receive all rows inside a IEnumerable. The T is the type of the entity requested to the Object Service.

How to convert Entity Framework that use file database to server database

When using Entity Framework, this one create a copy of the database when generating from the database and create a .mdf for the project. This database is used. This is good for small project but once you want to use Sql Profiler or to use the real database, it’s not.

To do the switch, only one place need to be changed and it’s the app.config or if you are in a web environment the web.config.

Here is the generated connection string generated and after the modified one.

    <add name="NorthWindContainer" 
      connectionString="metadata=res://*/FromNorthWind.csdl|res://*/FromNorthWind.ssdl|res://*/FromNorthWind.msl;
      provider=System.Data.SqlClient;
      provider connection string='Data Source=.\SQLEXPRESS;
                    AttachDbFilename=|DataDirectory|\northwnd.mdf;
                    Integrated Security=True;
                    Connect Timeout=30;
                    User Instance=True;
                    MultipleActiveResultSets=True'" 
      providerName="System.Data.EntityClient" />
  <add name="NorthWindContainer"
		 connectionString="metadata=res://*/FromNorthWind.csdl|res://*/FromNorthWind.ssdl|res://*/FromNorthWind.msl;
     provider=System.Data.SqlClient;
     provider connection string='Data Source=.\SQLEXPRESS;
		                Initial Catalog=Northwind;
                    Integrated Security=True;
                    Connection Timeout=60;
		    multipleactiveresultsets=true'" 
     providerName="System.Data.EntityClient" />
    

This simple modification do the trick and you are ready to go 🙂

Installing the AdventureWorks Database

AdventureWorks is a free database used . You can find it for free at CodePlex.com. The installation is well documented on MSDN website but can cause you some problems in few steps that I will try to smooth for you. First of all, before anything, be sure that you have the latest version of SQL Server. At this moment the lastest version is SQL Server 2008 R2. This can be found at Microsoft. If you don’t, you may receive this message:

The database cannot be opened because it is version 661. This server supports version 612 and earlier. A downgrade path is not supported.

Once the latest version of Microsoft SQL Server installed (this can be the Express Edition), you can install the AdventureWorks Setup executable file. This will install files located at : C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

From there, the next step is to attach the database to your SQL Server. This is where the server could not be able to open the database because of version. If you still continue to have this error, the second solution can be to download older version of AdventureWorks.

Install Windows of AdventureWorks database

AdventureWorks Installation Process

From there, steps at MSDN is what need to follow. When attaching the database you may receive this error message:

Unable to open the physical file “C:\sql\adventureworks\AdventureWorks_Data.mdf”. Operating system error 5: “5(error not found)”. (.Net SqlClient Data Provider)

This error occur when the sp_attach_db is executed or when the attach is done via the Microsoft Sql Management Studio.

SQLCMD -S (local)\sqlexpress -E
exec sp_attach_db @dbname=N'AdventureWorks', @filename1=N'C:\sql\adventureworks\AdventureWorks_Data.mdf', @filename2=N'C:\sql\adventureworks\AdventureWorks_log.ldf'
GO

To solve this issue you need to add to the directory the Network Service user with Read and Write access. Also, do not forget to start the command prompt or the Microsoft Sql Management Studio as Administrator.

Entity Framework explicit loading, lazy loading and eager loading

Entityframework set lazing loading to false as a default mechanism for loading its entity data. In other words, this mean that the default value is set to eager loading or explicit loading. This behavior can be modified by the developer if a need is required by changing the Lazy Loading Enable of the conceptual model’s property or by code by changing the the OptionContext property LazyLoadingEnabled.

To understand correctly how the lazy loading works, let get an example from the NorthWind database. The model generated from this database give us a Customer entity that contain orders (of type Order).

var db = new NorthWindContainer(); //Instantiate the Object Context
db.ContextOptions.LazyLoadingEnabled = false; //Default value
var collectionData = db.Customers.First().Orders;
Console.WriteLine("All orders from the first customer using lazy loaded to " + db.ContextOptions.LazyLoadingEnabled);
foreach (var order in collectionData)
{
	Console.WriteLine(order.OrderID);
}
Console.WriteLine(collectionData.Count + " shown");

In the example above, line 3 get all orders from the first customer. The lazy loading of the Object Context is set the false (the default value). The console will show nothing from the loop and a count of 0. This is because the lazy loading is not enabled.

SELECT TOP (1) 
.[CustomerID] AS [CustomerID], 
.[CompanyName] AS [CompanyName], 
.[ContactName] AS [ContactName], 
.[ContactTitle] AS [ContactTitle], 
.[Address] AS [Address], 
.[City] AS [City], 
.[Region] AS [Region], 
.[PostalCode] AS [PostalCode], 
.[Country] AS [Country], 
.[Phone] AS [Phone], 
.[Fax] AS [Fax]
FROM [dbo].[Customers] AS 

As you can see, the SQL reflect the result that we have. Nothing to take the orders.

In fact, in this mode, to be able to get all the data a new line must have been written between the request to get all orders and the display. The following code contain the Load() method that will go to the database to load orders.

var db = new NorthWindContainer();
db.ContextOptions.LazyLoadingEnabled = false; //Default value
var collectionData = db.Customers.First().Orders;
collectionData.Load(); //Explicit loading
Console.WriteLine("All orders from the first customer using lazy loaded to " + db.ContextOptions.LazyLoadingEnabled);
foreach (var order in collectionData)
{
	Console.WriteLine(order.OrderID);
}
Console.WriteLine(collectionData.Count + " shown");

This mean that when using the default value (eager loading/explicit loading) the value loaded is only the one of the object and not the object of the class. To load the object of the class, the developer needs to use Load() on the desired object. This can be interesting if the object is big and you do not need to load the whole object.

The Load method is called. This mean that it’s an explicit loading. Eager loading does not require the call to Load method. Further example will show you. For the moment, let check the SQL statement generated to see how it reflect the change.

SELECT TOP (1) 
.[CustomerID] AS [CustomerID], 
.[CompanyName] AS [CompanyName], 
.[ContactName] AS [ContactName], 
.[ContactTitle] AS [ContactTitle], 
.[Address] AS [Address], 
.[City] AS [City], 
.[Region] AS [Region], 
.[PostalCode] AS [PostalCode], 
.[Country] AS [Country], 
.[Phone] AS [Phone], 
.[Fax] AS [Fax]
FROM [dbo].[Customers] AS 

exec sp_executesql N'SELECT 
[Extent1].[OrderID] AS [OrderID], 
[Extent1].[CustomerID] AS [CustomerID], 
[Extent1].[EmployeeID] AS [EmployeeID], 
[Extent1].[OrderDate] AS [OrderDate], 
[Extent1].[RequiredDate] AS [RequiredDate], 
[Extent1].[ShippedDate] AS [ShippedDate], 
[Extent1].[ShipVia] AS [ShipVia], 
[Extent1].[Freight] AS [Freight], 
[Extent1].[ShipName] AS [ShipName], 
[Extent1].[ShipAddress] AS [ShipAddress], 
[Extent1].[ShipCity] AS [ShipCity], 
[Extent1].[ShipRegion] AS [ShipRegion], 
[Extent1].[ShipPostalCode] AS [ShipPostalCode], 
[Extent1].[ShipCountry] AS [ShipCountry]
FROM [dbo].[Orders] AS [Extent1]
WHERE [Extent1].[CustomerID] = @EntityKeyValue1',N'@EntityKeyValue1 nchar(5)',@EntityKeyValue1=N'ALFKI'

Using Lazy Loading

But, this is pretty manual task and if want to use lazy loading instead of explicit loading it’s possible. This will give the control of loading objects of the class to the system.

The C# code will be like this:

var db = new NorthWindContainer(); //Instantiate the Object Context
db.ContextOptions.LazyLoadingEnabled = true; //Default value
var collectionData = db.Customers.First().Orders;
Console.WriteLine("All orders from the first customer using lazy loaded to " + db.ContextOptions.LazyLoadingEnabled);
foreach (var order in collectionData)
{
	Console.WriteLine(order.OrderID);
}
Console.WriteLine(collectionData.Count + " shown");

This produce exactly the same output for the SQL.

SELECT TOP (1) 
.[CustomerID] AS [CustomerID], 
.[CompanyName] AS [CompanyName], 
.[ContactName] AS [ContactName], 
.[ContactTitle] AS [ContactTitle], 
.[Address] AS [Address], 
.[City] AS [City], 
.[Region] AS [Region], 
.[PostalCode] AS [PostalCode], 
.[Country] AS [Country], 
.[Phone] AS [Phone], 
.[Fax] AS [Fax]
FROM [dbo].[Customers] AS 

exec sp_executesql N'SELECT 
[Extent1].[OrderID] AS [OrderID], 
[Extent1].[CustomerID] AS [CustomerID], 
[Extent1].[EmployeeID] AS [EmployeeID], 
[Extent1].[OrderDate] AS [OrderDate], 
[Extent1].[RequiredDate] AS [RequiredDate], 
[Extent1].[ShippedDate] AS [ShippedDate], 
[Extent1].[ShipVia] AS [ShipVia], 
[Extent1].[Freight] AS [Freight], 
[Extent1].[ShipName] AS [ShipName], 
[Extent1].[ShipAddress] AS [ShipAddress], 
[Extent1].[ShipCity] AS [ShipCity], 
[Extent1].[ShipRegion] AS [ShipRegion], 
[Extent1].[ShipPostalCode] AS [ShipPostalCode], 
[Extent1].[ShipCountry] AS [ShipCountry]
FROM [dbo].[Orders] AS [Extent1]
WHERE [Extent1].[CustomerID] = @EntityKeyValue1',N'@EntityKeyValue1 nchar(5)',@EntityKeyValue1=N'ALFKI'

Caching

Also, if we run the all examples in the same method we will realize that even if the collectionData is accessed twice that the database will be called once because the object context has cached the collection. Here is a snippet of 3 calls. One with the lazy loading to false with a load call. One other with the lazy loading to true and one with false without the load. Interestingly, the last one will display value because the collection was already loaded.

var db = new NorthWindContainer();
db.ContextOptions.LazyLoadingEnabled = false; //Default value
var collectionData = db.Customers.First().Orders;
collectionData.Load();
Console.WriteLine("All orders from the first customer using lazy loaded to " + db.ContextOptions.LazyLoadingEnabled);
foreach (var order in collectionData)
{
	Console.WriteLine(order.OrderID);
}
Console.WriteLine(collectionData.Count + " shown");

db.ContextOptions.LazyLoadingEnabled = true;
collectionData = db.Customers.First().Orders;
Console.WriteLine("All orders from the first customer using lazy loaded to " + db.ContextOptions.LazyLoadingEnabled);
foreach (var order in collectionData)
{
	Console.WriteLine(order.OrderID);
}
Console.WriteLine(collectionData.Count + " shown");

db.ContextOptions.LazyLoadingEnabled = false; //Default value
collectionData = db.Customers.First().Orders;
Console.WriteLine("All orders from the first customer using lazy loaded to " + db.ContextOptions.LazyLoadingEnabled + " back to False");
foreach (var order in collectionData)
{
	Console.WriteLine(order.OrderID);
}
Console.WriteLine(collectionData.Count + " shown");


Console.ReadLine();

Advanced example and eager loading

Previous example does not show the real power of eager loading. Let go back quickly to the lazy loading code.

var db = new NorthwindEntities();
db.ContextOptions.LazyLoadingEnabled = true; //Default value

foreach (var customer in db.Customers)
{
	Debug.WriteLine("Customer " + customer.CustomerID);
	foreach (var order in customer.Orders)
	{
		Debug.WriteLine("|-->Order " + order.OrderID);
	}
}

This will produce 1 SQL to retrieve all Customers and then 1 SQL for each order. This is a problem called “N+1”. You will have 1 initial SQL hit and additional hit for each customer. This lead to huge performance problem. The magic of lazy loading suddenly disappear. Of course, lazy loading is useful in some moment like accessing only 1 object and its objects like in all previous example. The same thing occur with explicit loading. To make it works, the Load should have been called between the two foreachs.

var db = new NorthwindEntities();
db.ContextOptions.LazyLoadingEnabled = false;

foreach (var customer in db.Customers)
{
	Debug.WriteLine("Customer " + customer.CustomerID);
        customer.Orders.Load(); //Explicit loading
	foreach (var order in customer.Orders)
	{
		Debug.WriteLine("|-->Order " + order.OrderID);
	}
}

The N+1 problem is still there. With eager loading, this would result to a single query. To notify which object to load inside the class, the use of Include is required. Here is an example:

var db = new NorthwindEntities();
db.ContextOptions.LazyLoadingEnabled = true; 
var customers = db.Customers.Include("Orders");
foreach (var customer in customers)
{
	Debug.WriteLine("Customer " + customer.CustomerID);
	foreach (var order in customer.Orders)
	{
		Debug.WriteLine("|-->Order " + order.OrderID);
	}
}

Line 2 and line 3 have changed. The line 2 tell to use eager loading while the line 3 indicates that it needs to load to object Orders inside the Customers. This produce a single SQL.

SELECT 
[Project1].[C1] AS [C1], 
[Project1].[CustomerID] AS [CustomerID], 
[Project1].[CompanyName] AS [CompanyName], 
...
FROM ( SELECT 
	[Extent1].[CustomerID] AS [CustomerID], 
	[Extent1].[CompanyName] AS [CompanyName], 
	[Extent1].[ContactName] AS [ContactName], 
	...
	1 AS [C1], 
	[Extent2].[OrderID] AS [OrderID], 
	[Extent2].[CustomerID] AS [CustomerID1], 
	[Extent2].[EmployeeID] AS [EmployeeID], 
	...
	FROM  [dbo].[Customers] AS [Extent1]
	LEFT OUTER JOIN [dbo].[Orders] AS [Extent2] ON [Extent1].[CustomerID] = [Extent2].[CustomerID]
)  AS [Project1]
ORDER BY [Project1].[CustomerID] ASC, [Project1].[C2] ASC

This LEFT OUTER JOIN (also known as LEFT JOIN) do what you would have been doing in SQL to get additional information if available.

How to add model object to a third party Silverlight user control?

It can happen that we do not have the control of the source code of a user control and we still want to add an object into it. This scenario happened to me recently when I had to add a collection of object and know when they are changed.

This is where dependency property come at the rescue. I won’t explain in this post how dependency property works but show you how to use it with a collection and an event that will be trigged if this collection change.

First, you may wonder why having this collection of object in the user control is wanted. You are right that this collection should be in the view model of the application, and it is. In fact, this property will be binded to the view model and every time an object is added or removed from the collection in the view model, this one will notify with the binding the depencendy property that will raise our event to redraw the user control.

First, this require that you create a new user control that will contain the user control that does not have the property desired.

<UserControl x:Class="MyNameSpace.MyUserControl"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" 
	xmlns:ThirdPartyNamespace="clr-namespace:ThirdPartyNamespace;assembly=ThirdPartyUserControlHere" 
    d:DesignHeight="300" d:DesignWidth="400">

	<ThirdPartyNamespace:ThirdPartyUserControlHere />
</UserControl>

In the code behind of the created user control, we need to add the dependency property.

public static readonly DependencyProperty MyCustomProperty = DependencyProperty.Register(
                 "MyCustomProperty"
		  , typeof (ObservableCollection<MyObject>)
		  ,typeof (MyUserControl)
		 ,new PropertyMetadata(null,OnMyObjectCollectionPropertyChanged)
		 );

The line 3 contains the first parameters that is the property name you want to access from your control. This property will be bound to the third party user control. The line 4 if the type of the property added. In our example, it’s a collection of MyObject. The line 5 is the type of the user control we add this property. This should be the name of the new user control you just added. The line 6 is the callback when this property change.

The next step is to add the collection of MyObject and the callback.

       public ObservableCollection<MyObject > MyObjects
        {
            get { return (ObservableCollection<MyObject>) GetValue(MyCustomProperty); }
            set { SetValue(MyCustomProperty, value); }
        }

        private static void OnMyObjectCollectionPropertyChanged(DependencyObject o, DependencyPropertyChangedEventArgs e)
        {
            ((MyUserControl) o).OnMyObjectCollectionPropertyChanged(e);
        }

        private void OnMyObjectCollectionPropertyChanged(DependencyPropertyChangedEventArgs e)
        {
            //Redraw code here!
        }

The Xaml of the custom user control that envelop the third party user control can now be bound to the view model.

   <x:MyCustomUserControl
                                 DataContext="{Binding Path=MyObjectViewModel, Mode=TwoWay}"
                                 Shapes="{Binding Path=MyRealObjectsCollection, Mode=TwoWay}"
                                 />

Debugging trace with Silverlight 4

Sometime it can be useful to get some tracing line when debugging. For quick trace, I usually use System.Diagnostics.Debug.WriteLine method. This is still working with Silverlight. Also, the System.Diagnostics.Debugger.Log method can also log into the Output windows (Under Debug) of Visual Studio.

System.Diagnostics.Debug.WriteLine("~~~~~ WriteLine ~~~~~");
System.Diagnostics.Debugger.Log(0, null, "########### Log ##########\n");