Can I dynamically reference multiple databases in a LINQPad script?

Question

I have a database with a table describing multiple entities, with one column being the name of another database holding data for that entity. All entity databases are on the same SQL Server as the one listing them and all have an identical schema.

I know that I can use Ctrl-drag to add the additional databases to my script but what I actually want is to do this dynamically from the database name. Something like this.

var entities = ParentDatabase.EntityList
    .Where(e => ??)
    .Select(e => new { e.Id, e.DatabaseName });

var results = new List<ResultCarrier>();

foreach (var entity in entities)
{
    results.AddRange(
        GetDataContextFor(entity.DatabaseName).SomeTable
            .Select(t => new ResultCarrier() 
                { 
                    EntityId = e.Id, 
                    Column1 = t.Column1,
                    Column2 = t.Column2,
                    ... 
                }));
}

// further process combined results

Is this possible?

I see that the type of one of these databases is LINQPad.User.DatabaseNameTypes.TypedDataContext and wondered whether, as each database has the same schema, there might be a base class that I could use in some way to achieve this.

Answer 1

@sgmoore's answer got me on the right track. I hadn't come across ExecuteQuery in LINQPad and I was able to use it to achieve what I wanted. Below is the code I ended up with. I will now extend it to further retrieve data from a service and join it to databaseLocations to give the final result I'm after.

void Main()
{
    var organisations = ExecuteQuery<OrganisationCarrier>(@"
        SELECT do.GroupId [Id], o.sOrganisationName [Name], o.sConnectDatabase [Database]
          FROM dbo.Organisation o
          INNER JOIN dynamix.Organisations do ON o.liOrgID = do.OrganisationID
          INNER JOIN dynamix.OrganisationFeatures oft ON do.OrganisationKey = oft.OrganisationKey
          INNER JOIN dynamix.Features ft ON oft.FeatureKey = ft.FeatureKey
          WHERE ft.FeatureName = 'LightningLocations'").ToList();

    var databaseLocations = new List<LocationExtract>();

    foreach (var organisation in organisations)
    {
        this.Connection.ConnectionString = $"Data Source={this.Connection.DataSource};Integrated Security=SSPI;Initial Catalog={organisation.Database};app=LINQPad";

        databaseLocations.AddRange(ExecuteQuery<LocationCarrier>(@"
            SELECT dml.DmxLocationId [Id], ml.sLocationName [Name], ml.bDeleted [IsDeleted]
                FROM dynamix.MapLocations dml
                INNER JOIN dbo.MapLocations ml ON dml.FmLocationId = ml.liLocationID")
            .Select(l => new LocationExtract(organisation.Id, l.Id, l.Name, l.IsDeleted)));
    }

    databaseLocations.Dump();
}

class OrganisationCarrier
{
    public long Id { get; set; }
    public string Name { get; set; }
    public string Database { get; set; }
}

class LocationCarrier
{
    public long Id { get; set; }
    public string Name { get; set; }
    public bool IsDeleted { get; set; }
}

class LocationExtract
{
    public long OrganisationId { get; }
    public long LocationId { get; }
    public string Name { get; }
    public bool IsDeleted { get; }

    public LocationExtract(long organisationId, long locationId, string name, bool isDeleted)
    {
        OrganisationId = organisationId;
        LocationId = locationId;
        Name = name;
        IsDeleted = isDeleted;
    }
}

Answer 2

TypedDataContext is your base class, and you can just create a new instance of this and pass it the sql connection string.

You can find your current connection string using

this.Connection.ConnectionString.Dump();

For example, I use Integrated Security and I have a little routine that goes through all the database in my server and dumps out a table, so I use the following routine.

var databases = ExecuteQuery<String>("SELECT name FROM sys.databases").ToList();

foreach(var r in databases)
{
    switch (r)
    {
        case "master" :
        case "tempdb" :
        case "model" :
        case "msdb" :
            break;
        default:
           try
           {
               string newConnectionString = String.Format("Data Source={0};Integrated Security=SSPI;Initial Catalog={1};app=LINQPad", this.Connection.DataSource, r);

               var dc = new TypedDataContext(newConnectionString);

               dc.Table.Dump(r);

            }
            catch (Exception ex)
            {
                ex.Message.Dump(r);
            }
            break;

      }

}