How to get better table alias names for SQL statements created by Entity Framework?

Question

Is it possible to control which table alias names Entity Framework uses for the SQL statements it creates? For example, instead of

SELECT [Extent1].[Id] AS [Id]
FROM [dbo].[Orders] AS [Extend1]
INNER JOIN [dbo].[Customers] AS [Extend2] ON [Extend1].[CustomerId] = [Extend2].[Id]
WHERE [Extent2].[Name] = 'xyz'

I'd like to get

SELECT [order].[Id] AS [Id]
FROM [dbo].[Orders] AS [order]
INNER JOIN [dbo].[Customers] AS [customer] ON [order].[CustomerId] = [customer].[Id]
WHERE [customer].[Name] = 'xyz'

or something similar, as long as I don't have to remember which extend number is which table. Of course in this simple sample it's no problem, but in queries where you have [Extend1] to [Extend7] it's really getting complicated.

Answer 1

You could use IDbCommandInterceptor and override the CommandText.

I just created a very simple logic to replace the alias name using regular expression.

Following code

var query = (from f1 in db.Foos
            where f1.ParentFooId == null
            join f2 in db.Foos on f1.FooId equals f2.ParentFooId
            select f2).ToArray();

by default will become something like

SELECT 
    [Extent1].[FooId] AS [FooId], 
    [Extent2].[FooId] AS [FooId1], 
    [Extent2].[FooName] AS [FooName], 
    [Extent2].[ParentFooId] AS [ParentFooId], 
    [Extent2].[ParentFoo_FooId] AS [ParentFoo_FooId]
    FROM  [dbo].[Foos] AS [Extent1]
    INNER JOIN [dbo].[Foos] AS [Extent2] ON [Extent1].[FooId] = [Extent2].[ParentFooId]
    WHERE [Extent1].[ParentFooId] IS NULL
go

and then I intercepted the command text on ReaderExcecuting.

public void ReaderExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<System.Data.Common.DbDataReader> interceptionContext)
{
    var aliasLookup = new Dictionary<string, string>();
    var tableLookup = new Dictionary<string, int>();

    var text = command.CommandText;
    // Looking something like "[dbo].[Foos] AS [Extent1]"
    var pattern = @"\[dbo\]\.\[([\w\d]*)\] AS \[(Extent\d*)\]";
    var matches = Regex.Matches(text, pattern);
    foreach(Match m in matches)
    {
        if (m.Groups.Count == 3)
        {
            var tableName = m.Groups[1].Value;
            var aliasName = m.Groups[2].Value;

            var replacement = tableName;
            if (tableLookup.ContainsKey(tableName)) replacement += tableLookup[tableName]++;
            else tableLookup.Add(tableName, 2);

            aliasLookup.Add(aliasName, replacement);
        }
    });

    foreach(var pair in aliasLookup)
    {
        var oldAliasName = string.Format("[{0}]", pair.Key);
        var newAliasName = string.Format("[{0}]", pair.Value);
        command.CommandText = command.CommandText.Replace(oldAliasName, newAliasName);
    }
}

The result would look like

SELECT 
    [Foos].[FooId] AS [FooId], 
    [Foos2].[FooId] AS [FooId1], 
    [Foos2].[FooName] AS [FooName], 
    [Foos2].[ParentFooId] AS [ParentFooId], 
    [Foos2].[ParentFoo_FooId] AS [ParentFoo_FooId]
    FROM  [dbo].[Foos] AS [Foos]
    INNER JOIN [dbo].[Foos] AS [Foos2] ON [Foos].[FooId] = [Foos2].[ParentFooId]
    WHERE [Foos].[ParentFooId] IS NULL
go

This is just giving you the idea how to do it, the logic hasn't been tested for complex query and it should be used only for debugging.