Currently I'm implementing a simple Linq enabled ORM to handle an edge case in our system (For everything else we use NHibernate). I've found that the hard part about writing a Linq provider is parsing expression trees; the provider is actually the easy part. In the course of writing it I've created a simple base class that makes it even easier to create the provider:

public abstract class QueryableBase<T> : IOrderedQueryable<T>, IQueryProvider
{
    private readonly Func<QueryableBase<T>> _createQuery;

    protected QueryableBase(Func<QueryableBase<T>> createQuery)
    {
        _createQuery = createQuery;
        Expression = Expression.Constant(this);
    }

    public abstract object Query(Expression expression);

    // ---------------------- IOrderedQueryable Implementation -----------------

    public Expression Expression { get; set; }
    public Type ElementType { get { return typeof(T); } }
    public IQueryProvider Provider { get { return this; } }

    public IEnumerator<T> GetEnumerator()
    {
        return (IEnumerator<T>)((IEnumerable)this).GetEnumerator();
    }

    IEnumerator IEnumerable.GetEnumerator()
    {
        var result = (IEnumerable)Provider.Execute(Expression);
        return result == null ? Enumerable.Empty<T>().GetEnumerator() : result.GetEnumerator();
    }

    // ---------------------- IQueryProvider Implementation -----------------

    public IQueryable<TElement> CreateQuery<TElement>(Expression expression)
    {
        return (IQueryable<TElement>)CreateQuery(expression);
    }

    public IQueryable CreateQuery(Expression expression)
    {
        var query = _createQuery();
        query.Expression = expression;
        return query;
    }

    public TResult Execute<TResult>(Expression expression)
    {
        var value = Execute(expression);
        return value == null ? default(TResult) : (TResult)value;
    }

    public object Execute(Expression expression)
    {
        return Query(expression);
    }
}

This base class provides everything you need, save the expression tree parsing, for a Linq provider. All you have to do is inherit from this class and implement the Query method as follows:

public class SqlTable : QueryableBase<DataRow>
{
    private SqlConnection _connection;
    private string _tableName;

    public SqlTable(SqlConnection connection, string tableName) : 
        base(() => new SqlTable(connection, tableName))
    { 
        _tableName = tableName;
        _connection = connection;
    }

    public override object Query(Expression expression)
    {
        // TODO: Parse the expression tree, generate TSQL, execute it and return the results.
    }
}

You'll notice that the QueryableBase constructor requires a lambda that creates a new instance of your provider (In this case SqlTable). This is used by the base class in the implementation of IQueryProvider to construct new queries based on this one but with a different expression (So that's why we are creating the new provider with the exact same parameters that were passed into the constructor of this one). Now all the magic happens in the Query method. Here is where you would parse the expression tree and translate it into something useful; in this case a TSQL statement that will be executed. Parsing expression trees is a subject all its own (And covered pretty well on the interwebs) so we'll gloss over that. Here is how we could use our new table query provider:

using (var connection = new SqlConnection("..."))
{
    connection.Open();

    var table = new SqlTable(connection, "Products");

    var cheapProducts = table.Where(x => (double)x["Price"] < 5.0).
                              OrderBy(x => x["Name"]).
                              Take(50).
                              Select(x => new { Name = (string)x["Name"], 
                                                Price = (double)x["Price"]}).
                              ToList();

    foreach (var product in cheapProducts) 
        Console.WriteLine("{0} = ${1:0.00}", product.Name, product.Price);
}