Rick Strahl's Weblog  

Wind, waves, code and everything in between...
.NET • C# • Markdown • WPF • All Things Web
Contact   •   Articles   •   Products   •   Support   •   Advertise
Sponsored by:
Markdown Monster - The Markdown Editor for Windows

LINQ to SQL Queries out of a Business Layer


:P
On this page:

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.

Posted in ADO.NET  LINQ  

The Voices of Reason


 

Joe Maier
September 26, 2007

# re: LINQ to SQL Queries out of a Business Layer

Hi Rick,
thanks for sharing your LINQ exploration results.
I personally don't have the time at the moment to dig into LINQ, although i would love to.
Therefore i observe your blog with great interest. Your post touches a point i didn't find (maybe i missed it?) in other LINQ blog posts (like ScottGu's) until now: How to encapsulate LINQ-queries in a business layer and what to return from there. Good point - Keep up your great work!

Thanks again,
Joe

Nagarajan
September 26, 2007

# re: LINQ to SQL Queries out of a Business Layer

Can you Give us your sample application?

-Nagarajan.

Scott Allen
September 26, 2007

# re: LINQ to SQL Queries out of a Business Layer

I went into exploring LINQ with a lot of skepticism, but it is exactly the features you are pointing out here that are winning me over. I'm digging it.

Steve from Pleasant Hill
September 26, 2007

# re: LINQ to SQL Queries out of a Business Layer

What I don't like is that this introduces yet another syntax/debugging effort to the SQL mix.

Luke Breuer
September 26, 2007

# re: LINQ to SQL Queries out of a Business Layer

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

needs to be changed to
if (userPk > 0)
    q = q.Where(e => e.UserPk == userPk);

Rick Strahl
September 26, 2007

# re: LINQ to SQL Queries out of a Business Layer

@Luke - thanks for catching that. I actually fixed this - ahem in the code, but forgot to update here. Fixed and updated above.

@Scott - same here. I'm finding this sort of thing makes the front end code much easier and more declarative to deal with because you can actually state your extent explicitly. So in the query that binds I state explicitly which fields I'm binding which both makes the code more readable as well as making for an efficient SQL call that only retrieves data that is needed and no more. I'm actually pretty happy with the way LINQ to SQL is working out with the help of the bus layer for this (admittedly simple) sample app I'm building. It's encouraging.

@Steve - There's something to what you say about additional SQL Syntax, but I think it well makes up for the fact that you can throw out a huge amount of SQL code you may have been writing before for CRUD altogether. Ok, if you're using any kind of framework that's probably not true <s>, but still... I think it's a valid point though once you start getting into really complex queries which are often more difficult to express in LINQ to SQL than in plain Sql.

@Nagarajan - I'll post code for all of this at some point, but not quite ready yet.

Roger Jennings
September 26, 2007

# re: LINQ to SQL Queries out of a Business Layer


Ryan Haney
September 27, 2007

# re: LINQ to SQL Queries out of a Business Layer

How would you implement LINQ in Web Services? The current architecture I am dealing with is web services happy - everything goes through web services, which IMHO, are being overused. Would you pass serialized objects back and forth and have some sort of translation / state tracking mechanism? Would it make sense to serialize expression trees and send those across the wire? I love linq, and would like to see best practices for communication between web applications and web services.

Rick Strahl
September 27, 2007

# re: LINQ to SQL Queries out of a Business Layer

@Ryan - I think it depends which school of thought you subscribe to. The SOA purists will tell you should create explicit message objects for sending messages on the wire. But LINQ to SQL's entities support Web Service attributes and will serialize down - with some serious cautions however.

The problem with the stock entities is that you can very easily end up with circular references which won't serialize. Invoice -> LineItem -> Invoice will result in a serialization error for example, unless the relationship is broken explicitly by either applying private visibility or explicitly setting a related value to null before serializing.

Personally I think that LINQ entities would be fine for serialization and there should be no state associated with them. Pass the entities make changes and then let the framework handle the synchronization by reattaching. This is currently confusing but fairly easy to implement with some small wrapping code.

Scott Allen
September 27, 2007

# re: LINQ to SQL Queries out of a Business Layer

One question:

How are you handling the disposal / closing of the DataContext in these scenarios where you pass a queryable object all the way back? Have you seen anything leak?

kris
October 16, 2007

# re: LINQ to SQL Queries out of a Business Layer

I'm in a situation pretty much like you have laid out here. I have a webservice Middle Tier and I have a getProjects() in my BLL. The query I have to use a var because I'm getting a lot of tables back with weak relationships.

I need to pass what is returned to another method which will convert what is returned into my DTO.

I've been trying for a couple of days to figure out how to do this. I have discovered i can set my method to take an IQueryable parameter. You mention here you can still run the query, what is the syntax for doing so.

My method looks like:

internal static string ConvertToDTOs(IQueryable objs)
{

}

Thanks

Mike
December 08, 2007

# re: LINQ to SQL Queries out of a Business Layer

It's been a few months since you posted this, but I have just read this for the first time. I wonder if you have found any problems with this approach or if you still recommend it for simple applications?

Rick Strahl
December 09, 2007

# re: LINQ to SQL Queries out of a Business Layer

I'll be posting an update to this shortly for the RTM version and I'll re-post some of the findings and some more comments.

Honestly, I'm torn between what functionality LINQ to SQL provides and some of the limitations it imposes vis a vis Entity management across DataContexts (which for all intent and purpose is practically not supported).

However, in using L2S in a couple of small and medium sized apps the reality is that I never ran into these problem issues to begin with except in some of the serialization scenarios (for Web Services) which can be worked around.

In my (still limited) experience there's a fair chunk of code reduction and a big improvement in code readability and maintainability as you can pass around LINQ queries/results to provide lots of flexibilty for many query scenarios. From a coding perspective I find LINQ to SQL extremely enjoyable and useful.

But I also think in heavily segmented multi-tier apps - LINQ to SQL is going to suck because of the connected DataContext requirements, so it really depends on what you're doing and that frankly is not a sign of a good technology. If you use a data access technology you'll want to work anywhere and not just in specific scenarios.

In the end the thing that is frustrating with LINQ to SQL more than anything is that it's simply not a solution that is universal. If you use standard ADO.NET you probably could build apps that could do anything, any way you wanted. LINQ to SQL imposes limitations in some scenarios that can't be worked around...

Gaurang
May 10, 2008

# re: LINQ to SQL Queries out of a Business Layer

Can any one help me understanding that where Converter.ToDataReader is comming from. I could not find it.

Thanks.

Rick Strahl
May 11, 2008

# re: LINQ to SQL Queries out of a Business Layer

The converter is on the business object. Customer.Converter.ToDataReader(query); to turn a query into data reader (or data table, array, list etc.).

Garry McGlennon
June 12, 2008

# re: LINQ to SQL Queries out of a Business Layer

Rick

I would have to disagree with the approach. Now I'll admitt from the outset that my preference is for a SO approach, which may not be suitable in all situations. However, passing back the IQueryable reference to me defeats the purpose of any abstractions. If your client is making database calls, dont we call that a 2-tier application? Which I thought we mostly all agree is not a great way to build our applications. Now I guess one could argue with the syntax sugar and the meta data binding the query, that its not the same. Though I would say if your CLIENT binds directly to a Linq expression that is translated directly to SQL and executed (as Linq to SQL does) then your buildint a 2-tier app. It should be clearly pointed out that (kinda hinted in a previous comment) this technique is completely unusable in webservices or any type of service oriented application. Its bad enough I see services written with arguments as pure text, which is supposed to be a serialized entity, I really hope I never see a method with an expression tree as its arguement (I'm not even sure you can serialize it btw).

On another issue, the circular reference issue has been fixed in .NET 3.5 SP1, they added a new attribute that will correctly serialize multiple instances of the same object. For those interested here's the attribute - which has been added to Linq to SQL btw.

DataContractAttribute(IsReference=true)

So while I don't agree with the technique, if you're building a simple app (ie non-enterprise) then its probably Ok, but I'd still think twice.

Rick Strahl
June 12, 2008

# re: LINQ to SQL Queries out of a Business Layer

Garry - so what are you suggesting exactly? Pass out POCO objects only (IOW, having already called ToList() or whatever invokation method that will fully materialize an object graph)? In that case you're pretty much diminishing any benefits that LINQ to SQL actually provides in terms of performance, lazy loading plus you take a potential databinding hit.

Also I'm not sure what you mean about Web Services. Any Web Services methods can simply return the fully materialized versions (ie. you return .ToList() or ToArray() or whatever concrete implementation). You lose nothing in that.

IAC, the business layer doesn't require or even make you return IQueryable anywhere - that's a choice that I made for my actual business objects. But if you want to return concrete instances of types there's nothing that will stop you from doing that either. That's a choice the developer can make.

To me there's not a downside. Certainly if I create a Web Service or other distributed interface I'm not going to talk to these objects directly but have a front end service layer in front of it that can decide to do the right thing for serialization or whatever anyway. Passing IQueryable back allows you some flexibility that you otherwise have to implement with lots of overloads or worse client filtered data...

Peter
June 18, 2008

# re: LINQ to SQL Queries out of a Business Layer

What was needed to get global access to the Context.

In the code

from e in this.Context.EntryEntities

I can't get it to display the classes within

DavidZexyz
September 11, 2008

# re: LINQ to SQL Queries out of a Business Layer

Rick, can you show a sample of databinding to an anonymous type?

Amit
November 07, 2008

# re: LINQ to SQL Queries out of a Business Layer

Please tell me how to use LINQ in Web Services?


Thanks
Amit

Alex
February 15, 2009

# re: LINQ to SQL Queries out of a Business Layer

Hi Rick,

thanks a lot for your contribution concerning Dto's an Bo's.

I'm trying to get the right way disposing the Linq - datacontext stuff implementing a "using..." statement. But the the consumer (here: Windowsforms DataGridView / BindingSource) says "Released object reference of datacontext is not accessible" when I'm trying to get a IQueryable "List" of type DtoCustomers. It works fine when I'm fetching a single DtoCustomer.

What's wrong ?

Sincerly,
Alex

---- windows forms consumes the data access Method directly ----

private void Form1_Load(object sender, EventArgs e)
{
this.customerBindingSource.DataSource = daCustomerManager.GetCustomer();
}


---- separate Data Access Class ---

/// <summary>
/// Get List of Customer Dto
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public IQueryable<DtoCustomer> GetCustomer()
{
using (CustomerDataContext dc = new CustomerDataContext())
{
return (from p in dc.Customer
orderby p.Surname,p.Name
select new DtoCustomer
{
Name = p.Name,
Surname = p.Surname,
EntryDate = p.EntryDate,
ExitDate = p.ExitDate,
Info = p.Info
}
).AsQueryable<DtoCustomer>();
}
}

Alex
February 15, 2009

# re: LINQ to SQL Queries out of a Business Layer

...this works fine:

/// <summary>
/// Get Customer Dto
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public DtoCustomer GetCustomer(int id)
{
using (CustomerDataContext dc = new CustomerDataContext())
{
return (from p in dc.Customer
where p.Id == id
select new DtoCustomer
{
Name = p.Name,
Surname = p.Surname,
EntryDate = p.EntryDate,
ExitDate = p.ExitDate,
Info = p.Info
}
).SingleOrDefault();
}
}

rs.emenu
September 02, 2010

# re: LINQ to SQL Queries out of a Business Layer

Here is a link that provides good information about LINQ to SQL best practices

http://www.a2zmenu.com/LINQ/LINQ%20to%20SQL%20Best%20Practice.aspx

West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2024