Query Providers ARE Still Data Access Code!!
So everyone reading this (or at least hopefully everyone) will know that putting data access logic into the UI is anti-pattern of all anti-patterns. I am going to sidestep the issue of simple utilities and one-off screens and trivial applications for the purposes of this discussion. We're instead focusing on real applications with non trivial functionality. How many times have you taken over some application and found a button click handler that creates a ADO connection object right there? The immediate groans that I hear when you read that last sentence will be confirmation enough that we've all been there before.
So let me now get to the moral of the story: If one would never dream of writing ADO.Net code in a click handler how come I continually find code that does exactly that but against EF or Linq2Sql query providers?
In fact, I will extend this conversation to include direct concrete references to ORM infrastructure as well. You should always remember that a Query Provider, while handling most of the grunt work of query processing and marshaling of data, it is still data access code. PERIOD. Do not pass go, do not collect $100.
So how do we deal with this then?
Simple: The Command-Query Separation Principal. And specifically, since we're focusing on accessing data, the Query part of the equation.
By introducing your queries as first class citizens, one can model them explicitly. Since all of LINQ is based around Query Providers and Expression Trees the obvious path is to leverage them to express your intention in your UI. The core concept of the query can be modeled via an interface or an abstract type with required parameters or inputs and options introduced to the API. From there you can supply the IQueryable<YourType> to your application. If you walk away from this remembering only one thing it should be this: Intention is not Implementation! IQueryable and Expression types are all about modeling Intention. A Query Provider is all about Implementation.
No leaky abstractions of query providers in your UI, the pattern is amenable to mocks and stubs, and your DAL is swappable as needed. In addition, this allows you a natural way to follow another important concept, the Open-Closed Principal. Now your base queries are able to extended in all sorts of interesting ways (sorting, ordering, paging, additional predicates, etc) but cannot be altered from their fundamental logic (closed to modification). All this accomplished without causing you headaches as well as keeping your codebase lean and mean.
I've whipped up a simple example website illustrating the concept in use. Mind you, it's not the example you should use for the proper way to build scalable websites nor does it show all the best practices around state management but you should focus on the ability to keep your DAL implementation from being known to the UI layer.