C# Sealed Method

The keyword sealed is used in class definition to prevent being derived from. When it’s apply to methods, it mean that the method cannot be overrided.

Well, in fact, method can only be overrided when the virtual keyword is used so why sealed is required? It’s required when a class derive from a class that had a virtual method. This one will override and an overrided method can be also overrided. To prevent that, the override method can use the sealed keyword.

class A
{
   public virtual void F() {      Console.WriteLine("A.F");   }
   public virtual void G() {      Console.WriteLine("A.G");   }
}
class B: A
{
   sealed override public void F() {      Console.WriteLine("B.F");   } 
   override public void G() {      Console.WriteLine("B.G");   } 
}
class C: B
{
   override public void G() {      Console.WriteLine("C.G");   } 
}

Model Defined Function with Entity Framework

Entity Framework let you create Model Defined Function that is function that will be executed server side.

Let say that you you have a table with 3 columns : idProduct, Quantity, Price. You want to have the total amount for each rows. In Sql you would have done something like

SELECT idProduct, Quantity, Price, Quantity*Price as Total
FROM Product

You could also get this total with a Linq to Entity query like

var allRows = db.Products.Select(prod=>;new Product(prod.idProduct, prod.Quantity, prod.Price, prod.Quantity * prod.Price);

The problem is that with bigger mathematics function that may require more than 2 fields on a lot of objects, this would require you to get a lot of data for the calculus. If you just need the total, instead of returning the price, quantity, etc you just need to use the model defined function and you are ready.

Defining the Model Defined Function

The first step is to open the Entity Model (.edmx) file in XML. The second step is to search for the CSDL section (you can search for “edmx:ConceptualModels”). Inside this, you need to add your function inside the schema tag.

Here is an example:

<Function Name="Total" ReturnType="Decimal">;
   <Parameter Name="p" Type="NorthwindModel.Product" />; 
   <DefiningExpression>; 
      (p.Price * p.Quantity)
   </DefiningExpression>; 
</Function>;

The Parameter Name is the name used in the calculus and the Type is the type of this parameter. This must be a type of the Entity Model. The mapping will be then done to the table in the database later. In the DefiningExpression you could use some SQL function like CAST.

The third step is the add some C# code to be able to call this new function. This is where the EDMFunction attribute comes in action. This attribute need to be placed over a static method that will have the same return type of the one defined in the function attribute ReturnType. Also, the parameter need to be of the same type defined in the parameter element of the function.

   [EdmFunction("Northwind", "Total")] 
   public static decimal Total(Product product) 
   { 
      throw new NotSupportedException("Totalcan only be used in a LINQ to Entities query"); 
   } 

This static method can be placed where you want. You can insert this method in a class that hold all those EDMFunctions but the the best practice is to partial class the related class. This is perfect for a class generated by the Entity Framework because even if you synchronize it wont be erased. It’s also good if you have POCO class because it won’t dirty it with persisted functions. Do not forget to add the System.Data.Objects.DataClasses namespace.

How to use EDMFunction?

The Entity Model Function is defined inside the CSDL tag, it’s also defined in the C# and finally we can call it. As you have seen, this method is only a shell and does not have any code inside. It can only be executed on the server side.

Here is an example:

var result = (from p in db.Products
              select new 
                    { 
                       p.ProductID, 
                       DetailTotal = MyStaticClass.Total(p) 
                    }).ToList(); 

And here you go!

Persistence-ignorant Object

What is a persistence-ignorant object?

It’s an object that is not aware of any persistence. It doesn’t know if it will be persisted with a database (or which one), neither if it will be saved in a XML file or a binary one. It’s ignorant of that information. A POCO object is a persistence-ignorant class.

POCO mean “Plain Old CLR Object”

You can have POCO or peristence-ignorant object with Entity Framework.

How to use Poco object with Entity Framework 4

I have put in the title Entity Framework 4 because since version 4 it has a great new addition. This new feature is that POCO object does not require to use any interface inheritance. Also, no meta attribute is required to be added to your classes. So, how does the entities map to your Poco classes? Well, the mapping is done with the name of the entity and the class. These two must be the same. If you have a classe named “Region” than you need an entity name “Region”. So, this mean that you still need to have a conceptual model with a .edmx file. Also, you do not want the entity model to create the classes since you already have them. To do, you need to clear in the entity file (.edmx) the Custom Tool property. As said previously, the mapping is done with the name. In the case you have in your project 2 classes with the same name but with a different namespace, this will confuse the mapper and an exception will be thrown. It’s a good practice to put all your poco classes in the same project with the Entity Framework file.

Example

Create a new project and add a new Entity Framework Model file. You could choose to create your entities manually, but to be faster, let create them from the database. Lets just select the Region table. Do not forget to go in the entity framework’s model properties to remove the Custom Tool.

Remove the Custom Tool of the Entity Framework Properties

By turning this off, no classes generation will be done.

The last step is to create the Object Context. This is required because we have removed previously the generation of it. To do, you need to create a new class that inherit the base class “ObjectContext”. Don’t forget to add “using System.Data.Objects;” at the top of the file to be able to see ObjectContext.

So, here is the code.

Region class:

    public class Region
    {
        public Int32 RegionID { get; set; }
        public string RegionDescription { get; set; }
    }

The Object Context class:

 public class NorthwindObjectContext:ObjectContext
    {
        private ObjectSet<Region> _Region;

        public NorthwindObjectContext() : base("name=NorthwindEntities", "NorthwindEntities")
        {
            this.ContextOptions.LazyLoadingEnabled = true;
            
        }

        public ObjectSet<Region> Regions
        {
            get
            {
                if ((_Region == null))
                {
                    _Region = base.CreateObjectSet<Region>("Region");
                }
                return _Region;
            }
        }
    }

Finally, to test, I added a page with a paragraph and showed the first region description:

 NorthwindObjectContext db = new NorthwindObjectContext();
 TextBlock.InnerText = db.Regions.Where(region => region.RegionID == 1).FirstOrDefault().RegionDescription;

They are two errors that can appear to you if you try this code :

Mapping and metadata information could not be found for EntityType

This message indicates that you have not removed the Custom Tool of the entity framework model properties.

The specified default EntityContainer name ‘NorthwindEntities1’ could not be found in the mapping and metadata information.
Parameter name: defaultContainerName

This mean the the container name is not the name of container generated by the entity model. If your model is from the database, it should be the same as the connection string name. If you want to know exactly where is the container name, you need to go in the csdl file. This is inside the entity model. To see, open the .edmx file with a XML editor and go to the CSDL section. You will see a EntityContainer element.

Entity Framework Container's Name

They are two others details that you must know when using POCO object with Entity Framework and it’s concerning complex object. As you know, complex objects are in fact object inside object instead of CLR type. Those complex object must be build as class and not struct. Also, you cannot use inheritance with complex type. For example, if your Company class contain a list of Employee you cannot have the Employee derive from Person.

From here you should be all good to use Entity Framework and Poco object!

Linq To Sql .dbml file

To create a new Linq To Sql classes to communicate with your database you need to add a new item of Linq To Sql classes types in Visual Studio.

Create new Linq to Sql classes

Once created, the next step is to drag and drop table into the Visual Designer. This will generate Linq To Sql data class. You can also drag and drop stored procedure to be used.

When done, you need to save and you can after that instantiate the Linq to Sql’s data context.

 
var dataContext = new MapperDataContext();
dataContext.UpdateRegion(1, "Estern");

The process of creating a Linq to Sql data context will create a .dbml file. This file contains all tables dropped into the visual designer.

This file is a XML file that contain all definitions of the table and the stored procedure you added into the Visual Designer.

Here is a snippet of the .dbml of Northwind database with a custom stored procedure UpdateRegion.

   <Table Name="dbo.Region" Member="Regions">
    <Type Name="Region">
      <Column Name="RegionID" Type="System.Int32" DbType="Int NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
      <Column Name="RegionDescription" Type="System.String" DbType="NChar(50) NOT NULL" CanBeNull="false" />
      <Association Name="Region_Territory" Member="Territories" ThisKey="RegionID" OtherKey="RegionID" Type="Territory" />
    </Type>
  </Table>
 ...
  <Function Name="dbo.UpdateRegion" Method="UpdateRegion">
    <Parameter Name="id" Type="System.Int32" DbType="Int" />
    <Parameter Name="txt" Type="System.String" DbType="VarChar(50)" />
    <Return Type="System.Int32" />
  </Function>

But that’s not all, the .dbml come with a .dbml.layout file. This file is also a XML but a little bit more complicate to understand. Here is a short section of the file.

 <classShape Id="40fcd11c-862f-4d4c-9d0f-b7b810cf15f4" absoluteBounds="6.75, 7.5, 2, 1.1939925130208344">
      <DataClassMoniker Name="/MapperDataContext/Region" />
      <nestedChildShapes>
        <elementListCompartment Id="59d75784-613d-41c6-b53c-10f870c17964" absoluteBounds="6.765, 7.96, 1.9700000000000002, 0.63399251302083326" name="DataPropertiesCompartment" titleTextColor="Black" itemTextColor="Black" />
      </nestedChildShapes>
    </classShape>

This esoteric is in fact just the information of the visual representation of all data classes of the visual designer. You should never have to look it.

The last file that you have under the .dbml is *.dbml.designer.cs that contain the class that you will create an instance from. This file inherit of System.Data.Linq.DataContext and contains all methods generated to correspond to the classed generated from the tables dropped into the Visual Designer. This file contains multiple classes. You will have the DataContext class but also a single class for each of the tables. Here is an example of the code generated for the small table “Regions” of Northwind database.

[global::System.Data.Linq.Mapping.TableAttribute(Name="dbo.Region")]
	public partial class Region : INotifyPropertyChanging, INotifyPropertyChanged
	{
		
		private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);
		
		private int _RegionID;
		
		private string _RegionDescription;
		
		private EntitySet<Territory> _Territories;
		
    #region Extensibility Method Definitions
    partial void OnLoaded();
    partial void OnValidate(System.Data.Linq.ChangeAction action);
    partial void OnCreated();
    partial void OnRegionIDChanging(int value);
    partial void OnRegionIDChanged();
    partial void OnRegionDescriptionChanging(string value);
    partial void OnRegionDescriptionChanged();
    #endregion
		
		public Region()
		{
			this._Territories = new EntitySet<Territory>(new Action<Territory>(this.attach_Territories), new Action<Territory>(this.detach_Territories));
			OnCreated();
		}
		
		[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_RegionID", DbType="Int NOT NULL", IsPrimaryKey=true)]
		public int RegionID
		{
			get
			{
				return this._RegionID;
			}
			set
			{
				if ((this._RegionID != value))
				{
					this.OnRegionIDChanging(value);
					this.SendPropertyChanging();
					this._RegionID = value;
					this.SendPropertyChanged("RegionID");
					this.OnRegionIDChanged();
				}
			}
		}
		
		[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_RegionDescription", DbType="NChar(50) NOT NULL", CanBeNull=false)]
		public string RegionDescription
		{
			get
			{
				return this._RegionDescription;
			}
			set
			{
				if ((this._RegionDescription != value))
				{
					this.OnRegionDescriptionChanging(value);
					this.SendPropertyChanging();
					this._RegionDescription = value;
					this.SendPropertyChanged("RegionDescription");
					this.OnRegionDescriptionChanged();
				}
			}
		}
		
		[global::System.Data.Linq.Mapping.AssociationAttribute(Name="Region_Territory", Storage="_Territories", ThisKey="RegionID", OtherKey="RegionID")]
		public EntitySet<Territory> Territories
		{
			get
			{
				return this._Territories;
			}
			set
			{
				this._Territories.Assign(value);
			}
		}
		
		public event PropertyChangingEventHandler PropertyChanging;
		
		public event PropertyChangedEventHandler PropertyChanged;
		
		protected virtual void SendPropertyChanging()
		{
			if ((this.PropertyChanging != null))
			{
				this.PropertyChanging(this, emptyChangingEventArgs);
			}
		}
		
		protected virtual void SendPropertyChanged(String propertyName)
		{
			if ((this.PropertyChanged != null))
			{
				this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
			}
		}
		
		private void attach_Territories(Territory entity)
		{
			this.SendPropertyChanging();
			entity.Region = this;
		}
		
		private void detach_Territories(Territory entity)
		{
			this.SendPropertyChanging();
			entity.Region = null;
		}
	}

The most interesting part is to notice the inheritance of two interfaces. The INotifyPropertyChanging and INotifyPropertyChanged interface that notify when a property is changing.

To conclude, Linq to Sql dbml file is an auto-generated file that you should never really need to look the code. The Visual Designer is powerful enough to handle all modifications possible with Linq to Sql.

Entity Framework mapping to stored procedure

Sometime logic are stored in a stored procedure and is still required to call it instead of using the normal entity framework way to update, delete or save. Fortunately, the entity framework let you change the basic behavior of the auto generated sql for those operations by Sql Stored Procedure call.

An other scenario is that you may want to simply call a stored procedure without having to map them to a specific entity. This can called function import. This can return almost anything from the scalar, to a collection or a complex type. Even a call to a void stored procedure is possible.

Mapping Insert, Update, Delete

Let’s start with the mapping of the 3 basic Sql functions. The first thing to do is to go to the visual designer and right click on the entity you want to map the sql queries.

Mapping Context Dialog

Context dialog for mapping stored procedure

This will popup the “Mapping Details”. From there, it will be possible to select a function for each functions. If nothing is present in the dropdown, you’ll need to add the stored procedure to the entity model. To do, right click on the model, select to generate the model from the database and select under Stored Procedure the desired one. After, come back to the mapping details.

To test, lets use the Microsoft Northwind database and add inside the Sql server manager this new stored procedure:


CREATE PROCEDURE UpdateRegion
(	
	@id INT
    ,@txt VARCHAR(50)
)
AS
BEGIN
  UPDATE region
  SET RegionDescription = @txt
  WHERE RegionID = @id
END

After, lets add it into the entity model and map the stored procedure to the update function of the region table.

Visual Studio's mapping update screen

Mapping update function with a custom stored procedure

Also, you do not need to modify all of the 3 functions, only those you want.

To see the difference. Lets try to change the region without the stored procedure mapped and with to see how the generated SQL change.

var db = new NorthWindContainer();
var region = db.Region.First();
Console.WriteLine("The first region description is : " + region.RegionDescription);
region.RegionDescription = "Est";
db.SaveChanges();
region = db.Region.First();
Console.WriteLine("The first region description is : " + region.RegionDescription);
Console.ReadLine();

This produce without the stored procedure mapped:

exec sp_executesql N'update [dbo].[Region]
set [RegionDescription] = @0
where ([RegionID] = @1)
',N'@0 nchar(50),@1 int',@0=N'Est',@1=1

And with:

exec [dbo].[UpdateRegion] @id=1,@txt='Est'

Before going to the next part of this post, in the mapping details theirs 2 columns that we have not talk yet. The first one is “Use Original Value” and the second one is “Rows Affected Parameter”. The original value is a boolean that is to false by default. If you change it to true, this will pass to the database the original loaded value of the entity. This can be used in situation where multiple concurrences could have changed the value and you want the original one. The second columns is the “Rows Affected Parameter”. This one can be used if you have an integer parameter with an output specification in the stored procedure that will return the number of rows that has been changed. In the example above, that would be the value 1 that would have been passed if we had changed the stored procedure to have an additional parameter of type Int ( @paramReturnValue integer OUTPUT).

Executing a function in Sql Server with Entity Framework

The second way to use stored procedure is to call the stored procedure from the object context. To create the mapping, this time, it requires to go in the Model Browser and to open the Stored Procedure folder. From there, the context menu of the right click let you select the desired stored procedure.

Add direct call to stored procedure or function

Once added, the stored procedure is set under the Function Imports of the Entity Framework Model.

Result of adding a stored procedure

When it’s added, you can do the call by using directly the object context followed by the name of the function. At anytime you can change the name of it by going in the Model Browser and going in the property of the function.

db.UpdateRegion(1, "Estern!");

If the stored procedure return a complex entity, the wizard will let you create the complex entity and you will be able to use it from the code. If it’s a scalar, the wizard will let you select the good one and you will be able to use it.

Conceptual schema definition languague (CSDL)

The Conceptual schema definition language (CSDL) is an XML file generated while compiling a conceptual entity model (EDMX). If you do not see the .csdl file in your output folder (bin folder), it might be because the conceptual entity model is set to embedded this file. This can be easily changed.

The CSDL file is in fact a XML with three distinct sections. If we open the NorthWind database .csdl file (the conceptual entity model has been generated from the Microsoft Northwind database) we can see these 3 sections:

The 3 part of CSDL

The 3 part of CSDL file

The first section is the Entity Container. This section contain some information concerning the global container. In the example I created with the NorthWind database, I had chosen to call the NorthWind Conceptual Model “NorthWinContainer”. This is the name that we would use inside the project to get the Object Context.

<EntityContainer Name="NorthWindContainer" annotation:LazyLoadingEnabled="true">

Inside the code would have been called this way :

var db = new NorthWindContainer();
var collectionData = db.Territories;

The second attribute of the XML is the LazyLoading that is enable in that case. This option in code can be found in the ContextOption.

var db = new NorthWindContainer();
Console.WriteLine(“Lazy loading : ” + db.ContextOptions.LazyLoadingEnabled);

This value can be set also in the Conceptual Entity Model.

Lazy Loading Property of the Conceptual Entity Modal

This first part of the CSDL file contains also all entities and associations definition. It’s not in this part of the files that properties of theses entities are defined. In fact, it contains only the name and namespace. For the associations, it also only contain the two entity related.

<EntitySet Name="Categories" EntityType="northwndModel.Categories" />
<EntitySet Name="CustomerDemographics" EntityType="northwndModel.CustomerDemographics" />
<EntitySet Name="Customers" EntityType="northwndModel.Customers" />
...
...
<AssociationSet Name="FK_Products_Categories" Association="northwndModel.FK_Products_Categories">
  <End Role="Categories" EntitySet="Categories" />
  <End Role="Products" EntitySet="Products" />
</AssociationSet>
....
....

The second section of the CSDL file contain more meat about the entity. The unique identifier (key) of the entity is defined there and also all properties. Each property contains its meta data like the type, if the value can be nullable and other meta data depending of the type. For example, an integer does not have a MaxLength attribute but a String does.

  <EntityType Name="Categories">
    <Key>
      <PropertyRef Name="CategoryID" />
    </Key>
    <Property Name="CategoryID" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Identity" />
    <Property Name="CategoryName" Type="String" Nullable="false" MaxLength="15" Unicode="true" FixedLength="false" />
    <Property Name="Description" Type="String" MaxLength="Max" Unicode="true" FixedLength="false" />
    <Property Name="Picture" Type="Binary" MaxLength="Max" FixedLength="false" />
    <NavigationProperty Name="Products" Relationship="northwndModel.FK_Products_Categories" FromRole="Categories" ToRole="Products" />
  </EntityType>
  <EntityType Name="CustomerDemographics">
    <Key>
      <PropertyRef Name="CustomerTypeID" />
    </Key>
    <Property Name="CustomerTypeID" Type="String" Nullable="false" MaxLength="10" Unicode="true" FixedLength="true" />
    <Property Name="CustomerDesc" Type="String" MaxLength="Max" Unicode="true" FixedLength="false" />
    <NavigationProperty Name="Customers" Relationship="northwndModel.CustomerCustomerDemo" FromRole="CustomerDemographics" ToRole="Customers" />
  </EntityType>

The last section of the file contains detail about the association previously described in the first section. The association name concord with the AssociationSet_FK’s name of the first section. This will defined both ending point and their multiplicity. Also, the property reference is defined.

  <Association Name="FK_Products_Categories">
    <End Role="Categories" Type="northwndModel.Categories" Multiplicity="0..1" />
    <End Role="Products" Type="northwndModel.Products" Multiplicity="*" />
    <ReferentialConstraint>
      <Principal Role="Categories">
        <PropertyRef Name="CategoryID" />
      </Principal>
      <Dependent Role="Products">
        <PropertyRef Name="CategoryID" />
      </Dependent>
    </ReferentialConstraint>
  </Association>

To conclude, this file should not really be modified by hand. Visual Studio 2010 visual designer is easier to use, less error prone and can save you a lot of time. Also, if your database is already defined, all the schema can be pull from it.

Entity Framework XML file : CSDL, SSDL, and MSL

Entity Framework is built around 3 XML files. The CSDL, SSDL, and MSL.

The CSDL acronym is for “Conceptual schema definition language”. This file describe the model object.

The SSDL acronym is for “Store schema definition language” and define the storage model.

The MSL acronym is for “Mapping specification language” and do the bridge between the CSDL and SSDL or in other words map the model and the storage.

Theses files are handled by Entity Framework and for most of the time won’t be required to be edited by hand. Visual Studio 2010 has a create Visual Designer that let the developer go through a graphical user interface to create the model, to edit it and also to map everything.

Nevertheless, it’s always good good to know what’s going on under the hood. To get those 3 files, the project with the entity model must have been compiled with the option to display the meta data of the conceptual model into the Bin folder. By default, all these 3 meta data files are embedded in the assembly. To change the embedded to an external output, you need to open the Visual Designer of the Entity model. Click anywhere in a blank spot (not an entity or an association) and open the property windows. One property of the ConceptualEntityModel is called “Metadata Artifact Processing” and by default is set to “Embed in Output Assembly”. You need to change this to “Copy to Output Directory”.

Property of the Conceptual Entity Model to output the 3 XML files

This will output the CSDL, SSDL and MSL files in the bin folder.

Debug directory with the 3 XML files

For more information about the CSDL, SSDL and MSL XML files, wait few days 🙂

Entity Framework Complex Entity Mapping

Entity Framework can have in its EDMX file complex entities. The “normal” property of an entity is a “Scalar entity”.
Scalar entity is a property of the CLR type.

Scalar Types

Scalar types

The complex entity is not a CLR type. In fact, it’s a group of scalar entities. From the point of view of the database, nothing change either if it’s a complex or scalar property. But, from the point of view of the Object Context, the entity will have an object with a group of scalar property.

How to add Complex entity?

In the AdventureWorks database once the model is generated from the database you get tables, views and stored procedure. For this exercise, lets take the generated entity “Department”

DepartmentEntity

Department Entity


DepartmentID, Name, GroupName and ModifiedDate are all scalar entities. If we want to add a Complex Entity, we need to add a complex type that will be added to the entity. To add a complexe type, right-click the Visual Designer and select Add>Complex Type.

Adding a complex type

Context Menu to add a Complex Type

One the complexe type is created, this one will be added in the Model Browser under the Entity Model (.edmx file), under the folder “Complex types”. From there, it’s possible to right click the complex entity to add scalar entity or even other complex entity.

Complex type with properties

Once the complex type is created, it’s possible to add it as a complex property. Right clicking on the entity and selecting add and complex property. The last step is to select the complex property and to go to the property window to select the type of the created type.

From here, it’s possible to access the complex property and will be even in the Intellisense.

var db = new AdventureWorks_DataEntities();
var aSingleDepartment = db.DepartmentList.Where(dept => dept.DepartmentID == 1).FirstOrDefault<Department>();
int theIntegerPropertyOfMyComplexPropertyOfDepartment = 
                  aSingleDepartment.MyComplexProperty.MyFirstIntProperty;           

The only thing that remain to do before compiling is a last mapping for the complex entity. This is needed because you will receive an error message if not.

Error 3004: Problem in mapping fragments starting at line 5050: No mapping specified for properties Department.
MyComplexProperty in Set DepartmentList.
An Entity with Key (PK) will not round-trip when: Entity is type [AdventureWorks_DataModel.Department]

This last step is concerning the mapping with the database.

Database

What about the database? Well, once the entity is modified in the entity model, it requires to be synchronized with the database. This is done by right clicking the Visual Designer and selecting “Generate database model from model”. This will override the DDL file (Data Definition Language), the SSDL (Store schema definition language) and the MSL (Mapping Specification Language) files.