Typed Dataset LINQ entities

Document directory

• Entity and data table naming
• Relations and Foreign key constraints
• Database Construction from LINQ
• Column prerequisite
• Todatarow
• Toentity
• Keeping track of the changes
• Entity2dataset
• Dataset2entity

By Sarafian

Converting a typed dataset to and from LINQ entities.
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 cocould be supported by the same business object model and a data access layer over LINQ.

Assumptions-prerequisitesentity 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 inDataSetHave the same name.

Relations and Foreign key constraints

Every relation between entities must have the same name as that between the tables inDataSet. 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 = <span class="code-keyword">new</span> LinqTestDataContext(connectionString); <span class="code-keyword">if</span> (!ltdc.DatabaseExists()) { ltdc.CreateDatabase(); }

WhereLinqTestDataContextIsDataContextThe designer has created.

Column prerequisite

Each entity must haveversionProperty. This is because:

Attach(entity,<span class="code-keyword">true</span>)

Only works if there is such a property.

The database schema used for testing

The LINQ schema is namedLinqTestAnd itsDataSetRepresentation,DsLinqTest.

As seen in the picture below, there isRootElementWith a unique keyID, A version PropertyTimeStamp, And two string properties.

RootElementHas a Child RelationSubRootElementEntities which also have a unique keyID, A version PropertyTimeStamp, A string property andRootIDForeign key pointing toRootElementIt belongs.

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 sinceDataSetIs typed, every type inDataSetIs specifically named so it can be used to discover the entities it relates.

Todatarow

Is the part where the entities are used to fill the appropriate tables inDataSet. The entry point isEntity2DataSetClass, whereTEntityIs the entity type andTDataSetIsDataSetType. In our case,RootElementAndDsLinqTest, Respectively.

Basically,Entity2DataSetClass discovers the table that corresponds to the entity, and then calltheEntity2DataRowClass which, in addition, takesDataTableType 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,Entity2DataSetClass is called again, but this time,TEntityShocould beSubRootElement, In our case.

This side of the conversion is fairly easy.

Toentity

This case deals with converting a wholeDataSetTo its entity. The entry class isDataSet2EntityWhereTDataContextIs the type of ourDataContextAndTDataSetThe type of the sourceDataset. In our case,LinqTestBigDataContextAndDsLinqTest, Respectively.

The first thing thatDataSet2EntityDoes is to find the tables that have no parent relations. For each of these tables,DataTable2EntityIs used where, in addition,TDataTableAndTDataRowAre the types of the table and its rows.

DataTable2EntityDiscovers the entity type that must be created for each row it has, and does so by usingDataRow2EntityWhich 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 relatedEntitySetOf its parent entity instead of the EntityTableIn the data context.

The trick here is to know whether the original row is added, modified, deleted, or unchanged, which is the easy part throughRowState. 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 callInsertOnSubmit.

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 callDeleteOnSubmit.

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 entityPropertyChangedIs captured. There, with the help of a dictionary, the new values are applied to the original rows. This happens afterSubmitChangesOf the data context is used.

The rest ofDataRow2EntityFinds the child rows of the row for each data relation, and callanother 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 your attempts, I managed to make the creation entirely dynamic based on the primary keys of the entity.

This is done by these two functions:

<span class="code-keyword">private</span> System.Func&lt;TEntity, bool&gt; CreatePredicateFunction(TDataRow row) { <span class="code-keyword">return</span> p =&gt; (IsEqual(p, row)); } <span class="code-keyword">private</span> <span class="code-keyword">bool</span> IsEqual(TEntity entity, TDataRow row) { <span class="code-keyword">for</span> (<span class="code-keyword">int</span> i = <span class="code-digit">0</span>; i &lt; Cache.EntityPrimaryKeys&lt;TEntity&gt;.Names.Count; i++) { <span class="code-keyword">object</span> columnValue = <span class="code-keyword">null</span>; <span class="code-keyword">if</span> (row.RowState == DataRowState.Deleted) { columnValue = row[Cache.EntityPrimaryKeys&lt;TEntity&gt;.Names[i], DataRowVersion.Original]; } <span class="code-keyword">else</span> { columnValue = row[Cache.EntityPrimaryKeys&lt;TEntity&gt;.Names[i]]; } <span class="code-keyword">if</span> ((<span class="code-keyword">bool</span>)Cache.EntityPrimaryKeys&lt;TEntity&gt;.EqualMethods[i].Invoke( <span class="code-keyword">this</span>.entityType.GetProperty(Cache.EntityPrimaryKeys&lt;TEntity&gt;.Names[i]).GetValue( entity, <span class="code-keyword">null</span>), <span class="code-keyword">new</span> <span class="code-keyword">object</span>[] { columnValue }) == <span class="code-keyword">false</span>) { <span class="code-keyword">return</span> <span class="code-keyword">false</span>; } } <span class="code-keyword">return</span> <span class="code-keyword">true</span>; }

Happy as I was that I will be able to cast the above toExpression<System.Func<TEntity, bool>>, I found out that at runtime, an exception is thrown telling me thatIsEqualCannot be converted or something.

I assumeExpressionIs something far more complicated than a delegate. So, in order for this to work,CreatePredicateExpressionMust by supplied in everyDataRowOf ourDataSet. I did like this:

<span class="code-keyword">public</span> <span class="code-keyword">static</span> <span class="code-keyword">class</span> DsLinqTestPredicators { <span class="code-keyword">public</span> <span class="code-keyword">static</span> Expression&lt;System.Func&lt;RootElement, bool&gt;&gt; CreatePredicateExpression(DsLinqTest.RootElementRow row) { <span class="code-keyword">int</span> idValue = row.RowState == System.Data.DataRowState.Deleted ? (<span class="code-keyword">int</span>)row[<span class="code-string">"</span><span class="code-string">ID"</span>, System.Data.DataRowVersion.Original] : row.ID; <span class="code-keyword">return</span> (Expression&lt;System.Func&lt;RootElement, bool&gt;&gt;)(p =&gt; p.ID.Equals(idValue)); } <span class="code-keyword">public</span> <span class="code-keyword">static</span> Expression&lt;System.Func&lt;SubRootElement, bool&gt;&gt; CreatePredicateExpression(DsLinqTest.SubRootElementRow row) { <span class="code-keyword">int</span> idValue = row.RowState == System.Data.DataRowState.Deleted ? (<span class="code-keyword">int</span>)row[<span class="code-string">"</span><span class="code-string">ID"</span>, System.Data.DataRowVersion.Original] : row.ID; <span class="code-keyword">return</span> (Expression&lt;System.Func&lt;SubRootElement, bool&gt;&gt;)(p =&gt; 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 codeextenders

<span class="code-keyword">public</span> <span class="code-keyword">static</span> classDsLinqTestExtenders{ <span class="code-keyword">public</span> <span class="code-keyword">static</span> voidInsert(thisDsLinqTest extented, objectentity) { ((DataSet)extented).Insert(entity); } <span class="code-keyword">public</span> <span class="code-keyword">static</span> voidInsert(thisDsLinqTest extented, <span class="code-keyword">object</span>[] entities) { ((DataSet)extented).Insert(entities); } <span class="code-keyword">public</span> <span class="code-keyword">static</span> voidToEntities(thisDsLinqTest extented, DataContext dataContext) { ((DataSet)extented).ToEntities(dataContext); }}

Entity2dataset

<span class="code-keyword">public</span> DsLinqTest GetDsFromID(<span class="code-keyword">int</span> id) { LinqTestDataContext ltdc = <span class="code-keyword">new</span> LinqTestDataContext(connectionString); RootElement re = ltdc.RootElements.<span class="code-SDKkeyword">Single</span>(p =&gt; p.ID.Equals(id)); DsLinqTest ds = <span class="code-keyword">new</span> DsLinqTest(); ds.Insert(re); ds.AcceptChanges(); <span class="code-keyword">return</span> ds;}

Dataset2entity

<span class="code-keyword">public</span> <span class="code-keyword">void</span> SaveGeneralDs(DsLinqTest dsLinqTest) { LinqTestDataContext ltdc = <span class="code-keyword">new</span> LinqTestDataContext(connectionString); dsLinqTest.ToEntities(ltdc); ltdc.SubmitChanges(); }