LLBLGen Pro Typed Data Context Driver for LINQPad

I have written a LLBLGen Pro Typed Data Context Driver for LINQPad which provides first-class support for LLBLGen Pro ILinqMetaData to enable, amongst other things, the generated entities to be shown in the Schema Explorer.

Even if you are not using Linq you can still use LINQPad to run classic LLBL API or the newer QuerySpec queries by specifying the an IElementCreator rather than an ILinqMetaData type. (v2.x only).

Solutions Design have produced the Official LINQPad drivers for LLBLGen Pro v3.5 and 4.0, some of the differences are: it

• Has a simpler connection dialog
• Doesn't have the schema explorer tooltips mine has nor displays SQL column names
• Doesn't fully support SQL execution
• SQL trace is not executable
• Doesn't support DataAccessAdapter factories
• Doesn't support selection of Additional assemblies and namespaces
• Is a single Assembly (mine is about 5)
• Is more likely to work I expect

Installation

Binary via LINQPad drivers gallery (currently v2.2 now out of date)

1. Run LINQPad
2. Click 'Add connection'
3. Click 'View more drivers'
4. In the 'LINQPad Supplementary Data Context Drivers' page select either:
   Download Alternative Driver for LLBLGen 3.5+ 
   Download Alternative Driver for LLBLGen 3.1

Binary from file

1. Download the LLBLGen Pro version 3.1(V2.2.1), 3.5 and 4.0 (V2.3.1) Data Context Driver, version 3.0 and 3.1 Data Context Driver(V2.0) or version 2.6 Data Context Driver(V1.1)
2. Do steps 2-4 above
3. Browse to and select one of: AW.LLBLGenV4.0.DataContextDriver.lpx, AW.LLBLGenV3.5.DataContextDriver.lpx, AW.LLBLGenV3.1.DataContextDriver.lpx, 
   AW.LLBLGenV3.1.DataContextDriverV2.0.lpx,  AW.LLBLGenV3.0.DataContextDriverV2.0.lpx or AW.LLBLGenV2.6.DataContextDriver.lpx

Source

1. Get the source
2. Compile AW.LINQPad.sln or 'AW with Everything.sln' (you will probably need to fix the reference to LINQPad.exe)
3. Run AW.LLBLGen.DataContextDriver\bin\Debug\DevDeploy.bat (or DevDeploy4.bat)
4. Then in LINQPad:
   1. Click add connection
   2. Select AW LLBL Driver, click next

Example usage

In the connection dialog put in:

1. AW.Data.dll (with full path)
2. AW.Data.Linq.LinqMetaData (type or click choose)
3. Either AW.Win.exe.config or data source=(local)\sqlexpress;initial catalog=AdventureWorks;integrated security=SSPI

Options

There are 4 options for display the results of a query in a grid

1. Exclude EntityBase Properties
2. Default Linqpad behaviour
3. Use the Editable Data Grid included with the driver rather than the read-only LinqPad one
4. Use the Editable Data Grid with a page size of 10

DataAccessAdapter factory

There is an alternative way of creating the adapter and that is to have a static DataAccessAdapter factory method with this signature:

Func<string, IDataAccessAdapter> 
e.g. public static DataAccessAdapterBase CreateDataAccessAdapter(string connectionString)

This allows you to have custom code executed when the adapter is created and maybe select the appropriate adapter based on the connection string (or whatever data the string parameter has). The assembly, type and method name must be supplied.

Default connection properties

If you want to have a lot of connections with similar properties you can specify defaults to populate new connections. These can set by either the 'save as default' button in the first tab of the connection dialog or by editing them directly in the third tab shown below. This allows you to easily clone a connection say if you want to have the same code connected to multiple databases. This is redundant now that LINQPad has the ‘Create Similar Connection…’ feature.

Additional assemblies and namespaces

The driver adds these namespaces and assemblies to the ones LINQPad provides

"SD.LLBLGen.Pro.ORMSupportClasses", "SD.LLBLGen.Pro.LinqSupportClasses", "AW.Helper", 
"AW.Helper.LLBL", "AW.Winforms.Helpers.DataEditor", "AW.Winforms.Helpers.LLBL", "AW.LinqPadExtensions", 
"AW.LLBLGen.DataContextDriver", "AW.LLBLGen.DataContextDriver.Static"

"SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll", "SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll",
"AW.Helper.dll", "AW.Helper.LLBL.dll", "System.Windows.Forms.dll","AW.Winforms.Helpers.dll", 
"AW.Winforms.Helpers.LLBL.dll", "AW.LinqPadExtensions.dll"

If you want to have additional assemblies and namespaces used in the queries against your typed data context (ILinqMetaData) these can be specified in ‘Driver’ tab of the connection dialog shown above or in the ‘Additional Assemblies and Namespaces’ tab shown below.

The ‘Driver’ tab assemblies and namespaces are global to the AW.LLBLGen.DataContextDriver, while the ‘Additional Assemblies and Namespaces’ (version 2.3 and later only) are per connection. The additional assemblies can have relative or absolute paths. This an alternative to specifying them in each query. The ‘Add namespaces from selected assemblies’ button adds all the namespaces from any selected assemblies in the ‘Additional Assemblies’ grid plus all the assemblies defined in the driver.

SQL Translation Tab

The LLBL version 3.5 driver will show a non-executable SQL log in the SQL Translation Tab when a query is run using the TraceHelper.QueryExecutionSwitch. In earlier versions or to view executable SQL you must define an event called SQLTraceEvent in your data adapters or, if using LLBL 3.x selfservicing, a static event of that name in the CommonDaoBase class, e.g

public static event EventHandler<SQLTraceEventArgs> SQLTraceEvent;

where SQLTraceEventArgs is a descendent of EventArgs and has a public SD.LLBLGen.Pro.ORMSupportClasses.IQuery property Query. For example:

public class SQLTraceEventArgs : EventArgs
{
  public IQuery Query { get; private set; }
  public SQLTraceEventArgs(IQuery query)
  {
    if (query == null) throw new ArgumentNullException("query");
    Query = query;
  }
}

See SQLTraceEventArgs.cs and CommonDaoBase.Extended.cs in the LLBL Pro v3.1 branch for an example of how to do this.
For selfservicing the SQL trace only works for entity queries but not projections.

For Adapter you could add references to AW.Helper.dll and AW.Helper.LLBL.dll and then put this in your DataAccessAdapter:

// __LLBLGENPRO_USER_CODE_REGION_START CustomDataAccessAdapterCode

/// <summary>
/// Event which is raised whenever a query is executed. Use this event to perform SQL tracing. 
/// </summary>
public event EventHandler<SQLTraceEventArgs> SQLTraceEvent;

/// <summary>
/// Called whenever a query is executed.
/// </summary>
/// <param name="query">The query.</param>
protected void OnExecuteQuery(IQuery query)
{
  if (SQLTraceEvent != null) 
    SQLTraceEvent(this, new SQLTraceEventArgs(query));
}

/// <summary>
/// 	Executes the passed in action query and, if not null, runs it inside the passed in transaction.
/// </summary>
/// <param name = "queryToExecute">ActionQuery to execute.</param>
/// <returns>execution result, which is the amount of rows affected (if applicable)</returns>
public override int ExecuteActionQuery(IActionQuery queryToExecute)
{
  OnExecuteQuery(queryToExecute);
  return base.ExecuteActionQuery(queryToExecute);
}

/// <summary>
/// 	Creates a new Select DQ for the fields passed in using the parameters specified.
/// </summary>
/// <param name = "fieldsToFetch">fields to fetch using the select</param>
/// <param name = "persistenceInfoObjects">persistence info objects for the fields</param>
/// <param name = "filter">filter to use for the where clause</param>
/// <param name = "maxNumberOfItemsToReturn">max. amount of rows to return</param>
/// <param name = "sortClauses">sort clause specifications to use</param>
/// <param name = "relationsToWalk">relations to walk to build the FROM clause</param>
/// <param name = "allowDuplicates">flag to specify if duplicates should be returned</param>
/// <param name = "groupByClause">group by clause to embed in the query</param>
/// <param name = "pageNumber">The page number to retrieve</param>
/// <param name = "pageSize">the page size to retrieve</param>
/// <returns>ready to use query to use.</returns>
protected override IRetrievalQuery CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects,
	IPredicateExpression filter, long maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk,
	bool allowDuplicates, IGroupByCollection groupByClause, int pageNumber, int pageSize)
{
  var query = base.CreateSelectDQ(fieldsToFetch, persistenceInfoObjects, filter, maxNumberOfItemsToReturn, sortClauses, relationsToWalk,
  allowDuplicates, groupByClause, pageNumber, pageSize);
  OnExecuteQuery(query);
  return query;
}

// __LLBLGENPRO_USER_CODE_REGION_END

Extras

The driver also includes this DataEditor, this HierarchyEditor and this Entity Data Browser., the appropriate assemblies and namespaces are automatically included.

Related links:

• Official LINQPad driver for LLBLGen Pro v3.5 from Solutions Design
• LINQPad thread on the LLBLGen Pro forum.
• LLBLGen Pro thread on LINQPad forum.
• LINQPad Data Context Extensibility Model which offers first-class support in LINQPad for querying other data sources.
• The .linq files in the SampleQueryFiles folder.
• LINQPad Supplementary Data Context Drivers

Example Non Linq LLBL queries (Language C# Statement):

AdventureWorks (selfservicing)

QuerySpec

var qf = new AW.Data.FactoryClasses.QueryFactory(); 
var q = qf.Address.Where(AddressFields.AddressID == 3); 
var addresses = new AddressCollection(); 
addresses.GetMulti(q); 
addresses.Dump();

LLBL Classic Query API

var soh = new AW.Data.EntityClasses.SalesOrderHeaderEntity (43659);
soh.GetMultiSalesOrderDetails(false).Dump();

Northwind (adapter)

QuerySpec

using (var adapter = this.AdapterToUse)
{
  var qf = new Northwind.DAL.FactoryClasses.QueryFactory();
  var q = qf.Customer
            .From(QueryTarget.InnerJoin(CustomerEntity.Relations.OrderEntityUsingCustomerId)
                             .InnerJoin(OrderEntity.Relations.OrderDetailEntityUsingOrderId)
                             .InnerJoin(OrderDetailEntity.Relations.ProductEntityUsingProductId))
            .Where(Northwind.DAL.HelperClasses.ProductFields.ProductId == 1);

  adapter.FetchQuery(q).Dump();
}

LLBL Classic Query API

var product = new Northwind.DAL.EntityClasses.ProductEntity(10);
this.AdapterToUse.FetchEntityCollection(product.OrderDetails, product.GetRelationInfoOrderDetails()); 
product.OrderDetails.Dump();

Authorization example where 'this' is an IElementCreator rather than an ILinqMetaData

SD.LLBLGen.Pro.Examples.Authorization.Identity.LoginHelper.Login("cr1","cr1");
var order = new SD.LLBLGen.Pro.Examples.Authorization.EntityClasses.OrdersEntity(10248);
LLBLGenStaticDriver.GetAdapter(this).FetchEntityCollection(order.OrderDetails, order.GetRelationInfoOrderDetails());
order.OrderDetails.Dump();

Note: For IElementCreator LLBLGenStaticDriver.GetAdapter(this) has to be used instead of this.AdapterToUse