As I'm working through a sample application here with LINQ to SQL and a business layer that I've built I'm starting to find out some really cool things that you can do with LINQ to SQL that were not easily done before.

So I use a business object abstraction layer that hosts the data context. Typically there's one business object per main entity but the idea is that the business objects are logicial abstractions rather than the physical abstractions that LINQ to SQL Entities are.

So,  I have a business object and one of the things the BO does is hold all data related queries. The front end by and large doesn't access the the data (the data context in this case) and any query (and CRUD) logic is managed by the business object which in turn uses LINQ to SQL as a DAL.

Most of the query methods are really little more than method wrappers around LINQ queries, but of course there could be much more going on in these methods. So I might have a simple method like this to retrieve some open time entries:

/// <summary>

/// Get open entries for a given user

/// </summary>

/// <param name="userPk"></param>

/// <returns></returns>

public IQueryable<EntryEntity> GetOpenEntries(int userPk)

{

    IQueryable<EntryEntity> q = 

        from e in this.Context.EntryEntities

        where !e.PunchedOut

        orderby e.TimeIn

        select e;

    // *** Add filter for User Pk - otherwise all open entries are returned

    if (userPk > 0)

        q = q.Where(e => e.UserPk == userPk);

    return q;

}

/// <summary>

/// Get all open entries

/// </summary>

/// <returns></returns>

public IQueryable<EntryEntity> GetOpenEntries()

{

    return this.GetOpenEntries(-1);

}

I can then use this method in my code by instantiating the business object and invoking the method like this:

protected override void OnInit(EventArgs e)

{

    base.OnInit(e);

    entry = TimeTrakkerFactory.GetEntry();

    // *** Set up the base query

    IQueryable<EntryEntity> entries = this.entry.GetOpenEntries(this.TimeTrakkerMaster.UserPk);

    // *** 1st hit against the database 

    int count = entries.Count();

    if (count == 1)

    {                

        // *** Second hit against the database - single scalar result

        int? Pk = entries.Select(en => en.Pk).FirstOrDefault();

        if (Pk == null)

            Response.Redirect("~/Default.aspx");

        Response.Redirect("~/punchout.aspx?id=" + Pk.ToString());

    }

    // *** Assign the data source - note we can filter the data here!

    // *** Second hit against the database - query winnied down to just return fields specified

    this.lstEntries.DataSource = this.entry.Converter.ToDataReader(

                                    entries.Select(en => new { en.Pk, en.Title, en.TimeIn }));

    this.lstEntries.DataBind();

}

Now what's cool about this is that I have a single business object method here and it returns a single relevant result query to the front end UI code. But because the result is a query and not actually a concrete data object I have A LOT of choices what I can do with the query at this point.

So this form is an OpenEntries form that shows all open entries for a given user. It checks if there's only one entry and if so opens that one entry. Otherwise it goes ahead and binds a ListView with the open entries to display and lets you pick one from the list.

Notice in the code above though that all of this is done with a single business object method that returns a query. The front end code can now use the query and further filter it down as nessary. So the check for Count() is a separate query that actually hits the database (the 1st database hit for this query). If there's only one entry, another query is run that retrieves just the PK:

        int? Pk = entries.Select(en => en.Pk).FirstOrDefault();

which runs a very simple select top1 Pk from Entries query.

If there are multiple entries I just want to display them all and I can now use the query for databinding. I could directly assign the query to the DataSource for the ListView and that would work fine although it would result in an object entity list to be databound which is not terribly efficient.

So rather than binding the Entity List, I can use the business object to convert the result into various other types (like DataTable, DataReader, Array, List) to provide the most efficient mechanism possible for databinding. In this case binding a simple full list a DataReader() is the best choice and the BO's Converter object handles this conversion easily.

The converter also serves another important purpose: It provides a error handling for the actual query execution so if an error occurs the error is handled inside the business object. This is useful for logging and potential recovery operations. Without this data access errors would fire on the Page which is not always where you want to handle your errors especially data related errors.

But notice how much flexibility you gain with the result query because of the fact that the query is just an 'expression' that can be further manipulated. In the past deciding EXACTLY what to return from a business object was a difficult task. Do you return a DataReader, a DataTable or Entities or what? In many cases I had overloads that would deal with this.

With LINQ the CLIENT can decide exactly what he wants out of the base query and can winnie down the data to EXACTLY the fields that are required for databinding and the type of binding object that is produced from the query be it a DataReader, an Entity List or a DataTable.

Return Qualified Types!

One of the key things to remember though for business objects that return queries is to make sure that queries return a strong type rather than a generic var/object. All of the above works the way it does because the result is IQueryable<EntryEntity>. Remember that when you use projection in the Select clause of a LINQ to SQL query you loose the ability to create a strong type - IOW the type becomes anonymous and the generate item type will not be visible to any calling clients. THe business object method has to return type object in that case:

public IQueryable GetOpenEntriess(int userPk)

{

    var q =

        from e in this.Context.EntryEntities

        where !e.PunchedOut

        orderby e.TimeIn

        select new { e.Pk, e.Title, e.TimeIn, e.TimeOut, e.Description, e.UserPk };

    // *** Add filter for User Pk - otherwise all open entries are returned

    if (userPk > 0)

        q = q.Where(e => e.UserPk == userPk);

    return q;

}

Here the code produces an anonymous type and the only way that you can pass this query out of this business object method is to return it as type object (or IQueryable), which in turn means all the strong typing in front end code that I showed above is lost.

However, the object returned is actually an IQueryable and so you can still execute the query. You can use it for databinding, you can use it for conversion to a DataReader etc. This works because the query definition holds the SQL statement that can be executed and when enumerated the execution still works as you would expect. An entity list is still created when you enumerate, except there's no type information - except via Reflection - available on the individual entities.

In some situations it may make perfect sense to create anonymous types with projections. If you know for a fact what fields you need always and you don't expect the front end code to muck with the data but rather use it verbatim, then returning the above is perfect. In fact it's a good thing because it will prevent the front end code from mucking with the data.

However, if you do want the front end to filter the data or you want to control the exact field list etc. do try to pass a specific entity IQueryable back - as it gives the client additional control over the data.

This is pretty damn cool if you think about it - it gives the application a lot of choices as to how to return data and lets the client also retain some control over the formatting details. Lot of flexibility there. Even in this simple sample I'm building this is proving to be extremely useful and saving a lot of code in various places. Not only that it also produces very readable code in the front where intent is clearly visible even with several display options.