Click here to Skip to main content
Click here to Skip to main content
Go to top

LINQ to Entities business objects

, 20 Jan 2011
Rate this:
Please Sign up or sign in to vote.
How to start designing 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 at LINQ to Entities.

We know that Microsoft stopped developing LINQ to SQL, though they keep supporting it. I have read several articles that compare these two technologies and the reviews were mixed. Authors complained that LINQ to Entities is bulky, do not support POCO, etc... If you found this article on the internet, I think you have already read about them on the internet.

Fortunately, they say that Microsoft has addressed these issues in 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 LINQ to SQL represents direct communication with the database, I compile the data access layer as a stand-alone class library. The library has three types of files: LINQ to SQL class file which is generated by Visual Studio, a 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 the database 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. The 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. The LINQ to Entities technology is conceptually different. Rather than talking 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 MS SQL 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.

The LINQ to Entities technology gives you a lot of different options on how to handle your project. I selected the option where 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 am using MS SQL 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 can see, I have created three simple tables:

  1. Customer
  2. CustomerAddress
  3. 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 addresses (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 am using 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 the 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.

  1. Click Add > New Item.
  2. Change the file name to ObjectContext.edmx. This is not necessary though.

  3. Choose the model contents.
  4. ChoseModelContents.gif

    Click Next.

  5. Choose data connections.
  6. ChooseDataConnections.gif

    As you can see, I already have the connection to the proper database. You will have to click the button "New Connection..." and follow the instructions. 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 have used in my code.

    Click Next.

  7. Choose Database objects:
  8. ChooseDbObjects.gif

    Click Finish.

As a result, you will see the following ObjectContext.edmx file (Design mode):

edmx.gif

Please take your time to familiarize yourself with the file; you can open it with different editors by clicking the right mouse button against it and selecting the Open with... option. You will see that the file is actually just an 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 these references:

  1. TestSqlToEntities to the EntityModel (project reference)
  2. TestSqlToEnties to System.Data.Entity (assembly reference)
  3. EntityModel to System.Configuration (assembly reference)

Entity model

Now let us take a look at the Objectcontext.edmx.cs file. This file was generated by Visual Studio.

In our case, there are four classes that were created.

  1. ModelEntities class which inherits from ObjectContext and has three public properties that return object sets for Customer, CustomerAddress, and AddressTypes
  2. Customer class (EntityObject)
  3. CustomerAddress class (EntityObject)
  4. AddressType class (Entity object)

Through these classes, we can communicate with the database, and because they represent the conceptual layer, we can treat them as business classes rather than data access classes.

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 Visual Studio. Whatever was added would be erased the 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 explanation:

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. The GetObjectContext function accepts an 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 a 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 the 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 a 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 a non-existing object. The EntityKey helps to define the identity of the object. The EntityKey.EntitySetName property is used to define the EntitySetName in case of adding the new generic object into the ObjectSet. I use a 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 a 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 have placed comments; please follow them to understand how it works.

Taking care of business

Let's add the new file. It will be the 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, and the 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 explanation.

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. The Name and Title properties of the object are assigned.

Then the CustomerAddress object is created. We are adding the CustomerAddress into the Customer and saving it. 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 will notice that I am using:

using (ModelEntities db = ModelHelper.GetObjectContext())
{
 List<Customer> list = Customer.GetAll(db);
 ................................................
}

and passing the database into the GetAll procedure. The reason for this is to keep the scope of the "db" ObjectContext while retrieving the data from the Customer object. 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 the 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 LINQ to Entities is, compared 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 me. You can create business classes as partial classes for some of the EntityObjects that were generated for you. Combining these classes with the ModelHelper, using instance and static functions, you can easily create any business functionality you need.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ed Guzman
Architect
United States United States
I started as an electronic engineer and moved into programming in 1989, was fascinated by Internet and developed my first web application in 1997.
When Microsoft introduced C# I became its fan and am not disappointed by now.
As for the hobbies, except of course reading, I love skiing and horse riding.

You may also be interested in...

Comments and Discussions

 
Generalnot bad PinmemberA Code Farmer26-Sep-12 0:36 
GeneralEF 4.0 is excellent! Thanks for posting this... Pinmemberxzz019531-Jan-11 6:06 
GeneralRe: EF 4.0 is excellent! Thanks for posting this... PinmemberEd Guzman14-Feb-11 5:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 20 Jan 2011
Article Copyright 2011 by Ed Guzman
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid