How can I use, in Visual Studio 2017, the "C# Interactive" window to query a source in my "Data Connections"

Question

I'm connected to an external SQL server in my "Data Connections" (in the "Server Explorer" view). I can right click my SQL source and click "New Query" to quickly look up data with SQL statements.

I would like to use LINQ instead and I think the "C# Interactive" window would be a good and quick way to do this. My problem is that I don't know how to access my 'open' data connection. The name of the database or tables are not recognized.

Answer 1

Yes, you can right click on your main project in Solution Explorer and click Initialize Interacive with Project. This will build you projects and import all the dlls into the interactive window for you. Then you can start scratching!

For example, using Entity Framework you will need to stand up your DbContext. Enter something like...

> var context = new My.Namespace.MyDataContext("blah blah blah");

Where I have written the "blah blah blah" you need to add your connection string. The interactive console does not know about your .config files so you need to provide the connection string.

Note: To be able to do this make sure you have the nameOrConnectionString constructor override on your data context.

Now that you have the context it is as simple as normally querying the context...

> context.Users.Where(u => u.IsActive).Select(u => u).ToList()

Important
Take note that I have left the semicolon (;) off the end of the query. This is important as it tells the console to output the value of the query/command/line of code. Nothing will happen if you leave this off.

Answer 2

I got this to work by creating a class library that opens a connection to an EF data model, importing the DLL into the C# interactive window, and executing Linq statements against the data model.

First, create the class library, add the EF data model, and modify your DbContext (entities) class to use the constructor that takes a connection string. You need to do this to use this library from the c# interactive window, because if you don't, the interactive window will be looking for an app.config file with the connection string.

public partial class YourDBEntities : DbContext
{
    public YourDBEntities(string connectionString)
        : base(connectionString)
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        throw new UnintentionalCodeFirstException();
    }

    ....
}

If your class library, add a class with a static method for getting a data context:

public class AccessorClass
{
    public static YourDBEntities GetDataContext()
    {
        return new YourDBEntities("metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string=\";data source=xxxxxxx;initial catalog=xxxxxxx;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework\";");
    }
}

Compile the class library, and then import the DLL into your interactive window, and query away:

> #r "C:\Path...\bin\Debug\YourClassLibrary.dll"
> using YourClassLibrary;
> using (var ctx = AccessorClass.GetDataContext())
. {
.     Console.Write(ctx.Orders.Where(c => c.ProjectID == 309).Count().ToString());
. }