Introduction
On a previous post on my blog, I discussed about how LINQ entities do not fit the world of applications that do not have a constant access to the data source. I concluded that if there was a way to connect LINQ wntities and Typed DataSets, then the domain of Web applications and N-Tier applications could be supported by the same Business Object Model and a Data Access Layer over LINQ.
Assumptions - Prerequisites
Entity and Data Table Naming
Before I continue, there is a basic assumption that must be kept in mind. The Business Object Model and the Typed Dataset must be constructed by their respective designers in Visual Studio, by dragging the tables into each designer. The main reason is that the converter I have developed assumes that the corresponding entities in LINQ and tables in the DataSet
have the same name.
Relations and Foreign Key Constraints
Every relation between entities must have the same name as that between the tables in the DataSet
. The above are automatically (great coincidence) kept, just by using the designer.
Circular relations and all combinations have not been tested, so I do not know whether my code supports them.
Database Construction from LINQ
If you wish to construct the database schema from the LINQ designer, then just do so, but before creating the Typed DataSet, the database must be created. To do this, just call:
LinqTestDataContext ltdc = new LinqTestDataContext(connectionString);
if (!ltdc.DatabaseExists())
{
ltdc.CreateDatabase();
}
where LinqTestDataContext
is the DataContext
the designer has created.
Column Prerequisite
Each entity must have a version
property. This is because:
Attach(entity,true)
only works if there is such a property.
The Database Schema Used for Testing
The LINQ schema is named LinqTest
and its DataSet
representation, DsLinqTest
.
As seen in the picture below, there is a RootElement
with a unique key ID
, a version property TimeStamp
, and two string properties.
RootElement
has a child relation of SubRootElement
entities which also have a unique key ID
, a version property TimeStamp
, a string property and a RootID
foreign key pointing to the RootElement
it belongs to.
The relation name is the same, even thought it is not showing on the above image.
Each of the Business Objects are in a separate assembly.
DataSetEntityConvertion
This is the name of the assembly that does the conversion between a LINQ Business Object and a Typed DataSet, assuming that the above prerequisites are met. The assembly uses Reflection and Generics heavily, so an understanding of those must be at least good. Keep in mind that since the DataSet
is typed, every type in the DataSet
is specifically named so it can be used to discover the entities it relates to.
ToDataRow
Is the part where the entities are used to fill the appropriate tables in the DataSet
. The entry point is the Entity2DataSet
class, where TEntity
is the entity type and TDataSet
is the DataSet
type. In our case, RootElement
and DsLinqTest
, respectively.
Basically, the Entity2DataSet
class discovers the table that corresponds to the entity, and then calls the Entity2DataRow
class which, in addition, takes the DataTable
type discovered.
There are some helping functions that, through Reflection, fill the row from the entity, and also find the child relations of the entity if there are any. If that is true, the Entity2DataSet
class is called again, but this time, TEntity
should be SubRootElement
, in our case.
This side of the conversion is fairly easy.
ToEntity
This case deals with converting a whole DataSet
to its entity. The entry class is DataSet2Entity
where TDataContext
is the type of our DataContext
and TDataSet
the type of the source Dataset
. In our case, LinqTestBigDataContext
and DsLinqTest
, respectively.
The first thing that DataSet2Entity
does is to find the tables that have no parent relations. For each of these tables, DataTable2Entity
is used where, in addition, TDataTable
and TDataRow
are the types of the table and its rows.
DataTable2Entity
discovers the entity type that must be created for each row it has, and does so by using DataRow2Entity
which is supplied with the knowledge of whether it is a child row or not. This is crucial because if it is a child row, it must be added to the related EntitySet
of its parent entity instead of the entity Table
in the data context.
The trick here is to know whether the original row is added, modified, deleted, or unchanged, which is the easy part through RowState
. The hard part is what to do with it.
Added
This case is easy. Just construct the entity and add it the table or the entity set and call InsertOnSubmit
.
Modified or Unmodified
Here starts the problems. First, we must acquire the entity itself to which we will apply the values. Accordingly to if the row is a child or not, a predicate function or expression must be constructed. This part is the most difficult.
If the row is unmodified, then there will be no applying of values.
Deleted
Like in Modified, the entity must be retrieved from the entity table of the data context in order to call DeleteOnSubmit
.
Keeping Track of the Changes
When a row is inserted or modified, various column values need to be updated by the auto generated ones from the database. So in every entity the PropertyChanged
is captured. There, with the help of a dictionary, the new values are applied to the original rows. This happens after the SubmitChanges
of the data context is used.
The rest of the DataRow2Entity
finds the child rows of the row for each data relation, and calls another generic version of itself.
Creating Predicate Functions and Expressions
This was the hardest part, and still there are some points that I can't understand.
When trying to acquire an entity from the table entity of the data context, a simple delegate function suffices. After many attempts, I managed to make the creation entirely dynamic based on the primary keys of the entity.
This is done by these two functions:
private System.Func<TEntity, bool> CreatePredicateFunction(TDataRow row)
{
return p => (IsEqual(p, row));
}
private bool IsEqual(TEntity entity, TDataRow row)
{
for (int i = 0; i < Cache.EntityPrimaryKeys<TEntity>.Names.Count; i++)
{
object columnValue = null;
if (row.RowState == DataRowState.Deleted)
{
columnValue = row[Cache.EntityPrimaryKeys<TEntity>.Names[i],
DataRowVersion.Original];
}
else
{
columnValue = row[Cache.EntityPrimaryKeys<TEntity>.Names[i]];
}
if ((bool)Cache.EntityPrimaryKeys<TEntity>.EqualMethods[i].Invoke(
this.entityType.GetProperty(Cache.EntityPrimaryKeys<TEntity>.Names[i]).GetValue(
entity, null), new object[] { columnValue }) == false)
{
return false;
}
}
return true;
}
Happy as I was that I will be able to cast the above to an Expression<System.Func<TEntity, bool>>
, I found out that at runtime, an exception is thrown telling me that IsEqual
cannot be converted or something.
I assume the Expression
is something far more complicated than a delegate. So, in order for this to work, a CreatePredicateExpression
must by supplied in every DataRow
of our DataSet
. I did like this:
public static class DsLinqTestPredicators
{
public static Expression<System.Func<RootElement,
bool>> CreatePredicateExpression(DsLinqTest.RootElementRow row)
{
int idValue = row.RowState == System.Data.DataRowState.Deleted ? (int)row["ID",
System.Data.DataRowVersion.Original] : row.ID;
return (Expression<System.Func<RootElement, bool>>)(p => p.ID.Equals(idValue));
}
public static Expression<System.Func<SubRootElement,
bool>> CreatePredicateExpression(DsLinqTest.SubRootElementRow row)
{
int idValue = row.RowState == System.Data.DataRowState.Deleted ? (int)row["ID",
System.Data.DataRowVersion.Original] : row.ID;
return (Expression<System.Func<SubRootElement, bool>>)(p => p.ID.Equals(idValue));
}
}
Final Words for the Converter
Extension methods are heavily used to help make the conversion as programmatically transparent as possible.
Using the Code
Extenders
public static classDsLinqTestExtenders{
public static voidInsert(thisDsLinqTest extented, objectentity)
{
((DataSet)extented).Insert(entity);
}
public static voidInsert(thisDsLinqTest extented, object[] entities)
{
((DataSet)extented).Insert(entities);
}
public static voidToEntities(thisDsLinqTest extented, DataContext dataContext)
{
((DataSet)extented).ToEntities(dataContext);
}
}
Entity2Dataset
public DsLinqTest GetDsFromID(int id)
{
LinqTestDataContext ltdc = new LinqTestDataContext(connectionString);
RootElement re = ltdc.RootElements.Single(p => p.ID.Equals(id));
DsLinqTest ds = new DsLinqTest();
ds.Insert(re);
ds.AcceptChanges();
return ds;
}
DataSet2Entity
public void SaveGeneralDs(DsLinqTest dsLinqTest)
{
LinqTestDataContext ltdc = new LinqTestDataContext(connectionString);
dsLinqTest.ToEntities(ltdc);
ltdc.SubmitChanges();
}