Querying enhancements in LINQ to LightSpeed 3

The highest-profile querying enhancements in LightSpeed 3 are obviously join and group support, but there are also a lot of other improvements that give you a bit more flexibility in your queries. In this post I’m going to give a quick tour of some of these.

Querying on aggregate values

LightSpeed now supports using aggregate values such as Sum or Count as part of your query criteria. Here’s a simple example:

var prolificContributors = from m in UnitOfWork.Members
                           where m.Contributions.Count() > 8
                           select m;

You can also use selectors and functions within the aggregate:

var query = from m in UnitOfWork.Members
            where m.Contributions.Max(c => c.Title.Length) > 26
            select m;

This also enables you to use Any and All over associations in a where clause:

var terseMembers = from m in UnitOfWork.Members
                   where m.Comments.Any(ct => ct.Subject.Length <= 6)
                   select m;

Contains support

You can use the Contains operator to perform a SQL IN query:

int[] memberIds = new int[] { 41, 43 };
 
var query = from c in UnitOfWork.Comments
            where memberIds.Contains(c.MemberId)
            select c;

Subselects

But wait, there’s more: the Contains operator also allows you to perform an IN query over a subquery: that is, you can write where some_query.Contains(some_attribute). Here’s an example:

var query = from m in UnitOfWork.Members
            where (from c in UnitOfWork.Comments
                   where c.Subject.StartsWith("A")
                   select c.MemberId).Contains(m.Id)
            select m.UserName;

The subquery selects all member IDs from Comments whose subjects start with “A”. The outer query then selects the user names of all Members whose IDs fall into the subquery. The subquery is passed to the database as a subselect of the outer query so this still all happens in one database query.

Querying over child collections

Sometimes you want to select a subset of associated entities, rather than the whole collection that you’d get if you simply referenced the association property. In LightSpeed 2, you could do this by querying on the foreign key of the associated entities, but LightSpeed 3 offers a Query overload that’s a bit more concise and intention-revealing:

var contribution1 = UnitOfWork.FindById<Contribution>(1);
 
var commentsOnContribution1ByMember41 = 
  from ct in contribution1.Query(c => c.Comments)
  where ct.MemberId == 41
  select ct;

Note the use of

Query(c => c.Comments)

rather than the Comments collection itself. This produces a SQL query for only the selected Comment entities, rather than loading all the Comments on contribution1. This can be very handy if you’re dealing with very large child collections and don’t want to materialise large numbers of entities!

Smarter projections

We’ve extended the range of things you can use in projections (select clauses). In particular, aggregates over associations are available in projections and are translated to SQL and run on the server:

var query = from m in UnitOfWork.Members
            select new
            {
              m.Id, 
              CanBeTerse = m.Comments.Any(ct => ct.Subject.Length <= 6)
            };

Combining operators

LightSpeed now offers basic support for the Intersect, Union and Concat operators, though these are available only for entity selections (not projections) and do not support eager load.

Custom function mapping

Want to call a SQL function from your LINQ query, but we don’t provide a translation from a CLR method, or worse still there’s no corresponding CLR method at all? Now you can create a custom function mapping that allows you to keep writing strongly-typed LINQ queries and having the desired function appear in the SQL translation. Find out how here.

And many more

We’ve also implemented many more minor enhancements, such as ordering on properties of value objects, improved handling of literal values, etc. Download the free edition and try it out today!

Tagged as LightSpeed

Leave a Reply

Archives

Join our mailer

You should join our newsletter! Sent monthly:

Back to Top