Home » Blog

Join queries in LightSpeed 2

Tagged as LightSpeed

Join support is one of the most frequently requested features for LightSpeed, and it’s coming in version 3, but what people don’t always realise is that you don’t always need a join for a join query. LightSpeed 3 join support will enable you to pull back data from across multiple tables; but if all you want to is to pull back entities from a single table using criteria from other tables, you can often do that today.

Let’s look at a simple example of a video sharing site, based on something that came up in the forums recently. In this model, the main entities are Contributions. Each Contribution is associated with a Member (who uploaded it). In addition, there is a many-to-many (through) association between Contributions and Tags, via a ContributionTags through entity.

Now suppose you want to find all the Contributions uploaded by a Member named “t-dogg” and tagged with a Tag named “sheep.” Initially it might seem like you need a join for this, or at least a sequence of nested selects, but in fact you can do it efficiently in a single LINQ query:

from ct in UnitOfWork.ContributionTags
where ct.Contribution.Contributor.Name == "t-dogg" && ct.Tag.Value == "sheep"
select ct.Contribution

The trick here is to work around LINQ’s collection issues by finding a start point where all the associations are many-to-one. In this case, ContributionTag has many-to-one associations with Tag and with Contribution, and Contribution has a many-to-one association with Member. So we can write the query in terms of ContributionTag objects without needing to do a join or an explicit subselect. Then we can project the results along the many-to-one association to Contribution to get the Contributions instead of the ContributionTags.

The core API allows another syntax for the same query which you may find more natural:

UnitOfWork.Find<Contribution>(
  Entity.Attribute("Contributor.Name") == "t-dogg"
    && Entity.Attribute("ContributionTags.Tag.Value") == "sheep");

Here it’s clearer from the Find call that the query is interested in Contributions, not in ContributionTags. Notice also that LightSpeed is able to traverse the ContributionTags collection association without you needing to write an explicit join or subselect.

What LightSpeed can do automatically doesn’t cover every possible join query, and of course if you want to return data from multiple tables you currently have to use a view or a stored procedure rather than a join. But for simple queries that return data from a single table but need to join to other tables only for the “where” criteria, there’s a good chance you can build a LightSpeed query to do the job.

Add your comments
Posted by Ivan Towlson on 8 June 2009

2 Responses to “Join queries in LightSpeed 2”

1. If you have 2 tables which always join into 1 entity is there a way to have lightspeed manage them as a single entity definition. We have many cases where multiple different tables use a supporting table for drawing.

   gianna giavelli Aug 17th, 2009

2. No, this isn’t easily possible. The mapping between tables and entity types is one-to-one (ignoring single table inheritance). If you only want to read from the two tables, you can map a view to the “combined entity,” but you won’t be able to save changes. If you need to perform full CRUD over the combined tables, you *could* do it using stored procedures, but this could be a bit laborious.

   However, don’t forget that LightSpeed supports very efficient eager loading of associations. So rather than trying to map the two tables into one entity, you could have two entity types, but use an eager load to ensure that the “drawing info” entity is always loaded along with the main entity and as part of the same query. Would that help?

   Ivan Towlson Aug 17th, 2009

Leave a Reply