After a short chat with my ex-colleague asking me to help him to create generic method that loads any entity class from the database I decided to write this article in case of somebody needs the same in his project. By the way thank you Teodor for my inspiration to do it.
Scenario
We are implementing our data access layer based on LINQ TO SQL. In the specifications that were created by our software architect is required to have generic method that load from the data base any record into entity object by primary key.
public class LINQGateway<TEntity> where TEntity : class
{
private IDbConnection _DbConnection;
public LINQGateway(IDbConnection connection)
{
this._DbConnection = connection;
}
public TEntity GetEntityByPrimaryKey(object pkKey, params object[] pkKeys)
{
// TO DO
}
}
Problem
We become stuck, because each table has different primary key (count, name and data type of the primary key columns). So how to create a method that can be used in any case of loading a record from the database by primary key. So we are facing the challenge how to implement GetEntityByPrimaryKey.
The Algorithm
1. Get tha mapping of the entity type
2. Use information for the mapping
2.1 to get the name of the columns
2.2 to get the name of the columns included in the primary key
3. Generate the T-SQL Query
4. Execute the Query via LINQ Framework to retrive the result as entity object
Before explain my approach to implement the algorithm above I want to explain you how linq expressions are executed.
Implementation and Solutions
I figured out 2 soltuions that are almost the same. The first solution (the simplest one) uses standard T-SQL that is translated by LINQ. The second one is using dynamic lambda expressions to build LINQ Query that is used to get the entity from the database.
Solution No.1
This is the easiest and simple way to get a entity by primary key. This is the solution that I gave to my colleague.
public TEntity GetEntityByPrimaryKey(object pkKey, params object[] pkKeys)
{
List<object> primaryKeys = new List<object>();
primaryKeys.Add(pkKey);
primaryKeys.AddRange(pkKeys);
TEntity entity = null;
Type entityType = typeof(TEntity);
using (DataContext dataContext = new DataContext(this._DbConnection))
{
dataContext.Log = new DebuggerWriter();
dataContext.ObjectTrackingEnabled = false;
Table<TEntity> table = dataContext.GetTable<TEntity>();
MetaType metaEntityType = dataContext.Mapping.GetMetaType(entityType);
var primaryKeyColumns = from pkColumn in metaEntityType.DataMembers
where pkColumn.IsPrimaryKey
select pkColumn;
var columns = from col in metaEntityType.DataMembers
where col.IsPersistent && !col.IsAssociation
orderby col.Ordinal
select "[t0].[" + col.MappedName + "]";
string selectColumns = String.Join(", ", columns.ToArray());
int pkColumnsCount = 0;
if (primaryKeyColumns != null)
pkColumnsCount = primaryKeyColumns.Count();
if (pkColumnsCount == 0)
throw new InvalidOperationException("Table doesn’t have primary key");
if (pkColumnsCount != primaryKeys.Count)
throw new InvalidOperationException("Primary key values doesn’t match primary key columns.");
string tableName = metaEntityType.Table.TableName;
if (tableName.Contains(‘.’))
{
string[] splittedTablename = metaEntityType.Table.TableName.Split(‘.’).Select(p => "[" + p + "]").ToArray();
tableName = String.Join(".", splittedTablename);
}
StringBuilder builder = new StringBuilder("SELECT " + selectColumns + Environment.NewLine + "FROM " + tableName + " AS [t0]" + Environment.NewLine + "WHERE ");
int index = 0;
foreach (MetaDataMember pkColumn in primaryKeyColumns)
{
string columnName = pkColumn.Name;
string paramID = index.ToString(CultureInfo.InvariantCulture);
builder.Append("[t0].[" + columnName + "] = {" + paramID + "}");
if (index + 1 != pkColumnsCount)
builder.Append(" AND ");
index++;
}
string query = builder.ToString();
entity = dataContext.ExecuteQuery<TEntity>(query, primaryKeys.ToArray()).SingleOrDefault();
}
return entity;
}
Solution No.2
This is the solution is more complicated than the one above.
public TEntity GetEntityByPrimaryKey(object pkKey, params object[] pkKeys)
{
List<object> primaryKeys = new List<object>();
primaryKeys.Add(pkKey);
primaryKeys.AddRange(pkKeys);
TEntity entity = null;
Type entityType = typeof(TEntity);
using (DataContext dataContext = new DataContext(this._DbConnection))
{
dataContext.Log = new DebuggerWriter();
dataContext.ObjectTrackingEnabled = false;
Table<TEntity> table = dataContext.GetTable<TEntity>();
MetaType metaEntityType = dataContext.Mapping.GetMetaType(entityType);
var primaryKeyColumns = from pkColumn in metaEntityType.DataMembers
where pkColumn.IsPrimaryKey
select pkColumn;
int pkColumnsCount = 0;
if (primaryKeyColumns != null)
pkColumnsCount = primaryKeyColumns.Count();
if (pkColumnsCount == 0)
throw new InvalidOperationException("Table doesn’t have primary key");
if (pkColumnsCount != primaryKeys.Count)
throw new InvalidOperationException("Primary key value doesn’t match primary key columns.");
ParameterExpression paramExpression = Expression.Parameter(entityType, "entity");
BinaryExpression whereExpression = null;
int index = 0;
foreach (MetaDataMember pkColumn in primaryKeyColumns)
{
object value = primaryKeys[index];
string columnName = pkColumn.Name;
if (value != null && value.GetType() != pkColumn.Type)
{
Type paramType = value.GetType();
string exceptionMsg = String.Format("The type ‘{0}’ of parameter ‘{1}’ is different than its column ‘{2}’ type ‘{3}’", paramType, value, columnName, pkColumn.Type);
throw new InvalidOperationException(exceptionMsg);
}
BinaryExpression condition = Expression.Equal(Expression.Property(paramExpression, columnName), Expression.Constant(value));
if (whereExpression != null)
whereExpression = Expression.And(whereExpression, condition);
else
whereExpression = condition;
index++;
}
Expression<Func<TEntity, bool>> predicate = Expression.Lambda<Func<TEntity, bool>>(whereExpression, new ParameterExpression[] { paramExpression });
entity = table.SingleOrDefault(predicate);
}
return entity;
}
Conclusion
In Solution No.1 we generate T-SQL string that is executed by LINQ and the result is translated as entity object. Insead of this in Solution No.2 we created dynamic LINQ expression that is translated by LINQ TO SQL Framework to T-SQL, which expected to be costly. So I did some tests in Northwind database to prove my expectations.
Solution No.1 loads 1 record from table Employees for 315 milliseconds (average speed) againts 292 milliseconds (average speed) for Solution No.2. So I can’t say which implementation is the best way to resolve the task. Presently I’m in doubt about that. Why solution no.2 is faster than Solution no.1? I hope that I will give the aswer of this question soon in further posts.
. It will be separated to 4 sessions:
