Looking for a .Net ORM

Question

I'm looking for a .Net 3.5 ORM framework with a rather unusual set of requirements:

• I need to create and alter tables at runtime with schemas defined by my end-users.
  (Obviously, that wouldn't be strongly-typed; I'm looking for something like a DataTable there)
• I also want regular strongly-typed partial classes for rows in non-dynamic tables, with custom validation and other logic. (Like normal ORMs)
• I want to load the entire database (or some entire tables) once, and keep it in memory throughout the life of the (WinForms) GUI. (I have a shared SQL Server with a relatively slow connection)
• I want full WinForms data-binding support
• I also want regular LINQ support (like LINQ-to-SQL) for ASP.Net on the shared server (which has a fast connection to SQL Server)
• In addition to SQL Server, I also want to be able to use a single-file database that would support XCopy deployment (without installing SQL Server on the end-user's machine). (Probably Access or SQL CE)
• Finally, it has to be free (unless it's OpenAccess)

I'll probably have to write it myself, as I don't think there is an existing ORM that meets these requirements.
However, I don't want to re-invent the wheel if there is one, hence this question.

I'm using VS2010, but I don't know when my webhost (LFC) will upgrade to .Net 4.0

Answer 1

I don't think one framework will do it, but also you don't need to re-invent the wheel.

You could use a normal ORM like Entities Framework, NHibernate or Subsonic for the non-dynamic part and hack some ruby-like-migrations framework for the dynamic part.

It should be easy since these migrations frameworks already have all the methods for schema modification mapped to several databases, it's just the matter of writing some classes to expose them.

EDIT: The migrations-like framework would be for this: "I need to create and alter tables at runtime with schemas defined by my end-users. (Obviously, that wouldn't be strongly-typed; I'm looking for something like a DataTable there)"

For instance, with sharp-migrations you can write something like:

DataClient.Add
          .Table("TableName")
          .WithColumns(
             Column.AutoIncrement("ID").NotNull().AsPrimaryKey(),
             Column.String("NAME"),
             Column.Date("DATESTART"),
             Column.Int32("SOMENUMBER"),
          );

As you can see, it's using strings so you can create any table at runtime.

Cheers and good luck,

André Carlucci

Answer 2

You could look at ObjectDataBlocks if you are willing to write a provider for SqlLite and update the Query class to work with LINQ