How to represent a MySQL database schema in C#?

Question

The title is not so accurate, but I couldn't come up with a better one.

I’m trying to write a MySQL Connector for MS‘ Forefront Identity Manager (FIM is basically a sync engine that synchronizes identities between various data sources using a meta directory). But I’m having difficulties to come up with an appropriate design.

Let’s say I want to import user data from a db into FIM’s metaverse. A user object has various attributes like firstname, lastname, address etc. In the database these attributes can be distributed between multiple tables. FIM ultimately needs these attributes to be merged into one object. So the user needs to configure the connector to tell it how the data is stored in the DB.

I was wondering what would be the “best” way to represent this configuration. Two alternatives come to (my) mind:

1. I could just save a select query that merges/joins the data, so that the result is a single “table” with all the desired attributes. The problem with this is that I think I would have to do some kind of parsing on this query-string to create a fim-compatible-schema out of it (which is basically the name of the object type (f.e. “person”) and a list of attributes). This schema needs to be creatable from the query-string alone without actually executing the query (I could execute some fake queries if that would simplify the process).
2. I could create some classes to represent the database schema, i.e. the tables and relationships. Since I’m not that experienced with MySQL (or databases at all for that matter) I’m running the risk of missing some special cases. Also it might be some kind of overkill, since the schema can be assumed as fixed once it's configured.

Does anyone have same advice on which alternative to choose and how to tackle the problems that would come with it? Or is there another – better – alternative I didn’t think of? Any advice would be greatly appreciated! If something is not clear, please let me know.

Edit: Since there have been some questions on the use case, I'm going to elaborate a bit:

As I've said, I'm developing a Management Agent for FIM. FIM provides a so called Extensible Connectivity Management Agent, which is basically one single class implementing a few interfaces. (See this technet guide for a sample implementation).

Since I want to develop a generic agent for managing identities in a MySQL database, I don't know the database layout at compile time. When the enduser wants to use the management agent, he needs to decide, which attributes of the identities he'd like to manage. So I need to give the user some way to configure the management agent. My main question is, how to design the classes to save this configuration.

Lets look at a simple example:

Say you want to manage employee identities. To keep it simple, we have three attributes:

• firstName
• lastName
• department

In this example case it could be f.e. just one single table with 4 columns (the attributes plus an id). But it could also be the much better design, which uses two tables, one user table and one department table, using a 1:1 relation to define the users department.

FIM requires me to consolidate these attributes in one object. It provides a class CSEntryChange which has an AttributeChanges collection member. I would then create some instances of AttributeChange (which basically contains the attribute name und it's value) and add them to the collection. So the user-editable configuration must tell the management agent how it can get the users with all defined attributes from the db and how to create and modify users in that database.

So ideally I'd have an intance of some "MySQLSchema" class (which is configured by the user up front), that could return a List<CSEntryChange> (I wouldn't actually use the CSEntryChange class for the sake of decoupling, but you should get the point) that contains all users in the db (pagination might be a requirement but I can figure that out later). In addition I'd like to able to pass it a CSEntryChange which would result in the corresponding database entries beeing updated (or created if not yet present).

I hope this clear it up a bit more :)

Answer 1

I think that your real question is, "How to access MySQL entities over C#?"

To begin with, I hope you are building this in as a MVC application.

I would suggest sticking to a full Microsoft stack for purposes of learning and ease of implementation.

With this in mind, you will want to create an EntityFramework MySQL data provider in the following steps:

1. Create a new project and and EntityFramework either through the Nuget package manager UI or package manager console by typing Install-Package EntityFramework -Version 6.0.2 (and add a reference to this project from your web project). Look half way down the page for "Configure EntityFramework to work with a MySQL database".

2. Install the MySQL provider for entity framework through the Nuget package manager UI or by typing Install-Package MySql.Data.Entity in the package manager console

The next step requires understanding of db configuration changes, that are nicely detailed here - Configure EntityFramework to work with a MySQL database.

You should end up with a nice class structure which will allow you to traverse your entities' navigation properties through EF.

Depending on the level of security your application requires, you may also want to create data transfer objects (DTOs) that contains only the data required for your remote calls - keeping your data calls efficient.

This is by no means a definitive guide on how to do this, but hopefully gives you a start in the right direction.

With regards to your step #1 above:

I could just save a select query that merges/joins the data, so that the result is a single “table” with all the desired attributes. The problem with this is that I think I would have to do some kind of parsing on this query-string to create a fim-compatible-schema out of it (which is basically the name of the object type (f.e. “person”) and a list of attributes). This schema needs to be creatable from the query-string alone without actually executing the query (I could execute some fake queries if that would simplify the process).

I am slightly confused by this. Are you saying that you want to dynamically update your database schema based application requests?