Let's start from screenshots:
It is possible to make it showing SQL for queries, but I recommend using SQL Profiler for this (it shows batches):
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><ProgramFiles>\DataObjects.Net\Bin\Latest\Xtensive.Adapters.log4net.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Bin\Latest\Xtensive.Core.Aspects.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Bin\Latest\Xtensive.Core.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Bin\Latest\Xtensive.Core.Testing.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Bin\Latest\Xtensive.Core.Weaver.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Bin\Latest\Xtensive.Indexing.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Bin\Latest\Xtensive.Integrity.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Bin\Latest\Xtensive.Modelling.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Bin\Latest\Xtensive.PluginManager.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Bin\Latest\Xtensive.Sql.All.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Bin\Latest\Xtensive.Sql.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Bin\Latest\Xtensive.Sql.Oracle.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Bin\Latest\Xtensive.Sql.PostgreSql.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Bin\Latest\Xtensive.Sql.SqlServer.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Bin\Latest\Xtensive.Sql.VistaDb.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Bin\Latest\Xtensive.Storage.All.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Bin\Latest\Xtensive.Storage.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Bin\Latest\Xtensive.Storage.Indexing.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Bin\Latest\Xtensive.Storage.Indexing.Model.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Bin\Latest\Xtensive.Storage.Model.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Bin\Latest\Xtensive.Storage.Providers.Index.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Bin\Latest\Xtensive.Storage.Providers.Sql.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Bin\Latest\Xtensive.Storage.Rse.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Bin\Latest\Xtensive.TransactionLog.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Bin\Latest\Xtensive.TransactionLog.Providers.FileSystem.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Samples\Xtensive.Storage.Samples.Model\bin\Debug\Microsoft.Practices.ServiceLocation.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Samples\Xtensive.Storage.Samples.Model\bin\Debug\Mono.Security.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Samples\Xtensive.Storage.Samples.Model\bin\Debug\Npgsql.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Samples\Xtensive.Storage.Samples.Model\bin\Debug\PostSharp.Laos.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Samples\Xtensive.Storage.Samples.Model\bin\Debug\PostSharp.Public.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Samples\Xtensive.Storage.Samples.Model\bin\Debug\Xtensive.Storage.Samples.Common.dll</Reference>
<Reference><ProgramFiles>\DataObjects.Net\Samples\Xtensive.Storage.Samples.Model\bin\Debug\Xtensive.Storage.Samples.Model.dll</Reference>
<Reference><ProgramFiles>\Reference Assemblies\Microsoft\Framework\.NETFramework\v3.5\Profile\Client\WindowsBase.dll</Reference>
<Reference><RuntimeDirectory>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:
2. Open “Query” - “Advanced Query Properties” (F4):
3. Click “Add'” on “Additional References” tab there, and than - “Browse”:
4. Navigate to your DataObjects.Net installation folder and open “Bin\Latest” folder there:
5. Select all the assemblies there, and add them:
6. Add reference to WindowsBase.dll (from .NET 3.5) and System.Transactions.dll (from .NET 2.0):
7. Add reference to your model and all its dependencies. In our case it will be Xtensive.Storage.Samples.Model and its dependencies:
8. Go to “Additional Namespace Imports” tab and add the following namespaces there:
The last namespace there is namespace of your model.
9. Select “Language” - “C# Program” in LINQPad query window:
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):
12. Press F5 to run the program we just wrote. You must see the following output:
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.
Hi Alex. Nice article! The upcoming LINQPad preview features an extensibility model that will let you write a driver to provide first-class support for other ORMs. Let me know if you're interested in writing a driver. Joe
ReplyDeleteSurely we are ;)
ReplyDeleteBtw, it would be perfect if there is an API allowing us to affect on LINQPad's decision on what to display. I wrote we support true inverse associations, so loops in references aren't rare at all in case with DO4. Moreover, we provide few additional properties like Entity.Key & Entity.Type, that must not be displayed at all there.
I can create an exact description regarding Dump() method behavior change - if this is interesting, I can send it to you.