SD.LLBLGen.Pro.DQE.SqlServer.NET20.dll 2.6.8.1114 SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll 2.6.9.220 SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll 2.6.9.116

I am getting an "Index was outside the bounds of the array" whenever I try to run the following linq query:

var projects = projectRepository.GetAll().Where(p => p.ProjectStaff.Any(s => s.Person.LastName.Contains("wil"))).ToList();

projectRepository.GetAll() returns an IQueryable<Project> and projects the ProjectEntity to an object of type Project.

All other queries I have used work fine. Thanks for your help

Source="SD.LLBLGen.Pro.DQE.SqlServer.NET20" StackTrace: at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Boolean relationsSpecified, Boolean sortClausesSpecified) at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause) at SD.LLBLGen.Pro.DQE.SqlServer.SqlServerSpecificCreator.CreateSubQuery(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldPersistenceInfos, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32& uniqueMarker) at SD.LLBLGen.Pro.ORMSupportClasses.DbSpecificCreatorBase.CreateSubQuery(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldPersistenceInfos, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, IGroupByCollection groupByClause, Int32& uniqueMarker) at SD.LLBLGen.Pro.ORMSupportClasses.FieldCompareSetPredicate.ToQueryText(Int32& uniqueMarker, Boolean inHavingClause) at SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression.ToQueryText(Int32& uniqueMarker, Boolean inHavingClause) at SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression.ToQueryText(Int32& uniqueMarker, Boolean inHavingClause) at SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression.ToQueryText(Int32& uniqueMarker, Boolean inHavingClause) at SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression.ToQueryText(Int32& uniqueMarker) at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Boolean relationsSpecified, Boolean sortClausesSpecified) at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause) at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreatePagingSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateQueryFromElements(IEntityFields2 fieldCollectionToFetch, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize, IFieldPersistenceInfo[]& persistenceInfo, IRetrievalQuery& selectQuery) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, IEntityFields2 fields, IRelationPredicateBucket filter, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteHierarchicalValueListProjection(QueryExpression toExecute, IRelationPredicateBucket additionalFilter, ITemplateGroupSpecificCreator frameworkElementCreator) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery1.Execute() at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source)

Pease post the code snippet of the _GetAll _method.

And update to the latest build of the runtimes, your builds are quite old.

I updated the runtimes yesterday. These version numbers are from the files in runtime build 03092009. Does this contain the latest?

http://www.llblgen.com/pages/secure/filestreamer.aspx?DownloadID=131

nabils wrote:

I updated the runtimes yesterday. These version numbers are from the files in runtime build 03092009. Does this contain the latest?

http://www.llblgen.com/pages/secure/filestreamer.aspx?DownloadID=131

Sorry I didn't look properly, I thought it were assemblies from september 2008.

nabils wrote:

I updated the runtimes yesterday. These version numbers are from the files in runtime build 03092009. Does this contain the latest?

http://www.llblgen.com/pages/secure/filestreamer.aspx?DownloadID=131

Sorry I didn't look properly, I thought it were assemblies from september 2008.

Indeed, we need the code for GetAll(). please understand that with linq, it's complex to determine what the problem might be, and we therefore need all the info you can provide.

I know sorry. I try to narrow down all other possibilities before I submit any code as it takes time to put together a test case that does not contain any confidential code.

The code is below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LLBLGenPro.ORM.DatabaseSpecific;
using LLBLGenPro.ORM.Linq;
using SD.LLBLGen.Pro.ORMSupportClasses;

namespace ConsoleApplication7
{
    class Program
    {
        static void Main(string[] args)
        {
            var repository = new ProjectRepository();
            using (repository.Adapter = new DataAccessAdapter())
            {
                var list = repository.GetAll().Where(p => p.ProjectStaff.Any(s => s.Person.LastName.Contains("wil"))).ToList();
            }
        }

    }

    public class Project
    {
        public decimal ProjectId { get; set; }
        public IEnumerable<ProjectStaff> ProjectStaff { get; set; }
    }
    public class ProjectStaff
    {
        public decimal ProjectStaffId { get; set; }
        public Person Person { get; set; }
    }

    public class Person
    {
        public decimal PersonId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

    public class ProjectRepository
    {
        private IDataAccessAdapter _adapter;

        public IDataAccessAdapter Adapter
        {
            get { return _adapter; }
            set { _adapter = value; }
        }

        public IQueryable<Project> GetAll()
        {
            return new LinqMetaData(_adapter).Project.Select(project => new Project
            {
                ProjectId = project.ProjectId,
                ProjectStaff = project.ProjectStaff.Select(p => new ProjectStaff()
                        {
                            ProjectStaffId = p.ProjectStaffId,
                            Person = new Person
                                         {
                                             PersonId = p.PersonId,
                                             FirstName = p.Person.FirstName,
                                             LastName = p.Person.LastName
                                         }
                        })
            });
        }
    }
}

I think the projection to a custom class which will be wrapped inside the rest of the query you appended is the cause of the issue.

It should propagate the projection upwards (as the resulting query doesn't have a projection), though I'm not sure if this is the issue, but I'll try to reproduce it with your code.

I tried this:

var list = repository.GetAll().Where(p => p.ProjectStaff.Any(s => s.ProjectStaffId > 800)).ToList();

And it still gave the same error.

If you need me to test anything else please let me know. Thanks

nabils wrote:

I tried this:

var list = repository.GetAll().Where(p => p.ProjectStaff.Any(s => s.ProjectStaffId > 800)).ToList();

And it still gave the same error.

If you need me to test anything else please let me know. Thanks

I think if you get rid of the Person valuetype instance inside the returned Project class, it will work. This is not what you have in mind I guess, so I'll see if I can fix this. The problem is I think that the projection to produce that Person instance is wrapped inside a derived table though I'll run some tests to see if that's really the case. I'll get back to you on this.

No it doesn't. Still gives the same error.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LLBLGenPro.ORM.DatabaseSpecific;
using LLBLGenPro.ORM.Linq;
using SD.LLBLGen.Pro.ORMSupportClasses;

namespace ConsoleApplication7
{
    class Program
    {
        static void Main(string[] args)
        {
            var repository = new ProjectRepository();
            using (repository.Adapter = new DataAccessAdapter())
            {
                var list = repository.GetAll().Where(p => p.ProjectStaff.Any(s => s.ProjectStaffId > 800)).ToList();
            }
        }

    }

    public class Project
    {
        public decimal ProjectId { get; set; }
        public IEnumerable<ProjectStaff> ProjectStaff { get; set; }
    }
    public class ProjectStaff
    {
        public decimal ProjectStaffId { get; set; }
        public Person Person { get; set; }
    }

    public class Person
    {
        public decimal PersonId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

    public class ProjectRepository
    {
        private IDataAccessAdapter _adapter;

        public IDataAccessAdapter Adapter
        {
            get { return _adapter; }
            set { _adapter = value; }
        }

        public IQueryable<Project> GetAll()
        {
            return new LinqMetaData(_adapter).Project.Select(project => new Project
            {
                ProjectId = project.ProjectId,
                ProjectStaff = project.ProjectStaff.Select(p => new ProjectStaff()
                        {
                            ProjectStaffId = p.ProjectStaffId
                        })
            });
        }
    }



}

It's the nested query.

When I do:

private IQueryable<Customer> GetAllCustomers(LinqMetaData metaData)
{
    return metaData.Customer.Select(
        c => new Customer {
                            CompanyNme = c.CustomerId,
                            Orders = c.Orders.Select(
                                o => new Order() {
                                                    OrdId = o.OrderId,
                                                    RelatedEmployee = new Employee() 
                                                        {
                                                            FirstName = o.Employee.FirstName, LastName = o.Employee.LastName
                                                        }
                                                 })
                          });
}

and run it like:

[Test]
public void NestedQueryInNestedProjection()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        var allCustomers = GetAllCustomers(metaData);
        foreach(var v in allCustomers)
        {

        }
    }
}

it works correctly. (there's no test on the data returned in the test above, but the queries produced are the ones expected.)

However when I do:

[Test]
public void NestedQueryInNestedProjection()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        var allCustomers = GetAllCustomers(metaData).Where(c=>c.Orders.Any(o=>o.RelatedEmployee.FirstName.Contains("e")));
        foreach(var v in allCustomers)
        {

        }
    }
}

it fails.

The problem is this: the Where + Any clause target data which is fetched with a nested query. Nested queries are executed AFTER the main query (similar to prefetch paths) and then merged together. However, this leads to a catch 22: the main query filters on the nested query's data.

So this can't be done in the way you want it. I don't know your project's requirements so I can't suggest here what you should do, e.g. break open the repository or use a specification pattern inside the repository to produce the filter there.

It's a bit caused by Linq's nature to blur the line between query in-memory and query in-database. Actually your where + any query should be executed after the GetAll query has been ran on the DB (sequence consumed by sequence operator etc.) however, you want to merge the Where + Any query with the main query. This can't happen due to the nested query which is the source of the filter you execute.

Mmm. Ok managed to get it working using the specification pattern. However I'm not completely satisfied as it has forced me to expose the llblgen generated entities up a layer. Do you have any suggestions to avoid this??

By the way thanks again Otis for your first class support. Much appreciated.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using LLBLGenPro.ORM.DatabaseSpecific;
using LLBLGenPro.ORM.EntityClasses;
using LLBLGenPro.ORM.Linq;
using SD.LLBLGen.Pro.ORMSupportClasses;
using Expression = System.Linq.Expressions.Expression;

namespace ConsoleApplication7
{
    class Program
    {
        static void Main(string[] args)
        {
            var predicate = LlblgenPredicateBuilder.Null<Project>();

            predicate = predicate.And(p => p.ProjectId == 2303);

            var repository = new ProjectRepository();
            using (repository.Adapter = new DataAccessAdapter())
            {
                //var list = repository.GetAll().Where(p => p.ProjectStaff.Any(s => s.Person.LastName.Contains("wil"))).ToList();
                var query = repository.FindBySpecification(new Specification<ProjectEntity>(p => p.ProjectStaff.Any(s => s.Person.LastName.Contains("wil"))));
                var list = query.Where(predicate).ToList();
            }
        }

    }

    public class Project
    {
        public decimal ProjectId { get; set; }
        public IEnumerable<ProjectStaff> ProjectStaff { get; set; }
    }
    public class ProjectStaff
    {
        public decimal ProjectStaffId { get; set; }
        public Person Person { get; set; }
    }

    public class Person
    {
        public decimal PersonId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

    public class ProjectRepository
    {
        private IDataAccessAdapter _adapter;

        public IDataAccessAdapter Adapter
        {
            get { return _adapter; }
            set { _adapter = value; }
        }

        public IQueryable<Project> GetAll()
        {
            return new LinqMetaData(_adapter).Project.Select(project => new Project
            {
                ProjectId = project.ProjectId,
                ProjectStaff = project.ProjectStaff.Select(p => new ProjectStaff()
                {
                    ProjectStaffId = p.ProjectStaffId,

                    Person = new Person
                                 {
                                     PersonId = p.PersonId,
                                     FirstName = p.Person.FirstName,
                                     LastName = p.Person.LastName
                                 }
                })
            });
        }

        readonly Expression<Func<ProjectEntity, Project>> projectSelector = (project) => new Project
        {

            ProjectId = project.ProjectId,
            ProjectStaff = project.ProjectStaff.Select(p => new ProjectStaff()
                    {
                        ProjectStaffId = p.ProjectStaffId
                    })
        };

        public IQueryable<Project> FindBySpecification(Specification<ProjectEntity> specification)
        {
            return new LinqMetaData(_adapter).Project.Where(specification.Predicate).Select(projectSelector);
        }
    }

    public interface ISpecification<T>
    {
        bool IsSatisfiedBy(T item);
        Expression<Func<T, bool>> Predicate { get; }
        T SatisfyingElementFrom(IQueryable<T> candidates);
        IQueryable<T> SatisfyingElementsFrom(IQueryable<T> candidates);
    }

    public class Specification<T> : ISpecification<T>
    {
        private readonly Expression<Func<T, bool>> _predicate;

        /// <summary>
        /// Default Constructor.
        /// Creates a new instance of the <see cref="Specification{T}"/> instnace with the
        /// provided predicate expression.
        /// </summary>
        /// <param name="predicate">A predicate that can be used to check entities that
        /// satisfy the specification.</param>
        public Specification(Expression<Func<T, bool>> predicate)
        {
            _predicate = predicate;
        }

        /// <summary>
        /// Gets the expression that encapsulates the criteria of the specification.
        /// </summary>
        public Expression<Func<T, bool>> Predicate
        {
            get { return _predicate; }
        }

        /// <summary>
        /// Evaluates the specification against an entity of <typeparamref name="T"/>.
        /// </summary>
        /// <param name="entity">The <typeparamref name="T"/> instance to evaulate the specificaton
        /// against.</param>
        /// <returns>Should return true if the specification was satisfied by the entity, else false. </returns>
        public bool IsSatisfiedBy(T entity)
        {
            return _predicate.Compile().Invoke(entity);
        }

        public T SatisfyingElementFrom(IQueryable<T> candidates)
        {
            return SatisfyingElementsFrom(candidates).Single();
        }

        public IQueryable<T> SatisfyingElementsFrom(IQueryable<T> candidates)
        {
            return candidates.Where(Predicate).AsQueryable();
        }

        /// <summary> 
        /// Overloads the & operator and combines two <see cref="Specification{T}"/> in a Boolean And expression 
        /// and returns a new see cref="Specification{T}"/>. 
        /// </summary> 
        /// <param name="leftHand">The left hand <see cref="Specification{T}"/> to combine.</param> 
        /// <param name="rightHand">The right hand <see cref="Specification{T}"/> to combine.</param> 
        /// <returns>The combined <see cref="Specification{T}"/> instance.</returns> 
        public static Specification<T> operator &(Specification<T> leftHand, Specification<T> rightHand)
        {
            var rightInvoke = Expression.Invoke(rightHand.Predicate, leftHand.Predicate.Parameters.Cast<Expression>());
            var newExpression = Expression.MakeBinary(ExpressionType.AndAlso, leftHand.Predicate.Body, rightInvoke);
            return new Specification<T>(Expression.Lambda<Func<T, bool>>(newExpression, leftHand.Predicate.Parameters));
        }

        /// <summary> 
        /// Overloads the & operator and combines two <see cref="Specification{T}"/> in a Boolean Or expression 
        /// and returns a new see cref="Specification{T}"/>. 
        /// </summary> 
        /// <param name="leftHand">The left hand <see cref="Specification{T}"/> to combine.</param> 
        /// <param name="rightHand">The right hand <see cref="Specification{T}"/> to combine.</param> 
        /// <returns>The combined <see cref="Specification{T}"/> instance.</returns> 
        public static Specification<T> operator |(Specification<T> leftHand, Specification<T> rightHand)
        {
            var rightInvoke = Expression.Invoke(rightHand.Predicate, leftHand.Predicate.Parameters.Cast<Expression>());
            var newExpression = Expression.MakeBinary(ExpressionType.OrElse, leftHand.Predicate.Body, rightInvoke);
            return new Specification<T>(Expression.Lambda<Func<T, bool>>(newExpression, leftHand.Predicate.Parameters));
        }
    }
}

Clever

It's indeed a bit of a catch22: you need to specify a filter on elements you don't know at the spot where you're defining the filter but defining it after the query isn't going to fly as you've seen.

What you'd like to do is pass some kind of filter specification to GetAll(), or a method similar to getall which retrieves the data based on a filter specified. The problem of course is: how to specify that filter. In the general DDD sense, one should use a specification implementation which is then converted to the real filter. This of course creates extra overhead so a specification which is directly usable is indeed better, though this has other consequences.

Another alternative might be a special method on the repository for the purpose you're needing it for. This encapsulates a bit what you're fetching and how (as that's up to the repository) and gives you the freedom to specify the query more easier inside the repository, as you're filtering on a single element for example.