Typed Dataset <–> Linq Entities

Introduction

On my previous post I discussed about how LINQ entities to 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 Entities and Type Dataset, then the domain of Web Applications and N-Tier Applications could be supported by the same Bussiness 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 designer 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 table in the Dataset have the same name.

Relations and Foreign Key Constraints

Also every relation between entities must have the same name as that between 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 name LinqTest and its dataset represantion 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 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

image

The corresponding Dataset will be. The relation name is the same, even thought it is not showing on the above image.

image

Each of the Business Object are in a separate assembly.

DataSetEntityConvertion

This is name of the assembly that does the convention between an LINQ Business Object and a Typed Dataset assuming that the above prerequisites are met.

The assembly uses heavily reflection and generics so the understanding of the above 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 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 convention 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 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 create for each row it has and does so by using DataRow2Entity which is supplied with the knowledge of whether it is child row or not. This is crucial because if it is 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 entityset and call InsertOnSubmit.

Modified or Unmodified

Here start the problems. First we must acquire the entity it self 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 was 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 entitytable of the datacontext 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 datacontext is used.

The rest of the DataRow2Entity finds the child rows of the row for each data relation and calls another generic version of its self.

Creating Predicate Functions and Expressions

This was the hardest part, and still there are some point that I can’t understand.

When trying to acquire an entity from the table entity of the datacontext, a simple delegate function suffices. After many attempts a 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 making the convertion as programmatically tranparent 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(); }

Source Code for the above assemblies

Advertisements

11 comments

  1. Hi Alex,
    Thanks for providing us with this very helpful code, you save me a lot..

    I have a question here, why the extension method “ToEntities” for DataSets is only going through the root tables and convert them to entities and not the all the tables in the dataset?

  2. Hello.

    The answer to your question is because the code for each root table, finds its child related tables and does exactly the same. The reason is that, when the code executes within a child relation the new entities are not added to the EntitySet of the DataContext but in the related entities of the coresponding root entity.

    For example suppose you have 5 tables (A,B,C,D,E).
    B is child related to A
    C is child related to B
    D is child related to A

    So you have A and E as root tables. B and D will be parsed because of their relation with A as will C because of its relation with B.

    Again let me remind you, that the above library was written as a proof of concept, and then became something that maybe used in production until MS provides something to fill the gap.

  3. Pingback: LINQ to SQL not Suitable for LOB « Alex Sarafian as Developer

  4. Hey

    It seems I found the solution I searched for a long time… -> Your code!

    Can you please provide me the code, so I can use it for my project? – Or can you please tell me, where to download – I can’t find…

    THX!

  5. The link seems to be invalid.
    The problem is that I also thought I had the code because of this post, but now I’m not that certain.

    I’ ll try to check on my workspace, but since it has been a long time, I’m not that hopefull

    Thank you.

  6. We have an existing Rich Desktop application with a DataLayer and Business Layer that utilizes Datasets. We have been asked to develop a Silverlight application to replace the Desktop application. It is unrealistic to replace the DL and BL, especially as they are used by other programs. As such, we have been looking for a method that will load an Entity from a DataTable and a DataTable from an Entity, while preserving the RowState. It seems that your program does just that.

    However, i am not clear how to utilize this. Below is a snippet of code wherein we would be performing the conversion. Would you be so kind as to explain how to accomplish this, or to point us to other tools to do this.

    Thank you very much:

    public System.Data.Objects.ObjectResult GetServiceApplication(int? AppID)
    {

    ServiceApplication serviceApp;
    ServiceApplicationDA serviceAppDA = new ServiceApplicationDA();
    string connectionString = ConnectionHelper.GetConnectionString(ConnectionHelper.SQLConnection);
    // Load Dataset
    serviceApp = serviceAppDA.GetDataByAppID((int) AppID, connectionString);
    // Instatiate Entity
    ServiceApp app = new ServiceApp();

    // Perform Conversion of datatable to Entity

    return app;

    }

    public void SaveServiceApplication(ServiceApp app)
    {

    ServiceApplication serviceApp = new ServiceApplication();
    // Perform Conversion of Entity to Dataset maintaining RowState

    // Update Database

    ServiceApplicationDA serviceAppDA = new ServiceApplicationDA();
    serviceAppDA.Update(serviceApp);

    }

    • Hi Tuvia,
      I understand your problem because this article was the result of a research I made, to address a similar situation.
      But this was done along time ago, when LINQ To SQL was first indroduced.
      Also, I would like to point, that for various reasons at that point, this method was not used. One of the reasons was that I didn’t feel quite sure about the robustness and speed of this converter, and because I never agreed as an architect with LINQ to SQL as I had bloged here

      Having the above in mind, I would discourage you from using this solution for what you are trying to accomplish, maily because LINQ to SQL is not actually supported and because Expression in NET4 actually do miracles in a lot simpler way.
      If you insist on looking on this solution, the download link in the codeproject article works. You can download it and chech the test project.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s