Introduction
I have been playing with LINQ to SQL for a bit. It is great and easy to use along with the designer that ships with VS.NET 2008. I wanted to create a Facade layer that integrates with LINQ to SQL classes. The old school way is to create a public class (Manager) and this class calls your database mappers to get/set the required info. Now, LINQ to SQL replaces those mapper classes, and the way to do your operations is to open the DataContext and start defining queries to perform such operations.
So, I grouped those queries and operations into another separate layer called Processes/Operations layer in which the facade will end up calling. By doing that, the Facade remains as is and the Processes/Operations layer replaces the mappers layer while it encapsulates the whole logic and complexity for the DB operations.
Having said that, I created a base class which encapsulates the most common logic for database operations in the Processes/Operations layer instead of repeating the same code over and over for each class.
Note: This article is intended for people familiar with LINQ and LINQ to SQL.
Using the code
Class definition
Shown below is the definition of the class:
internal class DataBaseProcessBase<T, DC> where T :
class, new() where DC : DataContext, new()
As you can notice, this is a generic class taking two types: the first one is your entity type, while the second is your DataContext. Moving forward, here are the operations listed in that class:
- Add
- Get
- Update
- Delete
You just create a class that inherits from this one, as follows:
internal class MyProcess : DataBaseProcessBase<MyEntity,MyDataContext>
The Add operation
protected virtual object Add(T entity, string IdPropertyName)
{
using (DC db = new DC())
{
db.GetTable<T>().InsertOnSubmit(entity);
db.SubmitChanges();
}
return entity.GetType().GetProperty(IdPropertyName).GetValue(entity, null);
}
The add operation is pretty easy and straightforward. It just inserts the entity into the database and returns the ID of the new record. Note, the second parameter is the ID property name as I'm using Reflection to get the value of the ID property for the inserted entity.
How to use
base.Add(MyEntity, "ID");
Note: the name of the property is case sensitive.
The Get operation
protected virtual IList<T> Get(DataLoadOptions options,
Expression<Func<T, bool>> query, int from, int to)
{
IList<T> list = null;
if (to == 0)
to = 100;
if (from > to)
from = 0;
using (DC db = new DC())
{
if (null != options)
db.LoadOptions = options;
if (null == query)
list = db.GetTable<T>().Skip(from).Take(to - from).ToList();
else
list =
db.GetTable<T>().Where(query).Skip(from).Take(to - from).ToList();
}
return list;
}
The get method consists of four parameters:
DataLoadOptions: if defined, it would be assigned to the database.Expression<Func<T,bool>> query: this is the query or Lambda expression in which the results will be returned upon.int from: starting index, for pagination purpose.int to: ending index, also for pagination purpose.
How to use
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<MyObject>(m => m.MyNestedEntity);
base.Get(options,m => m.CategoryID == 1,0,100);
Suppose that you have a class called MyObject and this class has two properties, CategoryId and MyNestedObject. Here is the scenario, we want to get 100 results of MyObject where its CategoryID equals to 1 along with its nested object.
There are two overloads for this method, as shown:
protected virtual IList<T> Get(Expression<Func<T, bool>> query, int from, int to)
{
return Get(null, query, from, to);
}
protected virtual IList<T> Get(int from, int to)
{
return Get(null, null, from, to);
}
Points of interest
Each parameter defined in the get method can be null. Starting from the first one, if DataLoadOptions is null, it won't be assigned to the database context. If the query is null, then all results will be returned depending on the pagination indexes specified. And, last but not least, if the 'to' parameter is 0, then it will be defaulted to 100 (you can change this by a config value) and if the 'from' is greater than the 'to' parameter, it will be defaulted to 0 (also, you can change it if you don't like this behaviour).
Those methods are a great way when you do your search; based on conditions, you build up your query, and then you call one of those methods.
Remember, those methods will get any data based on the provided query. If you want to filter such get methods on the client code, you just create your methods in the facade and the process class, as follows:
public static class FacadeExample
{
public static MyEntity GetMyEntityByID(int id)
{
return (new MyProcess()).GetByID(id);
}
}
Now, in the MyProcess class (the one defined at the top of this article), add the same method but add the query logic to get by ID, somehow like this:
public MyEntity GetByID(int id)
{
return base.Get(m => m.ID == id,0,1)[0];
}
In the facade class, notice that we are instantiating a new instance of the MyProcess class (new MyProcess()), so each time this method is called, a new instance will be created. This is not a good practice, but I added it there for quick referencing the class as it's not related to this article subject. Consequently, you should think of a singleton or a factory class to get your process instances instead of creating a new instance each time a method is called.
Note: The code listed above will throw an IndexOutOfRangeException if the results count is zero, so you should always do null and count checking before returning results.
The Update operation
protected virtual void Update(T entity, Expression<Func<T, bool>> query)
{
using (DC db = new DC())
{
object propertyValue = null;
T entityFromDB = db.GetTable<T>().Where(query).SingleOrDefault();
if (null == entityFromDB)
throw new NullReferenceException("Query Supplied to " +
"Get entity from DB is invalid, NULL value returned");
PropertyInfo[] properties = entityFromDB.GetType().GetProperties();
foreach (PropertyInfo property in properties)
{
propertyValue = null;
if (null != property.GetSetMethod())
{
PropertyInfo entityProperty =
entity.GetType().GetProperty(property.Name);
if (entityProperty.PropertyType.BaseType ==
Type.GetType("System.ValueType")||
entityProperty.PropertyType ==
Type.GetType("System.String"))
propertyValue =
entity.GetType().GetProperty(property.Name).GetValue(entity, null);
if (null != propertyValue)
property.SetValue(entityFromDB, propertyValue, null);
}
}
db.SubmitChanges();
}
}
The update operation is a bit tricky because the properties mapping between the passed entity and the entity from the database is done dynamically, so any nested object will not be mapped. The only property mapping would be against value types and string values. If you want to use this method as is, then you should do the logic of mapping objects in your entity, then use this method as a final step to map the other value types and string properties you have.
How to use
base.Update(MyEntity,e => e.ID == MyEntity.ID);
How it works
Depending on the query expression, this method will get the entity from the database, then do a dynamic property mapping between the passed entity and the retrieved one, and then submit the changes. So, in the above example, I am updating the database entity where the ID = the passed entity ID.
Points of interest
The reason why we are getting the entity from the database and doing the mapping is, as you can notice, we are using the Facade to get our entities. So, once the database context is closed, then we cant submit the changes using the entity passed to this method. As you will hit an Object Disposed exception, the other way around is to get the entity from the database, do the mapping, and then submit the changes.
Updated
I have been thinking of the mapping between the properties in the update method and wasn't satisfied with the design, so I redesigned the whole mapping thing and plugged in a mapping provider for each type supported, where you can also create your own providers and plug them into play.
First, I created an interface as follows:
public interface IPropertyMappingProvider
{
void MapProperties(object entity, object LINQEntity, PropertyInfo LINQProperty);
}
And, a base property mapper class that implements the previous interface:
internal class PropertyMappingProviderBase : IPropertyMappingProvider
{
#region IPropertyMappingProvider Members
public virtual void MapProperties(object entity,
object LINQEntity, PropertyInfo LINQProperty)
{
object propertyValue = null;
PropertyInfo entityProperty = entity.GetType().
GetProperty(LINQProperty.Name);
if (null != entityProperty)
propertyValue = entityProperty.GetValue(entity, null);
if (null != propertyValue)
LINQProperty.SetValue(LINQEntity, propertyValue, null);
}
#endregion
}
So, as you can see, the base class implements the IPropertyMappingProvider, which has only one method, MapProperties. The MapProperties method accepts three parameters: the first one is your entity received from the client code, the next one is the entity retrieved from the database, and the last one is the property which the mapping will be held against.
Now, here is the fun part of creating providers. But, before pasting the code for each provider, the provider for each type should be initialized dynamically. No switch statements to initialize them, and no hardcoded values in the class responsible for initializing the correct providers. So, what's the best way to do this????
I found that creating a custom attribute and decorating our provider classes with it is the way. Here is the definition of it:
[AttributeUsage(AttributeTargets.Class)]
public class MappingPropertyTypeNameAttribute : System.Attribute
{
private string _propertyTypeName;
public MappingPropertyTypeNameAttribute(string propertyTypeName)
{
_propertyTypeName = propertyTypeName;
}
public string PropertyTypeName
{
get
{
return _propertyTypeName;
}
}
Simply, this attribute is assigned to each provider class (except the base), which will hold the fully qualified name of the type that it will process (e.g.: System.String). So now, I can list the two providers I have created: one for string values and the other one for value types:
String mapping provider:
[MappingPropertyTypeName("System.String")]
internal class StringPropertyMappingProvider : PropertyMappingProviderBase
{
public override void MapProperties(object entity,
object LINQEntity, PropertyInfo LINQProperty)
{
base.MapProperties(entity, LINQEntity, LINQProperty);
}
}
Value types mapping provider:
[MappingPropertyTypeName("System.ValueType")]
internal class ValueTypePropertyMappingProvider : PropertyMappingProviderBase
{
public override void MapProperties(object entity,
object LINQEntity, PropertyInfo LINQProperty)
{
base.MapProperties(entity, LINQEntity, LINQProperty);
}
}
Notice the MappingPropertyTypeName which is set exactly to the type name. Nothing special in both classes except calling the base method. But, it's a good practice to create a provider for each type in case you want to do more manipulation, or if you want to change the behaviour of one of them without affecting others.
Now, everything is set, the only thing missing is our facade class to initiate the right provider dynamically. So, here is the definition of that class:
public static class MappingProvider
{
public static void MapProperties(object entity,
object LINQEntity, PropertyInfo LINQProperty)
{
IPropertyMappingProvider provider = null;
Type[] currentProviders = Assembly.GetExecutingAssembly().GetTypes().Where(
t => t.GetCustomAttributes(typeof(MappingPropertyTypeNameAttribute),
false).ToArray().Length > 0).ToArray();
if (null != currentProviders && currentProviders.Length > 0)
{
Type providerType = currentProviders.Where(p =>
(p.GetCustomAttributes(typeof(MappingPropertyTypeNameAttribute),
false).ToArray()[0] as MappingPropertyTypeNameAttribute).PropertyTypeName ==
LINQProperty.PropertyType.ToString()).SingleOrDefault();
if(null == providerType)
providerType = currentProviders.Where(p =>
(p.GetCustomAttributes(typeof(MappingPropertyTypeNameAttribute),
false).ToArray()[0] as MappingPropertyTypeNameAttribute).PropertyTypeName ==
LINQProperty.PropertyType.BaseType.ToString()).SingleOrDefault();
if (null != providerType)
{
provider = ProviderFactory.CreatePropertyMappingProvider(providerType);
provider.MapProperties(entity, LINQEntity, LINQProperty);
}
}
}
}
To be honest, this class was great fun to write, because it does everything dynamically using Reflection. First, it gets all the types defined in the assembly where they have our MappingPropertyTypeNameAttribute defined, meaning, it will get all our mapping providers. If it finds any, it proceeds with trying to get the right provider by comparing the passed property type with the attribute value defined on each of our provider classes, using a string comparison. If not found, then it will try and compare the base type of the property with our attribute's value (reason for that is, all value types (e.g.: Int32) have their type set to System.Int32 and their base type to value type; plus it's a good thing to expand the possibilities of comparison).
So, after finding our provider type, we need to initialize it. I created a factory class to do that job for us, as follows:
internal static class ProviderFactory
{
static IDictionary<string, IPropertyMappingProvider> providers =
new Dictionary<string, IPropertyMappingProvider>();
public static IPropertyMappingProvider
CreatePropertyMappingProvider(Type providerType)
{
IPropertyMappingProvider provider = null;
if (providers.ContainsKey(providerType.ToString()))
provider = providers[providerType.ToString()]
as IPropertyMappingProvider;
else
{
provider= AppDomain.CurrentDomain.CreateInstanceAndUnwrap(
Assembly.GetExecutingAssembly().ToString(),
providerType.ToString()) as IPropertyMappingProvider;
providers.Add(provider.GetType().ToString(),
provider as IPropertyMappingProvider);
}
return provider;
}
}
Nothing special there, except initializing the provider and inserting it into a custom Dictionary for caching purposes, as there is no need to keep creating the provider over and over, just a singleton will do.
And finally, it calls the MapProperties method from the corresponding provider, so our Updatemethod in the base class will look like this after the modification:
protected virtual void Update(T entity, Expression<Func<T, bool>> query)
{
using (DC db = new DC())
{
T entityFromDB = db.GetTable<T>().Where(query).SingleOrDefault();
if (null == entityFromDB)
throw new NullReferenceException("Query Supplied to Get" +
" entity from DB is invalid, NULL value returned");
PropertyInfo[] properties = entityFromDB.GetType().GetProperties();
foreach (PropertyInfo property in properties)
{
if (null != property.GetSetMethod())
{
MappingProvider.MapProperties(entity, entityFromDB, property);
}
}
db.SubmitChanges();
}
}
Last, about the update operation, the old DatabaseProcessBase class is still available in the downloads, and the new design for all the providers and the modification for the class is added as well.
The Delete operation
protected virtual void Delete(Expression<Func<T, bool>> query)
{
using (DC db = new DC())
{
db.GetTable<T>().DeleteOnSubmit(
db.GetTable<T>().Where(query).Single());
db.SubmitChanges();
}
}
The delete operation only takes one parameter, which is the query to perform to get the entity which is going to be deleted. You can extend this method to delete all, if no query was defined.
How to use
base.Delete(e => e.ID == 1);
Conclusion
Hope you all benefitted from this class. Thank you all for reading.
History
- 9 May 2008 - Initial version of this article.
- 31 May 2008 - Updated to include mapping providers to map properties between objects.
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
