Customize table and column name generation

Omnius

I've upgraded to LinqPad 6 and since I can't load my existing application Data Context DLLs (because they are .net 6 and not .net Core) I'm trying to find a way to keep most of my scripts running. The biggest thing that I get by loading these DLLs are the renamed tables, columns, and connection variables. So, I thought maybe I could come up with a way to continue to import these as table aliases or some such. Our database uses prefixes on the table names (like xxx_Customers, yyy_Users) but when the applications were written these prefixes were ignored (so the C# classes are just Customers and Users). There are also a few column names that were changed (mostly to avoid name collisions with C#).

The goal here is to be able to cut/paste code from the application and have a high chance of it running without having to change every table name and connection variable. I have a set of shim classes that I use that mimic classes from our application to make this work, but this method still assumes that the table names and connection variables will align.

What I'm looking for here is the best way to do this in LinqPad 6.

• Should I create a class with properties that map from one name to another (Customers => xxx_Customers)? If this is the right way to go would it be easier to do with inheritance from UserQuery or through composition? Note that while this will work for the table names, connection variables will still have the long-form name, so this is at best a partial solution.
  
• Should I create a custom Data Context Driver for LinqPad? Can I inherit from the existing SQL Server Data Context Driver (so I don't have to rewrite so much)?
  
• Is there a way to customize the way the existing Data Context generates table and column names so that I can override the ones that are different?
  
• Other?
  

Thanks in advance for any suggestions.

JoeAlbahari

If you don't have too many data contexts, the best solution might be to use VS to create custom data contexts using EF Core 3 instead of LINQ-to-SQL, and consume them using the new EF driver in LINQPad.

You can ease the transition by defining extension methods like this:

public static class LinqToSqlCompatibilityExtensions
{
    public static void InsertOnSubmit<T> (this Microsoft.EntityFrameworkCore.DbSet<T> table, T entity) where T : class
        => table.Add (entity);

    public static void InsertAllOnSubmit<T> (this Microsoft.EntityFrameworkCore.DbSet<T> table, IEnumerable<T> entities) where T : class
        => table.AddRange (entities);

    public static void DeleteOnSubmit<T> (this Microsoft.EntityFrameworkCore.DbSet<T> table, T entity) where T : class
        => table.Remove (entity);

    public static void DeleteAllOnSubmit<T> (this Microsoft.EntityFrameworkCore.DbSet<T> table, IEnumerable<T> entities) where T : class
        => table.RemoveRange (entities);
}

Omnius

Thanks for the reply Joe.

Reverse engineering the databases might get me close enough. The two biggest issues with that will be that EF and L2SQL handle connection tables (among other things) very differently, and this is an active application so now the team would have to maintain both implementations when the schema changes (it would be more than just reverse engineering again since we would need to modify the names that have been changed in the same way as the existing L2SQL implementation). If that ends up being the only real option then it might be easier to just use both versions 5 and 6 (not a dig, I get that Core is the official future).

I didn't go into detail before, but what I was considering was parsing the DBML files and building the table and column aliases on the fly (but I'm not really sure at what layer this might work in LinqPad). The thinking was that I would either dynamically generate a shim class or read the DBML from a custom data context driver. Either of these would not require managing the schemas in multiple places.

I've parsed the DBML for other project tools, so I have a feel for how complex that will be, but I don't quite get how to interrupt the stream in which LinqPad builds the dynamic classes it uses for scripts.

Omnius

As an aside, do you know if .net 5 will alleviate this problem? I've read some about it and it seems some people believe this is true, but to me it looks like .net 5 will just be .net Core renamed (mainly to make it clear that "Core is the future, so stop fighting it already") and not a fundamental shift to combine support for older libraries with newer libraries.

JoeAlbahari

As I understand, Microsoft are not planning to significantly increase support for older libraries in netcore5. I would be very surprised if LINQ-to-SQL comes back.