We are working with LLBLGen v2.6 Final (October 9th, 2009) (runtime library version 2.6.9.1202), against SQL Server 2005, and trying to pull back records which do not have a certain relation. This relation is a category with a one to many mapping, and one of our entities can have zero or many categories. We want the ones with zero.

We have tried the following linq query, with little success. NoCategories is a boolean intended to indicate that we only want the people who have no categories assigned to them:

var linq = new LinqMetaData(Adapter);
    var NoCategories = true;
    List<Int32> selectedCategories = {1,2,3,4,5}.ToList();

    var people = from people in linq.People_Table
                                 join category in linq.Categories on people.ID equals category.Module_ID into categoriesRelation
                                 from category in categoriesRelation.DefaultIfEmpty()
                                 where (selectedCategories.Count == 0 && !NoCategories 
                                         || selectedCategories.Contains(category.Category_ID) 
                                         || (NoCategories && categoriesRelation.Any()))
                                 select people;

We get the following error:

SD.LLBLGen.Pro.DQE.SqlServer.NET20 error '80131508' 
    Index was outside the bounds of the array. 

By the way, we are not working with nullable types in our llbl model.

Please post the relevant tables structure. And the SQL Query you want to execute.

The following illustrates the data we have. Obviously it is not real data, but we have verified that it accurately reflects what we are trying to do. There is no actual relationship between People_Table and Entity_To_Category, as Entity_To_Category can handle many different types of entities (which we have tried to show by including categories in the categories table which do not relate to people).

People_Table

ID Name 1 Joe 2 Fred 3 Billy 4 Jim

Entity_To_Category

Entity_ID Category_ID Entity_Type 1 3 PERSON 2 1 PERSON 3 2 PERSON

Category

ID Category 1 Fishermen 2 Bakers 3 Candlestick makers 4 4 wheel drive 5 Refrigerated 6 French cheeses

The query should be:

select p.* from People_Table p
left join Entity_To_Category etc on p.ID = etc.Entity_ID and etc.Entity_Type = 'PERSON'
where etc.Category_ID is null

Isn't this easier?

SELECT p.* 
FROM People_Table
WHERE p.ID NOT IN
     (SELECT etc.Entity_ID 
       FROM Entity_To_Category etc
       WHERE etc.Entity_Type = 'PERSON'
       AND etc.Category_ID IN (1,2,3, ...)
     )

List<Int32> selectedCategories = {1,2,3,4,5}.ToList();
var categories = from c in metaData.Entity_To_Category
                          where selectedCategories.Contains(c.Category_ID)
                          select c.Entity_ID;

var peopleWithoutCategory = (from p in metaData.People_Table
                                              where !categories.Contains(p.ID)
                                               select p).ToList();

Unfortunately, that's solving a slightly different problem. We don't want people whose categories are not in the list that's defined, we want people who have no categories at all.

We found a solution. It turns out it's quite straightforward, but not immediately intuitive (to my mind at least). It goes something like this:

var firstquery = from people in linq.People_Table
                             select new
                                        {
                                            personID = people.ID,
                                            categoriesIDs = from cat in linq.Entity_To_Category
                                                            where people.ID == cat.Entity_ID
                                                            && cat.Entity_Type == "PERSON"
                                                            select cat.Category_ID
                                        };

            var people = from ent in firstquery
                                 join person in linq.People_Table on ent.PersonID equals person.ID
                                 select person;


var result = from entity in firstquery.ToList() 
                                    join person in people.ToList() on entity.personID equals person.ID
                                    where (categoriesList.Count == 0 && !NoCategories ||
                                    categoriesList.Where(x => entity.categoriesIDs.Contains(x)).Any() || 
                                    (NoCategories && entity.categoriesIDs.Count()==0))
                                    select people;

The above is capable of retrieving either everyone (if no categories are specified, and the NoCategories flag is not set), people who have categories in the specified list, or (if NoCategories is set) those people who do not have any categories at all. It might be possible to condense the above somewhat, but we are confident we are now getting the results we wanted.

Thanks for updating...!

Matt