LINQ to Entities business objects
Introduction
After having developed a number of applications using LINQ-TO-SQL technologies, I have finally decided to take a close look into LINQ-TO-ENTITIES.We know that Microsoft stopped developing LINQ-TO-SQL, though they keep supporting it.
I read several articles that compare these two technologies and the reviews were mixed.
Authors complained that LINQ-TO-ENTITIES are bulky, do not support POCO, etc…
If you found this article, I think you have already read this on the internet.
Fortunately they said that Microsoft addressed the issues in the Visual Studio 2010.
I like LINQ-TO-SQL; it gives the ability to bring database communication into your programming code.
I am a great supporter of layering architecture and because the LINQ-TO-SQL represents the direct communication with the database, usually I compile the data access layer as stand alone class library.
The library has three types of the files: LINQ-TO-SQL classes’ file which is being generated by the Visual Studio, DbHelper file – a custom file which is a wrapper of useful functions and methods, and a controller file, which inherits the DbHelper class and is responsible for handling all data base objects.
My business layer communicates with the data access layer and is typically a different class library with all the business objects. These objects utilize the properties and methods of the data access layer objects.
Presentation layer communicates only with the business layer and is hidden from the data access layer.
Unfortunately, such an approach sometimes requires a lot of similar coding.
Very often I have to repeat properties and methods of the data objects in my business objects.
LINQ-TO-ENTITIES technology is conceptually different.
Rather than to talk directly to the database, it talks to conceptual entities.
They are linked to the database objects through special mappings.
Ideally, once the mappings are done you don’t care about the database at all.
LINQ-TO-SQL is directed to the MSSQL server, and LINQ-TO-ENTITIES are intended to be database independent.
So I decided to try this technology and figure out how much time and effort it can save me in coding.
Also, I decided to combine the business layer with the data access layer to save me coding time.
LINQ-TO-ENTITIES technology gives you a lot of different options on how to handle your project. I selected the option when the ADO.NET entity data model generates the code from the database.
It is not a bullet proof code, but rather the proof of concept. I did not compare the performance. I think this will happen later.
Let us start working on this together.
Database
First comes first - create a database. I used the MSSQL server 2008. Run the following script:CREATE TABLE [dbo].[Customer](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[Title] [varchar](50) NOT NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
GO
CREATE TABLE [dbo].[AddressType](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Type] [varchar](50) NOT NULL,
CONSTRAINT [PK_AddressType] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
GO
CREATE TABLE [dbo].[CustomerAddress](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CutomerID] [int] NOT NULL,
[TypeID] [int] NOT NULL,
[Address] [varchar](50) NULL,
[City] [varchar](50) NULL,
[State] [varchar](50) NULL,
[PostalCode] [varchar](50) NULL,
[Country] [varchar](50) NULL,
CONSTRAINT [PK_CustomerAddress] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
GO
ALTER TABLE [dbo].[CustomerAddress] WITH CHECK ADD CONSTRAINT [FK_CustomerAddress_AddressType] FOREIGN KEY([TypeID])
REFERENCES [dbo].[AddressType] ([ID])
GO
ALTER TABLE [dbo].[CustomerAddress] CHECK CONSTRAINT [FK_CustomerAddress_AddressType]
GO
ALTER TABLE [dbo].[CustomerAddress] WITH CHECK ADD CONSTRAINT [FK_CustomerAddress_Customer] FOREIGN KEY([CutomerID])
REFERENCES [dbo].[Customer] ([ID])
GO
ALTER TABLE [dbo].[CustomerAddress] CHECK CONSTRAINT [FK_CustomerAddress_Customer]
GO
As you see I created 3 simple tables:
- Customer
- CustomerAddress
- AddressType
Customer keeps the major information about the customer identity.
CustomerAddress keeps information about different addresses for the customer
AddressType specifies the possible types of address (like home, office, etc..)
Each table has a primary key and they are related with each other through foreign key constraints.
Visual Studio Solution
I used Visual Studio 2010.Create a Windows Console project and name it TestSqlToEntities.
Change the default solution name to TestSqlToEntitiesSol.
After the solution has been created, add a new Class Library project to the solution and name it EntityModel.
Delete the default Class1.cs file in this solution.
Now you have to add ADO.NET Entity Data Model into you EntityModel project.
I will try to lead you through the process; for those who are familiar with it, just skip the instructions.
- Click Add > New Item
Change the file name to ObjectContext.edmx. It is not necessary though.
- Choose model contents
Click Next - Choose data connections
As you can see I have already the connection to the proper database.
You will have to click button “New Connection…” and follow the instruction. It will lead you to the same screen.
Change the name of the default setting in the App.Config file to ModelEntities.
It is what I used in my code.
Click Next - Choose Database objects:
Click Finish
Please take your time in familiarizing yourself with the file; you can open it with different editors by clicking right mouse button against it and selecting Open with… option. You will see that the file is actually just XML file.
From its structure you can recognize how conceptuality is separated with the data storage.
But because this article has different goals, let us proceed.
References
Please add references:- TestSqlToEntities to the EntityModel(project reference)
- TestSqlToEnties to System.Data.Entity (assembly reference)
- EntityModel to System.Configuration (assembly reference)
Entity model
Now let us take a look into Objectcontext.edmx.cs fileThis file was generated by Visual Studio.
In our case, there are 4 classes that were created
- ModelEneities class which inherits from ObjectContext and has 3 public properties that return object sets for Customer, CustomerAddress, and AddressTypes.
- Customer class (EntityObject)
- CustomerAddress class (EntityObject)
- AddressType class(Entity object)
The problem is that we want to add some state and behavior into them, and we cannot do so because the base file is generated by the Visual studio. Whatever was added would be erased next time we modify the edmx file from the database.
Fortunately, we can add some partial classes to overcome the issue.
But before doing this, let us create a ModelHelper file:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Objects;
using System.Data.Objects.DataClasses;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Reflection;
using System.Collections;
using System.ComponentModel;
namespace EntityModel
{
public class ModelHelper
{
public static ModelEntities GetObjectContext(ModelEntities context = null)
{
if (null != context && context.Connection.State == ConnectionState.Open) return context;
return new ModelEntities(ConfigurationManager.ConnectionStrings["ModelEntities"].ConnectionString);
}
public static object Get<TEntity>(EntityKey key, ModelEntities context = null)
{
try
{
return (TEntity)GetObjectContext(context).GetObjectByKey(key);
}
catch
{
return null;
}
}
public static TEntity Save<TEntity>(TEntity entity, EntityKey key, ModelEntities context = null)
{
EntityObject ent = entity as EntityObject;
using (ModelEntities updater = GetObjectContext(context))
{
try
{
TEntity dbEntity = (TEntity)updater.GetObjectByKey(key);
DeepCopy<TEntity>(entity, dbEntity);
}
catch (Exception ex)
{
if (ex is System.Data.ObjectNotFoundException)
updater.AddObject(key.EntitySetName, entity);
}
updater.SaveChanges();
return entity;
}
}
public static void Delete<TEntity>(TEntity entity, EntityKey key, ModelEntities context = null)
{
using (ModelEntities updater = GetObjectContext(context))
{
try
{
TEntity dbEntity = (TEntity)updater.GetObjectByKey(key);
updater.DeleteObject(dbEntity);
updater.SaveChanges();
}
catch { }
}
}
#region "Utility functions"
public static void DeepCopy<T>(T copyFrom, T copyTo)
{
//get properties info from source:
PropertyInfo[] propertiesFrom = copyFrom.GetType().GetProperties();
//loop through the properties info:
foreach (PropertyInfo propertyFrom in propertiesFrom)
{
//get value from source:
var valueFrom = propertyFrom.GetValue(copyFrom, null);
//get property from destination
var propertyTo = copyTo.GetType().GetProperty(propertyFrom.Name);
if (propertyTo != null && valueFrom != null) //a bit of extra validation
{
//the property is an entity collection:
if (valueFrom.GetType().Name.Contains("EntityCollection"))
{
//get value from destination
var valueTo = copyTo.GetType().GetProperty(propertyFrom.Name).GetValue(copyTo, null);
//get collection generic type:
Type genericType = propertyTo.PropertyType.GetGenericArguments()[0];
//get list source from source:
IListSource colFrom = (IListSource)valueFrom;
//get list source from destination:
IListSource colTo = (IListSource)valueTo;
//loop through list source:
foreach (dynamic b in colFrom.GetList())
{
//create instance of the generic type:
dynamic c = (dynamic)Activator.CreateInstance(genericType);
//copy source into this instance:
DeepCopy<dynamic>(b, c);
//add the instance into destination entity collection:
colTo.GetList().Add(c);
}
}
// do not copy if the property:
//is entity object,
//is entity reference,
//entity state,
//entity key
else if (propertyTo.PropertyType.BaseType.Name.Contains("EntityObject")
|| valueFrom.GetType().Name.Contains("EntityReference")
|| valueFrom.GetType().Name.Contains("EntityState")
|| valueFrom.GetType().Name.Contains("EntityKey"))
{
//do nothing;
}
else // set the value of the destination property:
propertyTo.SetValue(copyTo, valueFrom, null);
}
}
}
#endregion
}
}
It needs some explanations:public static ModelEntities GetObjectContext(ModelEntities context = null)
{
if (null != context && context.Connection.State == ConnectionState.Open) return context;
return new ModelEntities(ConfigurationManager.ConnectionStrings["ModelEntities"].ConnectionString);
}
This function returns ModeEntities which inherits from the ObjectContext class.
The ObjectContext class provides facilities for querying and working with entity data as objects.
The ObjectContext class is the primary class for interacting with data as objects that are instances of entity types that are defined in a conceptual model.
An instance of the ObjectContext class encapsulates a connection to the database, in the form of an EntityConnection object.
GetObjectContext function accepts one optional parameter of the ModelEntities type, defaulted to null.
It is done in case if we want to reuse the existing ObjectContext, rather then open a new one.
Optionally you can provide the context into this function and if it's connection to the database is opened it will be returned. Otherwise the new ObjectContext will be created and returned.
The connection string for this function is retrieved via ConfigurationManager.
Note: I cut App.config file from the EntityModel project and moved it into the main project.
public static object Get<TEntity>(EntityKey key, ModelEntities context = null)
{
try
{
return (TEntity)GetObjectContext(context).GetObjectByKey(key);
}
catch
{
return null;
}
}
GetObjectByKey tries to retrieve an object that has the specified EntityKey from the ObjectStateManager.If the object is currently not loaded into the object context, a query is executed in an attempt to return the object from the data source.
If the object is loaded it returns the object without trip to the data source and this is a great difference with LINQ-TO-SQL.
The EntityKey class provides a durable reference to an object that is an instance of an entity type.
Each entity type has a key that is based on one or more scalar properties of the entity.
Keys are defined by the Key element in the conceptual model.
As in relational databases, these key values are used to verify the uniqueness of a given entity and to improve the performance of queries.
Usually, key properties are mapped to a key column in the underlying table, either an identity column or some other column that is constrained to guarantee a unique value. I wrote this function for the entity object of a generic type.
public static TEntity Save<TEntity>(TEntity entity, EntityKey key, ModelEntities context = null)
{
EntityObject ent = entity as EntityObject;
using (ModelEntities updater = GetObjectContext(context))
{
try
{
TEntity dbEntity = (TEntity)updater.GetObjectByKey(key);
DeepCopy<TEntity>(entity, dbEntity);
}
catch (Exception ex)
{
if (ex is System.Data.ObjectNotFoundException)
updater.AddObject(key.EntitySetName, entity);
}
updater.SaveChanges();
return entity;
}
}
The save function is also generic. It accepts the entity object and the entity key for this object.
It is imperative to supply the EntityKey, even if the key is pointing to non existing object.
The EntityKey is helping to define the identity of the object.
EntityKey.EntitySetName property is used to define the EntitySetName in case of adding the new generic object into the ObjectSet. I use try... catch... construction to define if the object exists and should be updated or a new object should be added into the set.
This function uses the DeepCopy function which I will introduce later.
public static void Delete<TEntity>(TEntity entity, EntityKey key, ModelEntities context = null)
{
using (ModelEntities updater = GetObjectContext(context))
{
try
{
TEntity dbEntity = (TEntity)updater.GetObjectByKey(key);
updater.DeleteObject(dbEntity);
updater.SaveChanges();
}
catch { }
}
}
This function does not require any elaboration.
It just deletes the entity object.public static void DeepCopy<T>(T copyFrom, T copyTo)
{
//get properties info from source:
PropertyInfo[] propertiesFrom = copyFrom.GetType().GetProperties();
//loop through the properties info:
foreach (PropertyInfo propertyFrom in propertiesFrom)
{
//get value from source:
var valueFrom = propertyFrom.GetValue(copyFrom, null);
//get property from destination
var propertyTo = copyTo.GetType().GetProperty(propertyFrom.Name);
if (propertyTo != null && valueFrom != null) //a bit of extra validation
{
//the property is an entity collection:
if (valueFrom.GetType().Name.Contains("EntityCollection"))
{
//get value from destination
var valueTo = copyTo.GetType().GetProperty(propertyFrom.Name).GetValue(copyTo, null);
//get collection generic type:
Type genericType = propertyTo.PropertyType.GetGenericArguments()[0];
//get list source from source:
IListSource colFrom = (IListSource)valueFrom;
//get list source from destination:
IListSource colTo = (IListSource)valueTo;
//loop through list source:
foreach (dynamic b in colFrom.GetList())
{
//create instance of the generic type:
dynamic c = (dynamic)Activator.CreateInstance(genericType);
//copy source into this instance:
DeepCopy<dynamic>(b, c);
//add the instance into destination entity collection:
colTo.GetList().Add(c);
}
}
// do not copy if the property:
//is entity object,
//is entity reference,
//entity state,
//entity key
else if (propertyTo.PropertyType.BaseType.Name.Contains("EntityObject")
|| valueFrom.GetType().Name.Contains("EntityReference")
|| valueFrom.GetType().Name.Contains("EntityState")
|| valueFrom.GetType().Name.Contains("EntityKey"))
{
//do nothing;
}
else // set the value of the destination property:
propertyTo.SetValue(copyTo, valueFrom, null);
}
}
}
This recursive method copies one entity object into another using System.Reflection.
It loops through the properties of the source object and analyzes each property. If the property is the simple one - it copies its value to the corresponding property's value of the destination object. If the property is an entity collection, it loops through the collection generic type and recursively makes the copy to the corresponding collection of the destination object
It does not copy some properties. I placed comments; please follow them to understand how it works.
Taking care of business
Let's add the new file. It will be partial class Customer.In this class we will utilize our ModelHelper class and all the classes that were generated for us by Visual studio.
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
namespace EntityModel
{
public partial class Customer
{
private EntityKey entityKey = null;
public Customer() { entityKey = new EntityKey("ModelEntities.Customers", "ID", 0); }
public Customer(int id)
{
if (id > 0)
entityKey = new EntityKey("ModelEntities.Customers", "ID", id);
Customer b = (Customer)ModelHelper.Get<Customer>(entityKey);
if (null != b)
ModelHelper.DeepCopy<Customer>(b, this);
}
public Customer Save()
{
return ModelHelper.Save<Customer>(this, entityKey);
}
public void Delete()
{
ModelHelper.Delete<Customer>(this, entityKey);
}
public static List<Customer> GetAll(ModelEntities context)
{
var list = context.Customers.ToList();
return list;
}
public static CustomerAddress CreateAddress(string address, string addressType,
string city, string country, string state = null, string postalCode = null, bool saveOnCreation = false)
{
CustomerAddress ca = new CustomerAddress();
EntityKey caEntityKey = new EntityKey("ModelEntities.CustomerAddresses", "ID", 0);
ca.Address = address;
ca.Country = country;
ca.City = city;
ca.State = state;
ca.PostalCode = postalCode;
//process address type:
AddressType at = Customer.CreateAddressType(addressType, saveOnCreation);
if (at.ID > 0)
ca.TypeID = at.ID;
else
ca.AddressType = at;
if (saveOnCreation)
ModelHelper.Save<CustomerAddress>(ca, caEntityKey);
return ca;
}
public static AddressType CreateAddressType(string type, bool saveOnCreation = false)
{
using (ModelEntities db = ModelHelper.GetObjectContext())
{
AddressType at = db.AddressTypes.Where(o => o.Type.ToLower() == type.ToLower()).FirstOrDefault();
EntityKey atEntityKey = new EntityKey("ModelEntities.AddressTypes", "ID", 0);
if (null != at)
return at;
else
{
at = new AddressType();
at.Type = type;
}
if (saveOnCreation)
ModelHelper.Save<AddressType>(at, atEntityKey);
return at;
}
}
}
}
This class has two constructors, Save(), Delete(), and GetAll() instant methods.
We need to take care of creating the EntityKey for the class and after that, use the ModelHelper to help.
Additionally the class has several static methods to create AddressType and CustomerAddress objects.
The code is simple and does not require any explanations.
The program
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EntityModel;
namespace TestSqlToEntities
{
class Program
{
static void Main(string[] args)
{
Customer c = new Customer();
c.Name = "Name";
c.Title = "Title";
CustomerAddress ca = Customer.CreateAddress("Street address", "Home", "City", "Country", "State", "Post Code");
c.CustomerAddresses.Add(ca);
c.Save();
using (ModelEntities db = ModelHelper.GetObjectContext())
{
List<Customer> list = Customer.GetAll(db);
string line = "{0} {1} {2} {3}";
foreach (Customer cst in list)
{
foreach (CustomerAddress cadr in cst.CustomerAddresses)
{
Console.WriteLine(line, cst.Title, cst.Name, cadr.Address, cadr.AddressType.Type);
}
}
Console.ReadKey();
}
}
}
}
The following code will create the objects, save them into the database and display what was saved.
The code starts with the creation of an empty Customer object.
Name and Title properties of the object is assigned.
Then the CustomerAddress object is created.
We are adding the CustomerAddress into the Customer and saving it.
The LINQ-TO-ENTITIES takes care of handling the database.
If the AddressType (in our case Home) exists, it will be used, otherwise the new entry for AddressType will be created.
So the simple c.Save() procedure will affect either three or two tables.
Then the program displays the customer list.
You noticed that I use
using (ModelEntities db = ModelHelper.GetObjectContext())
{
List<Customer> list = Customer.GetAll(db);
................................................
}
and pass the db into GetAll procedure.
The reason for this is to keep the scope of the "db" ObjectContext while retrieving the data from the Customer object.The LINQ-TO-SQL makes lazy requests to the database. It means that if an underlying object is not referred, the data for it is not requested.
When I refer to underlying objects (example cst.AddressType.Type) at this moment the data is being retrieved.
That is why I have the "db" object be ready and connected.
Once it is out of scope (after "using") it is being disconnected.
Conclusion
I did not consider performance for this small project, rather just wanted to find out how easy and convenient is LINQ-TO-ENTITIES, comparing with LINQ-TO-SQL.I must confess, that I am really impressed with this technology.
As you can see the simple ModelHelper class that I developed eliminates tons of coding for you.
You can create business classes as partial classes for some of EntityObjects, that were generated for you.
Combining this classes with the ModelHelper, using instance and static functions, you can easily create any business functionality you need.
推荐.NET配套的通用数据层ORM框架：CYQ.Data 通用数据层框架