Linq-To-SQL Legacy Relation Mapping

Question

Hi,

I am trying to get Linq2SQL to work with my legacy database. I currently have a notes table that is generic to a few different entities and mapped m:m. Instead of mapping one relation table per entity type whoever designed this database decided to use a single relation table with a type column (as a varchar yuck!).

How do I map Foo and Bar to have a Notes collection? Is this even possible. I am not seeing the light. I tried to have two classes FooNotes and BarNotes that inherit from RelateNotes and then mapping the Type field as the descriptor.

This doesn't work and I receive the below error.

Bad Storage property: '_EntityID' on member 'TestLinq.BarNotes.EntityID'.

I don't want to get too far down the Linq2SQL road before realising it not possible. I am not allowed to change the database much.

Many Thanks,

Answer 1

I would consider expanding your app's design to include a Domain Model based layered architecture.

This way you can create a Domain Model that meets the requirements of the system while abstracting away how the mapping works underneath. For example, you could have a common interface for the data access layer that returns the mapped entities. An implementation of this interface could be created for the old 'string-equality' m2m relationship in the legacy database. One day when you are ready to ditch the legacy database, a new implementation could be created for a different ER db model which would allow your Domain Model (object model) and higher layers (services, UI etc) to remain unchanged (because they all utilise the common interface).

In your object model you could define each object that needs Notes and have them each contain a Notes collection for each instance. Eg. Foo has a collection of Notes; Bar has a collection of Notes. Your Repository interface would look after returning these entities but the implementation of that repo would worry about how it's read and persisted to the db.