I used it to build a LINQ provider for a legacy mainframe database system, abstracting away the incomprehensible table and column naming, weird date systems, EBCDIC, and other nastiness involved in querying that system using straight-up SQL.

One thing I found is that implementing a custom LINQ provider gives you a better understanding of how Entity Framework runs your queries. You get to see how queries get split up into the parts executed in the CLR versus the parts that get translated, and how expressions get rearranged and rewritten.