LLBLGen Pro Typed Data Context Driver for LINQPad

A LLBLGen Pro Typed Data Context Driver for LINQPad which provides first-class support for LLBLGen Pro ILinqMetaData to enable, among other things, the generated entities to be shown in the Schema Explorer. Notable features include: schema explorer tool-tips, displaying SQL column information, grouping entities by schema and/or table prefix, ability to select additional assemblies and namespaces, ability to import connections from those of earlier LLBL versions, includes Four Custom Visualizers.

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, see bottom of page for examples.

Installation

Binary via LINQPad drivers gallery

  1. Run LINQPad
  2. Click 'Add connection'
  3. Click 'View more drivers'
  4. In the 'LINQPad Supplementary Data Context Drivers' page select the appropriate version:
    Download Alternative Driver for LLBLGen X.Y

Binary from file

  1. Download the LINQPad driver for the version of LLBLGen Pro you are using AW.LLBLGenV4.2.DataContextDriver.lpx, AW.LLBLGenV4.1.DataContextDriver.lpx, AW.LLBLGenV4.0.DataContextDriver.lpx, AW.LLBLGenV3.5.DataContextDriver.lpx, AW.LLBLGenV3.1.DataContextDriver.lpx (v2.2.1.0)
    AW.LLBLGenV3.0.DataContextDriverV2.0.lpx and AW.LLBLGenV2.6.DataContextDriver.lpx
  2. Do steps 1-3 above
  3. Browse to and select the *.lpx file that was downloaded

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

For version history see release notes in download pages
Current LLBLV4.2 and LLBL V5.0 version are at Version 3.2 and are only available via the LINQPad drivers gallery, for previous versions see Version 3.0, Version 2.2.1 and V2.3.1, Version 2.0, Version 1.1

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

image

And for Northwind with Adapter, with the adapter there is an option to specify a method to use to register Custom Function Mapping so they are available to LINQPad

image

UI Options

image

New in version 3 is the option to group the entities by schema and/or group them by table prefix.

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

  1. Exclude EntityBase Properties
  2. Default Linqpad behaviour
  3. Use the GridDataEditor included with the driver rather than the read-only LinqPad one, unpaged
  4. Use the GridDataEditor 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.

image

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.

image
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 V2.3.1 and later drivers 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 V3 or later 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 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 Four Custom Visualizers:  GridDataEditor, HierarchyEditor, Object Inspector and the Entity Browser., the appropriate assemblies and namespaces are automatically included.
Version 3 has the ability to import connections from earlier LLBL version connections

Difference between this and the Official LINQPad LLBLGen Pro drivers

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

  • Doesn't have the schema explorer tooltips nor displays SQL column information
  • 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 (they would have to be added to every query)
  • Doesn’t support grouping entities by Schema and/or table prefix
  • Doesn’t support Custom Function Mapping (they would have to be added to every query)
  • Does not create the adapter for SQL Server with CatalogNameUsage.Clear which means the it ignores the catalog name in connection string
  • Does not have ability to import connections from earlier LLBL version connections
  • Is a single Assembly (mine is 15)
  • Is more likely to work, I expect

Related links:

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

Last edited Aug 30, 2016 at 12:04 PM by JezzaT, version 123

Comments

No comments yet.