SharePoint Lookup (Count Related) value in Linq Query

Question

I have two lists, Posts and Comments. Comments has a Lookup column to the Posts list, and the Posts has a Lookup (Count Relate) relationship back to the Comments list. What I'm trying to do is just display the number of Comments in each Post. For some reason I can't get how to do this with the Entity References.

I have an ArchiveItem class:

    public class ArchiveItem
    {
        public string Id { get; set; }
        public string Title { get; set; }
        public string Comments { get; set; }
        public string Date { get; set; }
    }

And then the query that I'm trying to run:

        var queryItems = from item in spotlightItems
                         join comment in commentItems on item.Title equals comment.Title
                         select new ArchiveItem
                         {
                             Id = item.Id.ToString(),
                             Title = item.Title,
                             Comments = comment.Post.Title.Count().ToString(),
                             Date = item.Date.ToString()
                         };

I've tried a few different ways and get a variety of error messages. This particular version gives me

The query uses unsupported elements, such as references to more than one list, or the projection of a complete entity by using EntityRef/EntitySet.

Any ideas? I thought this would be pretty simple, but maybe I'm missing something.

Answer 1

Linq-to-Sharepoint does not support joins. A sharepoint list is not a separate table in the actual database, the actual data model of sharepoint is not the point, but you should keep in mind that what's a logical and cheap operation in plain SQL is not that easy per se in CAML, and every Linq-to-Sharepoint query is ultimately converted to CAML.

Anyhow, joins aren't implemented. You can use the lookup columns' Entity to get the data, but in the background this is always implemented as a different query, and in my experience you cannot use any aggregate or other multi-record operations on these lookup'd-entities, including Count().

There is probably a good way around this, because count is such an easy function. I would try converting the property you want to count to an array (or similar), and using the length or count of that.

In general, the way around these issues is to do the data processing in your code and rely on fairly crude queries. And by investing some care in choosing the correct data structures you can speed up the operations really well. On several occasions I experienced better performance with code-processing then with linq-to-sharepoint query solutions, even though the queries in the first case produced a certain amount of unnecessary data traffic to the database.

One more thing: If you plan to eventually generate your Sharepoint Lists using CAML or code, and you are using 'clicked' content-types/lists only during development, bear in mind that there are differences in classes SPMetal generates in these cases. More specifically, lookup fields are represented not as Entity classes, but as two normal fields, on with the item Id and one with the title (more like in SPListItem). Also, the reverse lookup-entitysets are not present at all. I've not seen documentation about this, but I have experienced it. Consequently, you may need to rethink some of you queries if you plan to use a CAML generated site. There may be a workaround, but in my experience Lookup Entity(sets) are very slow anyway, and it's better to use a normal linq query.

I hope this helps.