How to reuse a query on different database connections?

I have a single database holding many application-specific Serilog tables. To understand my example, let's say I have 2 applications (Weather and Store), that means I'll have 2 tables Weather_StructureLog and Store_StructureLog. Both tables have identical schema because they were built with the SQL create table command.

To help me monitor the different applications, I have a LINQPad program that shows me the recent logs. It remembers the last ID so the next time I run the program, I see the logs that were recorded from the last execution.

I want to reduce the repeated code and make it easier to add another table.

I wrote this function to return a TypedDataContext for the database.

TypedDataContext SetDynamicConnection(string databaseName)
{
    new { ServerName, databaseName }.Dump("Connection Info");
    var dynamicConnection = new SqlConnection($"Data Source={ServerName};Integrated Security=SSPI;Initial Catalog={databaseName};app=LinqPad");

    return new TypedDataContext(dynamicConnection);
}

Using that function, I get dbContext, but I still have to explicitly use the tables name. In my program, I have this duplication of the query my 2 supported tables. If I wanted to add a 3rd, then I have to copy/paste for the new one.

var baseQueryable = new List<SerilogSchemaTable>();

switch (tableName)
{
    case "Weather_StructureLog":
        {
            baseQueryable =
            dbContext.Weather_StructureLog
                .Where(ln => ln.TimeStamp > DateTime.Now.AddMonths(-1))
                .OrderByDescending(ln => ln.Id)
                .Select(ln => new SerilogSchemaTable()
                {
                    Exception = ln.Exception,
                    Id = ln.Id,
                    Level = ln.Level,
                    Message = ln.Message,
                    MessageTemplate = ln.MessageTemplate,
                    Properties = ln.Properties,
                    TimeStamp = ln.TimeStamp
                })
                .ToList<SerilogSchemaTable>();
            break;
        }
    case "Store_StructureLog":
        {
            baseQueryable =
            dbContext.Store_StructureLog
                .Where(ln => ln.TimeStamp > DateTime.Now.AddMonths(-1))
                .OrderByDescending(ln => ln.Id)
                .Select(ln => new SerilogSchemaTable()
                {
                    Exception = ln.Exception,
                    Id = ln.Id,
                    Level = ln.Level,
                    Message = ln.Message,
                    MessageTemplate = ln.MessageTemplate,
                    Properties = ln.Properties,
                    TimeStamp = ln.TimeStamp
                })
                .ToList<SerilogSchemaTable>();
            break;
        }
}

The only difference in the 2 cases is the table's name. Any idea how to remove those repeated blocks of code?

My question is not these

• Reuse of a LINQ query
• LINQ to SQL: Reuse lambda expression

You could use ExecuteQueryDynamic , e.g.

    var baseQueryable = dbContext.ExecuteQueryDynamic(
             $@"Select * from {tableName} 
                where TimeStamp > @p0 
                order by ID DESC", 
             DateTime.Now.AddMonths(-1))
    .Select(ln => new SerilogSchemaTable()
    {
        Exception = ln.Exception,
        Id = ln.Id,
        Level = ln.Level,
        Message = ln.Message,
        MessageTemplate = ln.MessageTemplate,
        Properties = ln.Properties,
        TimeStamp = ln.TimeStamp
    }).ToList();