Show Blogger Panel Hide Blogger Panel
Alex Yakunin

November 29, 2009

Using DataObjects.Net with LINQPad

LINQPad is a very nice tool for testing LINQ queries. Unfortunately, there is no public extension API allowing DataObjects.Net to be integarted there natively. But there are workarounds.

Let's start from screenshots:
image image

It is possible to make it showing SQL for queries, but I recommend using SQL Profiler for this (it shows batches):
image image
image image

You may find the queries shown here are taken from nearly the same example as I reviewed in my previous post:
  var query = 
  from customer in Query<Customer>.All
  select new {
    Customer = customer,
    Orders = customer.Orders,
    First5Orders = (
      from order in Query<Order>.All
      where order.Customer==customer
      orderby order.Id
      select order
      ).Take(5)
    };
  
  string.Format("Total entities: {0}", query.Count()).Dump();
  foreach (var item in query) {
    var subqueryResult = item.First5Orders.ToList(); 
    // Actual execution must happen here, 
    // but see the comments below.
    string.Format("{0} in first 5, {1} total",
      subqueryResult.Count,
      item.Orders.Count).Dump();
  }

Above SQL Profiler output perfectly shows how we prefetch EntitySets. 32 is default count of items to prefetch per each EntitySet. If there are more items, they'll be loaded by additional query (or you should specify the limit using prefetch API).

So, how to “teach” LINQPad to run DataObjects.Net queries?

The fastest way: download DO4Template.zip, extract DO4Template.linq from it, open it in LINQPad and go to step 11. DO4Template.linq is regular text file with the following content:
<Query Kind="Program">
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Bin\Latest\Xtensive.Adapters.log4net.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Bin\Latest\Xtensive.Core.Aspects.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Bin\Latest\Xtensive.Core.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Bin\Latest\Xtensive.Core.Testing.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Bin\Latest\Xtensive.Core.Weaver.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Bin\Latest\Xtensive.Indexing.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Bin\Latest\Xtensive.Integrity.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Bin\Latest\Xtensive.Modelling.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Bin\Latest\Xtensive.PluginManager.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Bin\Latest\Xtensive.Sql.All.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Bin\Latest\Xtensive.Sql.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Bin\Latest\Xtensive.Sql.Oracle.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Bin\Latest\Xtensive.Sql.PostgreSql.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Bin\Latest\Xtensive.Sql.SqlServer.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Bin\Latest\Xtensive.Sql.VistaDb.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Bin\Latest\Xtensive.Storage.All.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Bin\Latest\Xtensive.Storage.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Bin\Latest\Xtensive.Storage.Indexing.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Bin\Latest\Xtensive.Storage.Indexing.Model.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Bin\Latest\Xtensive.Storage.Model.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Bin\Latest\Xtensive.Storage.Providers.Index.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Bin\Latest\Xtensive.Storage.Providers.Sql.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Bin\Latest\Xtensive.Storage.Rse.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Bin\Latest\Xtensive.TransactionLog.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Bin\Latest\Xtensive.TransactionLog.Providers.FileSystem.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Samples\Xtensive.Storage.Samples.Model\bin\Debug\Microsoft.Practices.ServiceLocation.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Samples\Xtensive.Storage.Samples.Model\bin\Debug\Mono.Security.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Samples\Xtensive.Storage.Samples.Model\bin\Debug\Npgsql.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Samples\Xtensive.Storage.Samples.Model\bin\Debug\PostSharp.Laos.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Samples\Xtensive.Storage.Samples.Model\bin\Debug\PostSharp.Public.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Samples\Xtensive.Storage.Samples.Model\bin\Debug\Xtensive.Storage.Samples.Common.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\DataObjects.Net\Samples\Xtensive.Storage.Samples.Model\bin\Debug\Xtensive.Storage.Samples.Model.dll</Reference>
  <Reference>&lt;ProgramFiles&gt;\Reference Assemblies\Microsoft\Framework\.NETFramework\v3.5\Profile\Client\WindowsBase.dll</Reference>
  <Reference>&lt;RuntimeDirectory&gt;System.Transactions.dll</Reference>
  <Namespace>Xtensive.Core</Namespace>
  <Namespace>Xtensive.Core.Disposing</Namespace>
  <Namespace>Xtensive.Storage</Namespace>
  <Namespace>Xtensive.Storage.Configuration</Namespace>
  <Namespace>Xtensive.Storage.Linq</Namespace>
  <Namespace>Xtensive.Storage.Rse</Namespace>
  <Namespace>Xtensive.Storage.Samples.Model</Namespace>
</Query>

// You must run DataObjects.Net Console Sample before using this template!

public Domain BuildDomain()
{
  var c = new DomainConfiguration();
  c.ConnectionInfo = new UrlInfo(
    @"sqlserver://localhost/DO40-Tests");
  c.Types.Register(typeof(Company).Assembly);
  c.UpgradeMode = DomainUpgradeMode.Validate;
  return Domain.Build(c);
}

public static void Display<TEntity>(IQueryable<TEntity> query)
{
  using (Xtensive.Storage.Rse.Providers.EnumerationScope.Open()) {
  var recordSet = ((Queryable<TEntity>) query).Compiled;
  Xtensive.Storage.Rse.Compilation.CompilationContext.Current.Compile(
    recordSet.Provider).ToString().Dump();
  }
  query.Dump();
}

void Main()
{
  var domain = BuildDomain();
  using (Session.Open(domain))
  using (var scope = Transaction.Open()) {
    Query();
    // scope.Complete();
  }
}

void Query()
{
  var query = 
    from c in Query<Contact>.All
    orderby c.FirstName, c.LastName
    select new {
      c.FirstName, 
      c.LastName, 
      CompanyName = c.Company.Name
      };
  Display(query); // Our own Display method
  query.Dump(); // Standard LINQPad method
}

If you would like to know how this file was created,

1. Open LINQPad and select “File” – “New Query” (Ctrl-N) there:
image

2. Open “Query” - “Advanced Query Properties” (F4):
image

3. Click “Add'” on “Additional References” tab there, and than - “Browse”:
image image

4. Navigate to your DataObjects.Net installation folder and open “Bin\Latest” folder there:
image

5. Select all the assemblies there, and add them:
image image

6. Add reference to WindowsBase.dll (from .NET 3.5) and System.Transactions.dll (from .NET 2.0):
image image

7. Add reference to your model and all its dependencies. In our case it will be Xtensive.Storage.Samples.Model and its dependencies:
image

8. Go to “Additional Namespace Imports” tab and add the following namespaces there:
image
The last namespace there is namespace of your model.

9. Select “Language” - “C# Program” in LINQPad query window:
image

10. Add the following BuildDomain, Display, Main and Query methods:
public Domain BuildDomain()
{
  var c = new DomainConfiguration();
  c.ConnectionInfo = new UrlInfo(
    @"sqlserver://localhost/DO40-Tests");
  c.Types.Register(typeof(Company).Assembly);
  c.UpgradeMode = DomainUpgradeMode.Validate;
  return Domain.Build(c);
}

public static void Display<TEntity>(IQueryable<TEntity> query)
{
  using (Xtensive.Storage.Rse.Providers.EnumerationScope.Open()) {
  var recordSet = ((Queryable<TEntity>) query).Compiled;
  Xtensive.Storage.Rse.Compilation.CompilationContext.Current.Compile(
    recordSet.Provider).ToString().Dump();
  }
  query.Dump();
}

void Main()
{
  var domain = BuildDomain();
  using (Session.Open(domain))
  using (var scope = Transaction.Open()) {
    Query();
    // scope.Complete();
  }
}

void Query()
{
  var query = 
    from c in Query<Contact>.All
    orderby c.FirstName, c.LastName
    select new {
      c.FirstName, 
      c.LastName, 
      CompanyName = c.Company.Name
      };
  Display(query); // Our own Display method
  query.Dump(); // Standard LINQPad method
}

11. Run the original application to populate the data. In our case this is Console Sample (don’t forget to select “Microsoft SQL Server” there):
image

12. Press F5 to run the program we just wrote. You must see the following output:
image

That’s it. Now you can write your own queries and dump their results there. But remember:
  • Dump() method in LINQPad dumps all the references recursively. This means it will stuck on paired associations and will dump a part of Domain.Model while dumping Entity.Type property.
  • This explains why we use anonymous type projections in our queries: they restrict Dump() greediness.
  • As you see, it's pretty easy to write your own Dump method here.
Note that such an approach does not bring any huge benefits in comparison to e.g. running the same queries in separate project in Visual Studio. You get the ability to visualize any query result, and that's all. But I think this is pretty good for testing queries quickly. All you must do is to create your own .linq file for your project.