Introducing LINQ to LightSpeed

As Andrew mentions in his overview, LightSpeed 2 incorporates LINQ (Language Integrated Query) support. If you’re using C# 3.0 or Visual Basic 9, and targeting .NET 3.5 or above, LINQ brings the concept of querying directly into the language core, so instead of writing a SQL statement and embedding it as quoted string, you can write the query using C# or Visual Basic syntax. Among the benefits of LINQ are compile-time type checking, saving you learning (and writing) the details of the SQL syntax and the fact that the LINQ implementation can optimise the way it handles your query, for example optimising the SQL or parallelising an in-memory query.

Of course, LightSpeed 1 already provided these benefits to a great extent. The LightSpeed query API saved you from having to put together SQL strings, set up SQL parameters, and so on. LightSpeed queries returned entity objects instead of data readers, so you had typed objects from the start. And the LightSpeed engine created optimised, database-specific SQL from your abstract query description.

LINQ to LightSpeed means that if you know LINQ, you can get these benefits without having to learn a new API. You can continue to write LINQ queries as if against LINQ to SQL or another LINQ provider, but you’ll get database independence (effectively LightSpeed gives you LINQ to Oracle, LINQ to MySQL, LINQ to PostgreSQL and LINQ to SQLite as well as LINQ to SQL Server, all in the one box), advanced eager loading functionality (avoiding the so-called “n+1″ problem which affects some LINQ implementations) and of course the blazingly fast LightSpeed query engine.

In order to use LINQ to LightSpeed, you’ll need to add a reference to Mindscape.LightSpeed.Linq.dll to your project. This defines the extension method Query on IUnitOfWork. So now (with the appropriate using statement), you can write:

var query = unitOfWork.Query<Member>();
 
var everyoneExceptJB = from m in query
                       where m.UserName != "jb"
                       select m;

We don’t really want to be writing unitOfWork.Query() all the time, so it’s convenient to create a strong-typed unit of work, which exposes properties corresponding to the various entity types. (If you’re familiar with LINQ to SQL, this is a bit like the DataContext generated by the LINQ to SQL designer.) To do this, derive from the LightSpeed UnitOfWork class (which implements the IUnitOfWork interface). Here’s an example:

using Mindscape.LightSpeed.Linq;  // to bring extension method into scope
 
public class VideoSharingUnitOfWork : UnitOfWork
{
  public IQueryable<Member> Members
  {
    get { return this.Query<Member>(); }
  }
}

A bonus of this is that we no longer need to have a using clause for the extension method in our querying code — only the convenience class needs to see the extension method. By the way, if you use the Visual Studio LightSpeed designer to create your models, it automatically creates this class for you. (The designer will also add the LINQ DLL reference if required.)

Also, because VideoSharingUnitOfWork is an IUnitOfWork, we can still call the usual methods on it for updates: e.g. Add(entity), Remove(entity), SaveChanges, etc. These don’t change just because we’re using LINQ to write queries.

If we create this convenience unit of work class, we need a way get an instance of it. LightSpeedContext.CreateUnitOfWork() returns a “normal” IUnitOfWork, not our convenience class. What we need to do instead is call ==LightSpeedContext<VideoSharingUnitOfWork>.CreateUnitOfWork()==. ==LightSpeedContext<TUnitOfWork>== is exactly like LightSpeedContext except that CreateUnitOfWork() returns the convenience class rather than the base IUnitOfWork interface. (The scope helper classes in LightSpeed 2 also support convenience unit of work classes.)

So with the plumbing out of the way, we can write our LINQ query as follows:

var everyoneExceptJB = from m in unitOfWork.Members
                       where m.UserName != "jb"
                       select m;

And of course now we have a queryable Members object we can go on to write more complex queries:

var matureAdults = from m in unitOfWork.Members
                   where m.Age > 21 && m.UserName != "ludwigthemad"
                   orderby m.FeedbackRating descending
                   select m;

And projections:

var exMembers = from m in unitOfWork.Members
                where m.Expired
                select m.UserName;
 
var memberSummary = from m in unitOfWork.Members
                    orderby m.UserName
                    select new { m.UserName, m.MemberSince, m.LastLogin };

Most of the time, you’ll be iterating over the results of a query:

foreach (var adult in matureAdults)
{
  Console.WriteLine(adult.Name + " is no longer one of the cool kids");
}

In this case, the LINQ query behaves just like a traditional LightSpeed query. You get back some entities, and you work with those.

But iteration isn’t the only thing you can do with LINQ queries.

int adultCount = matureAdults.Count();

When you execute the line above, LightSpeed issues a Count query, not a Find query. As with a native LightSpeed Count query, this becomes a SQL COUNT and is executed on the database. Count isn’t the only operation for which this happens:

int oldest = unitOfWork.Members.Max(m => m.Age);
int youngest = unitOfWork.Members.Min(m => m.Age);
int ifYouLaidThemAllEndToEnd = unitOfWork.Members.Sum(m => m.Height);
bool gotYoungsters = unitOfWork.Members.Any(m => m.Age < 16);

And if you haven’t used LINQ before you may be surprised to find that you can even use some .NET methods and operators, and LightSpeed will execute them on the database:

var allTheJs = from m in unitOfWork.Members
               where m.UserName.StartsWith("j")
               select m;
 
var revenue = from o in unitOfWork.Orders
              where o.BookedDate >= periodStart && o.BookedDate <= periodEnd
              select o.UnitPrice * o.Quantity * (100.0 - o.Discount) / 100.0;

We won’t go into LINQ examples in any more detail, as there are plenty of resources out there for learning the LINQ syntax, and for the most part they apply without any changes to LINQ to LightSpeed. So all you need to remember that’s specific to LightSpeed is:

  • Reference the LINQ DLL.
  • Optionally, create a strong-typed unit of work (convenience class), or use the designer which will create one for you.
  • If you’re using a strong-typed unit of work, use ==LightSpeedContext<TUnitOfWork>== instead of the base LightSpeedContext.

Want to give it a try? Get LightSpeedExpress (it’s free, even for commercial use) and give it a go!

kick it on DotNetKicks.com

Tagged as LightSpeed

8 Responses to “Introducing LINQ to LightSpeed”

  • That is Great, I was wondering how does it work on data paging, I mean does it really support skip/take for all the supported databases??

  • Kazi, yes it does.

    Cheers,

    Andrew.

  • [...] Introducing LINQ to Lightspeed (Ivan Towlson) [...]

  • Has there been any consideration to support Astoria with Lightspeed?

  • Scott, I assume you’re talking about using LightSpeed to run queries against an Astoria service? (As opposed to using LightSpeed to create an Astoria-like service layer.) If so, it’s not something we are actively working on at the moment, but it’s certainly an area we’re discussing amongst ourselves. Do you have a particular use-case / architecture in mind?

  • [...] your project targets .NET 3.5 or above, the generated code includes a strong-typed unit of work class so that you can use LINQ. The designer also adds references to the LightSpeed (and, if [...]

  • Hi Ivan,

    Does LightSpeed suports Sybase Anywhere ? I know the actual version 2.2 does not includes that server in the database list, but have you included Sybase Anywhere support in your next release?

    Thanks in advance!

  • Hi Juan,

    Although we don’t have SQL Anywhere support planned in, we’d be very happy to look at it if you have a need for it. Can’t promise anything, but if you have a project which would make a suitable beta test for a LightSpeed implementation then drop us a line via the contact form and we’ll take a look.

  • Leave a Reply

Archives

Join our mailer

You should join our newsletter! Sent monthly:

Back to Top