How not to join tables in LightSpeed

We’ve had a couple of customers recently asking about writing joins using LightSpeed query expressions.

Not so fast!

A lot of the time when the SQL part of your brain is thinking “I need to write a join here,” what you’re actually doing is traversing an association. And in a lot of cases, LightSpeed can take care of that for you.

Suppose that you want to filter on a property of an associated entity. For example, you want to get all the Comments where the Contribution on which the comment occurs was posted by a particular User. In pseudo-SQL, that would be something like SELECT * FROM Comment WHERE Comment.Contribution.UserId = n. Of course that isn’t legal SQL, and if you wanted to write the query in real SQL you’d use a join to bring the Contribution table into the query.

But in LightSpeed you don’t have to do that. You can use a dotted path just like a C# or VB property access:

var comments = uow.Find<Comment>(Entity.Attribute("Contribution.UserId") == userId);

It even works for collections too. When you dotted-path through a collection, LightSpeed rewrites this as a query that will pass if any member of the collection matches. Here’s a query that gets all Users who have posted a Contribution with a rating of 4 or higher:

var users = uow.Find<User>(Entity.Attribute("Contributions.Rating") >= 4);

Of course there are some cases where you need to reach for an explicit join, but if all you want is to query on a property of an associated entity, don’t touch that dial — just use LightSpeed association syntax and let us take the strain.

Tagged as LightSpeed

11 Responses to “How not to join tables in LightSpeed”

  • Dear Mindscape: Could you please produce a LightSpeed music video with Ivan rapping and wearing hammer pants. Thanks.

  • But no dancing, right?

  • *shiver* magic strings… This is one of those scenarios where I prefer NHibernate.

  • I don’t think they’re particularly magical — just as in a query on a non-associated property, you write the C# expression for accessing that property, except now the expression happens to include dots because you’re traversing another object.

    Okay, the collection string is *slightly* magical, in that SomeCollection.SomePropertyOfTheIndividualEntitiesInThatCollection isn’t legal in C#. But it allows you to get the job done in a way which is easy to read and convenient to write. I think the small amount of magic involved is an acceptable tradeoff.

    Or were you meaning that you dislike the general concept of having the ORM generate SQL in a way that doesn’t map one-to-one onto the query as written, and you prefer to direct it explicitly so you know exactly what is going on? That’s a fair attitude: there is a tradeoff between explicitness and convenience and different people value them in different measures!

  • I just mean that it’s not strongly typed. If the property name changed on contributions then you’re not going to pick up the error that your query is broken.

    NHibernate would achieve the same thing doing something like:

    Contribution contribution = null;
    var result = session.QueryOver()
    .JoinAlias(x => x.Contributions, () => contribution, JoinType.InnerJoin)
    .Where(() => contribution.Rating > 4)
    .List();

  • Oh, fair enough. That’s because query objects were designed to work with .NET 2.0 and 3.0, before expression trees arrived. For strong typing you would usually just use LINQ.

    Also, even for query objects, you can use the generated field name constants to get errors if the names change e.g. Entity.Attribute(Comment.SubjectField) == “frist!!!”.

    Maybe one of these days I’ll also write a little helper library that allows you to write query expressions using strong-typed lambdas as per your NH example. That could make a nice little demo of working with expression trees. But since people using .NET 3.5 or above would typically turn first to LINQ, I’m not sure how useful it would be in practice.

  • I personally don’t like LINQ, it gets too complicated too easily. Lambda’s are much easier to read.

    So is it possible with LS to eager load without the string value? Every time I see examples it’s always with strings.

  • Heh, maybe I’ll make that helper library after all then!

    Named aggregates names are always strings. However if you use the designer it does generate constants for all the aggregate names in your project: if you use those constants, you will get a compiler error if one of the names changes. What this won’t catch is if there are multiple associations in the same aggregate and you forget to change the name on one of them.

  • Ivan: There will be dancing – http://www.youtube.com/watch?v=otCpCn0l4Wo#t=55s

  • I dislike the Named aggregates if you have got some more of them, it gets easly confusing and unmaintainable.

    Teleriks OpenAccess has a nice feature called “FetchPlans” especially the “fetchStrategy” looks good (strongly typed).

    http://www.telerik.com/help/openaccess-orm/dev-guide-crud-fetch-plans-per-query.html

  • I wonder if OpenAccess is slower then Entity Framework. I would assume it is based on teleriks control libraries.

  • Leave a Reply

Archives

Join our mailer

You should join our newsletter! Sent monthly:

Back to Top