As I mentioned in my last post I'm trying to see how LINQ fits into a middle tier type business layer. LINQ to SQL works by providing strongly typed access through the underlying language to data objects contained in a database. But the process to get there basically requires code generation which effectively results in a static class structure.

There are many advantages to this approach including type discovery, Intellisense support, compiler type checking before you run your app and the ability to more easily write SQL code in your applications.

But there's also a large downside to this static linking: The problem is that everything is, well, static and that means if you need to do something that is dynamic it gets to be problematic. What am I talking about? Here's a simple example.

As I mentioned in my last post I tend to work with business objects and I have a base business object that provides core abstractions for many common operations. My thinking here is that LINQ to SQL might fit into that frame as a DAL to provide for at least most of the data access with additional support for ADO.NET also provided (for a variety of reasons - ORM by itself is rarely adequate even in the best of scenarios).

So a base business object wants to be generic - and you might have  a dynamic Load() method that's responsible for loading up entities based on a PK. While you can do this with LINQ to SQL natively you have to write code explicitly like this:

tt_customer Tcust = context.tt_customers.Single(c => c.Pk == pk);

For front end business code I would rather not have to think about a Lambda expression in my code so I'd rather like to see:

tt_customer cust = customer.Load(pk);

or maybe even less intrusive:

customer.Load(pk);

where the active entity is tracked internally ( like customer.Entity).

Ok, sounds simple enough right? We can create a concrete implementation easily enough with code like this:

public tt_customer Load(int pk)

{

    try

    {            

        Table<tt_customer> CustTable = context.tt_customers;

        tt_customer cust = CustTable.Single(c => c.Pk == pk);

        if (cust == null)

        {

            this.SetError("Invalid Pk");

            return null;

        }

        return cust;

    }

    catch (Exception ex)

    {

        this.SetError(ex);

        return null;

    }

    return null;

}

But this obviously is concrete in that it hard codes the primary key field, the context and the table name. So to create a generic version you might define a class like this:

    public class busBase<TEntity,TContext>
           where TEntity: class, new()
           where TContext: DataContext, new()
    

and implement a generic Load method. Except that I can't figure out how to do this truly generically:

public TEntity Load(int pk)

{

    try

    {

        Table<TEntity> table =  context.GetTable( typeof(TEntity) ) as Table<TEntity>;

        TEntity entity = table.Single(c => c.Pk == pk);

        if (entity == null)

        {

            this.SetError("Invalid Pk");

            return null;

        }

        return entity;

    }

    catch (Exception ex)

    {

        this.SetError(ex);

        return null;

    }

    return null;

}

The problem is the line in bold. In order to retrieve a Single() I have to provide a strongly typed property (c.Pk) reference. There's no way that I can see to effectively make that Lambda expression dynamic even if I know what the name of the PK field is.

In previous versions of my bus object there's a primary key field and type provided as part of the schema and because that code used pure ADO.NET and *strings* to build SQL it's easy to construct a query that properly reflected the dynamic Pk field. It's as simple as this:

SqlCommand cmd = this.GetCommand("select * from " + this.PkField + "=@Pk", this.CreateParameter("@Pk", pk));

this.LoadBase(cmd);

where LoadBase is responsible for loading the data and feeding the entity.

But this sort of dynamic thing can not be done with LINQ to SQL because as far as I can tell there's no way to tell LINQ to SQL to create a dynamic query expression short of getting real low level in building a Lambda expression tree manually and even then I'm not sure if that's even possible given the predicate syntax. IOW, you are explicitly tied to a very specific schema that you imported with LINQ to SQL. Ouch!

This is problematic in any scenarios where you need generic data access such as in any sort of framework code where you may not know the explicit type or member that you are using as part of a query or a query result. But it can also be a problem if you need to access the SQL backend with syntax that LINQ to SQL simply can do (although thankfully LINQ to SQL is fairly good about the breadth of functionality).

Coupled with the issue of detached entities that can't be re-attached to the data context you can't even easily run your own query with ADO.NET and fill up an entity with data externally and then attach it to a DataContext so when dynamic queries are required you're pretty much blown completely out of LINQ to SQL.

What's insidious about this is that it's not real obvious at the get-go. You maybe running fine until you start abstracting common behavior that needs to be more generic or until you hit a query that LINQ to SQL simply doesn't support. At that point your options with LINQ become very limited.

Has anybody given some thought to this issue?