complicated query with multi-lookups

Question

I am trying to get the items of one list where any of the multi-lookup values is contained in the result of another query.

The situation is this: I am building a webpart with which users can make reports ("melding") using a set of sharepoint (foundation 2010) lists for five property dropdowns. The properties are connected through lookups in the lists. Every time a dropdown selection is changed a postback occurs and the following dropdowns are re-populated.

The problem is that the connections between the properies aren't a straightforward match to the dropdowns. This is because the dropdown inputs are chosen for intuitive input of reports, and the datamodel is designed for easy and intuitive input of the system-data, not for efficient and easy programming...

Relevant part of the data model:
-Locaties (locations) is a list
-Contracten (contracts) is a list, each contract has one or more locaties (multi-lookup)
-Urgentie (urgency) is a list, each urgentie has one contract (lookup)
-Meldingtypes (reporttype) is a list
-Categorieën (categories) is a list, each categorie has one meldingtype (lookup), each categorie has one or more contracts (multi-lookup)

The report inputs are as follows:
User chooses Locatie first
User chooses Meldingtype second
User chooses Categorie third
User chooses Contract fourth
Finally user chooses Urgentie

Of course, each time the dropdown should only be populated with valid options. So when a location is chosen, the only meldingtypes shown in the dropdown should be the ones that have categories which have a contract which have the chosen location...yeah, I know ;)

I'm having trouble creating the queries to populate the dropdowns. I've been trying several methods, including multiple queries, constructions with the Contains selector, and none worked. In the last iteration I've created a query which uses the property an item has when it is referenced by a multilookup. Here the example for the meldingtype dropdown:

    private void fillMeldingtypeDropdown(Intermediair.IntermediairDataContext idctx)
    {
        var meldingtypeData = (from l in idctx.Locaties
                               from co in l.ContractenItem
                               from ca in co.CategorieënItem
                               where l.Id == selectedLocatieId
                               select ca.Meldingtype
                              ).Distinct<Intermediair.Item>();
        foreach (Intermediair.Item meldingtype in meldingtypeData)
        {
            ctrl_Meldingtype.Items.Add(new ListItem(meldingtype.Titel, meldingtype.Id.ToString()));
        }
    }

selectedLocatieId is a property which gets the value from the Locatie dropdown control.

The above code throws an System.InvalidOperationException. The error description is in Dutch, translated it would be something like: "The query uses unsupported elements, like references to multiple lists or projection of a full entity with EntityRef/EntitySet"

I get the same error if I use EntitySet.Contains:

    private void fillCategorieDropdown(Intermediair.IntermediairDataContext idctx)
    {
        var contractenData = from c in idctx.Contracten
                             where c.LocatieS.Contains( 
                                 (  from l in idctx.Locaties 
                                    where l.Id == selectedLocatieId 
                                    select l
                                 ).First<Intermediair.LocatiesItem>() 
                             )
                             select c;
        var categorieenData = ( from ca in idctx.Categorieën
                                from co in contractenData
                                where ca.Contract.Contains(co) && ca.Meldingtype.Id == selectedMeldingtypeId
                                select ca
                              ).Distinct<Intermediair.CategorieënItem>();
        foreach (var categorie in categorieenData)
        {
            ctrl_Categorie.Items.Add(new ListItem(categorie.Titel, categorie.Id.ToString()));
        }
    }

I have tried several permutations, but I can't seem to find the right one. I can't find any good examples for linq-to-sharepoint queries with multi-lookup fields, and I'm not yet fluent enough with linq, so I'm probably making some mayor mistakes. While I'm figuring this out I would really appreciate any helpful idea.

[Edit:another try failed] I tried to make separate queries of all steps to check intermediate results. I also tried out an Any-Contains combination to match up the multi-lookup contracts of categorie with the multiple contracts I got from the location.

        var locatieItem = (from l in idctx.Locaties
                           where l.Id == selectedLocatieId
                           select l
                          ).First();
        var contractenData = from c in locatieItem.ContractenItem
                             select c;
        var categorieenData = from c in idctx.Categorieën
                              where c.Contract.Any(co => contractenData.Contains(co))
                              select c;
        var meldingtypeData = (from c in categorieenData
                               select c.Meldingtype
                              ).Distinct();
        foreach (var meldingtype in meldingtypeData)
        {
            ctrl_Meldingtype.Items.Add(new ListItem(meldingtype.Titel, meldingtype.Id.ToString()));
        }

locatieItem and contractenData are filled as expected, but categorieenData generates the same error again. [/Edit]

P.S. because the Dutch names in the code are very readable in English, I've not translated the names. Sorry for the confusion.

Answer 1

I have solved the problem, sortof. It does give some overhead: too many records are being retrieved from the database. But only duplicates, so hopefully caching will solve most of that overhead.

The query I now use is deceivingly simple.

        var categorieenContractenData = from c in contractenData
                                        select c.CategorieënItem;

This doesn't give me a single set of CategorieItems, but a set of sets of CategorieItems, some of which can be duplicates. Then I loop through each entry with a double foreach loop, and put the items in three data structures for easy retrieval. This means that to fill each of the three dropdowns I only have to do one linq-query.

I expect some real-life data in the database by the end of the week, so then I'm going to test if it's fast enough. If not, I'm going to have to make an extra list (filled by eventreceivers).

Full code:

    private void ensureLoadData(Intermediair.IntermediairDataContext idctx)
    {
        if (dataLoaded) return;

        meldingtypes = new SortedList<string, int?>();
        categorieen = new SortedList<int?,SortedList<string,int?>>();
        contracten = new SortedList<int?, SortedList<int?, SortedList<string, int?>>>();
        IQueryable<Intermediair.ContractenItem> contractenData = from c in
                                                                     (from l in idctx.Locaties
                                                                      where l.Id == selectedLocatieId
                                                                      select l
                                                                     ).SingleOrDefault().ContractenItem
                                                                 select c;
        var categorieenContractenData = from c in contractenData
                                        select c.CategorieënItem;
        foreach (EntitySet<Intermediair.CategorieënItem> categorieenPerContract in categorieenContractenData)
        {
            foreach (Intermediair.CategorieënItem categorie in categorieenPerContract)
            {
                if (!meldingtypes.ContainsKey(categorie.Meldingtype.Titel))
                {
                    meldingtypes.Add(categorie.Meldingtype.Titel, categorie.Meldingtype.Id);
                    categorieen.Add(categorie.Meldingtype.Id, new SortedList<string,int?>());
                    contracten.Add(categorie.Meldingtype.Id, new SortedList<int?, SortedList<string, int?>>());
                }
                if (!categorieen[categorie.Meldingtype.Id].ContainsKey(categorie.Titel))
                {
                    categorieen[categorie.Meldingtype.Id].Add(categorie.Titel, categorie.Id);
                    contracten[categorie.Meldingtype.Id].Add(categorie.Id, new SortedList<string,int?>());
                    foreach (Intermediair.ContractenItem contract in categorie.Contract)
                    {
                        contracten[categorie.Meldingtype.Id][categorie.Id].Add(contract.Titel, contract.Id);
                    }
                }
            }
        }
        dataLoaded = true;
    }

Answer 2

There are some limitations to linq in SharePoint 2010. Maybe this article can put you on the right track: http://www.chaholl.com/archive/2010/03/12/joins-in-linq-to-sharepoint-2010.aspx