Hi, Am fairly new to LLBLGEN and am currently facing an issue with entity hierarchy and the resulting SELECT query containing more than 4096 columns , which is resulting in SQL Server error.

Here's the scenario:

Assume I have a hierarchy where parent Entity is A and entities B,C,D,E,F,G are inheriting from Entity A and the inheritance type is TargetPerEntity .

In our applications Users can create the entities and it so happened that one of the users has created the hierarchy above and each of the child entities ended up having up to 1000 columns (yes I got the 0's right).

So when the app code is trying to fetch entities of type A, the resulting query is including all the columns from entity which is more the 4096.

We are working on restricting the way the entities are created in the app to avoid this issue in future.

However till then I am trying to see if we have any possible workaround for this issue (as an interim solution).

So far I have tried:

1) Dynamically adding prefetch path's (when A is on the other end of OneToMany relation) for each subtype dynamically in the overridden FetchPrefetchPath method, but it has its own issues if Sort, Limit rows is applied.

SQL Server how ever happily returns data if the query is framed as below:

SELECT a.*,b.*,c.*,d.*,e.*,f.*,g.*
  FROM A 
  LEFT JOIN (SELECT <Table B Columns> FROM B) ON A.PrimaryKey = B.PrimaryKey
  LEFT JOIN (SELECT <Table C Columns> FROM C) ON A.PrimaryKey = C.PrimaryKey
  LEFT JOIN (SELECT <Table D Columns> FROM D) ON A.PrimaryKey = D.PrimaryKey
  LEFT JOIN (SELECT <Table E Columns> FROM E) ON A.PrimaryKey = E.PrimaryKey
  LEFT JOIN (SELECT <Table F Columns> FROM F) ON A.PrimaryKey = F.PrimaryKey
  LEFT JOIN (SELECT <Table G Columns> FROM G) ON A.PrimaryKey = G.PrimaryKey

I am not able to understand how I would be able to generate a query like above. Any pointers?

Let me know if the question is not clear.

So when the app code is trying to fetch entities of type A, the resulting query is including all the columns from entity which is more the 4096

So why are you fetching all entities of type A, can you change that. I mean instead of fetching entities, you can compose a DynamicList, and fetch the fields you need, also you can fetch specific entities while specifying fields to be fetched or not to be fetched. (Exclude/Include fields).

I'd go for a DynamicList, with the fields and relations you need.

The child entities have foreign key relation to another set of entities which form most part of the 1000 columns list.

I need to load the entities by type A, for one of the scenario where I have another Entity say CustomerEntity

public class CustomerEntity
{
         A[] AEntities {get; set;}
}

When I load the Prefetch Path for AEntities, AEntities could have rows across any of the child entities of A.

Hope I was clear

• Despite all of that, you could use DynamicList, right?
• Is there a way you can reconsider the design of your inheritance and relations?

Thanks Daelmo.

The new approach for the relations is underway, but it might take a while to get that done. We have a user who wants to use the app and we are looking for an interim solution.

I have never used DynamicList, will take a look at it.

For now I found a work around. For some reason if the query with columns > 4096 is framed as below it works on SQL Server,

SELECT *
   FROM  
  (
    SELECT <LONG LIST OF COLUMNS GREATER THAN 4096>
        FROM <WHAT EVER TABLE LIST>
  ) A

Can you guide me on how to generate the query as above given the final selectList and all other params in CreateSelectDQ?

So far I tried to use a derived table based on the parameters received in the CreateSelectDQ override as given below (ignore the crude form of the code as I quickly wanted to try the idea).

protected override IRetrievalQuery CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, long maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, bool allowDuplicates, IGroupByCollection groupByClause, int pageNumber, int pageSize)
{
    var derivedTable = new DerivedTableDefinition(fieldsToFetch, "aa", filter, relationsToWalk, null, groupByClause, 0, allowDuplicates);
    for (var i = 0; i < persistenceInfoObjects.Count(); i++)
    {
        derivedTable.SetFieldPersistenceInfo(persistenceInfoObjects[i], i);
    }
    var relationCollection = new RelationCollection();
    relationCollection.Add(new DynamicRelation(derivedTable));
    var resultFields = new ResultsetFields(1);
    resultFields.DefineField(new EntityField2("*", derivedTable.Alias, typeof (int)), 0);
    var persistenceInfo = new FieldPersistenceInfo("*");
    var perInfo = new IFieldPersistenceInfo[1];
    perInfo[0] = persistenceInfo;
    return base.CreateSelectDQ(resultFields, perInfo, null, maxNumberOfItemsToReturn, sortClauses, relationCollection, allowDuplicates, null, pageNumber, pageSize);
}

So far it looks ok, however am stuck with the issue when sort clauses are specified. Since the the columns are aliased in the subquery, am not sure how to make the order by use the aliased column names instead of the column names used in the sub query.

i.e. So far I am able to generate something like

SELECT *
  FROM (SELECT A.Id AS F1_0, B.Name AS F1_1 FROM A LEFT JOIN B ON A.Id = B.Id)
ORDER BY A.Id, B.Name

as you can see this query would fail.

Any tips on how to make Order By use the final aliases applied to the column.

OK. I have tried another approach to generate the query I am looking for. Basically, let the DynamicQueryEngine create, structure the retrieval query. Then check if the fields in the resulting query are > 4096, then manipulate the query.Command.CommandText to wrap the actual query as a Sub Query.

Can you please let me know if the approach is ok?

Draft code changes are given below:

public class MyCustomQueryEngine:DynamicQueryEngine
{
    protected override void CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IRetrievalQuery query, IPredicate selectFilter, long maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, bool allowDuplicates, IGroupByCollection groupByClause, bool relationsSpecified, bool sortClausesSpecified)
    { 
        base.CreateSelectDQ(selectList, fieldsPersistenceInfo, query, selectFilter, maxNumberOfItemsToReturn, sortClauses, relationsToWalk, allowDuplicates, groupByClause, relationsSpecified, sortClausesSpecified);
         if (selectList.Length > 4096 && fieldsPersistenceInfo.Count(a => a != null) > 4096)
        {
            var sql = query.Command.CommandText;
            if (!sql.StartsWith("SELECT ")) return;

            string newSql;
            if (!sql.StartsWith("SELECT DISTINCT"))
            {
                newSql = string.Format("SELECT * FROM ({0}) {1}", sql, "MPA_" + (new Random()).Next(9));
            }
            else
            {
                newSql = string.Format("SELECT {2}.* FROM (SELECT DISTINCT TOP {1} {0}) AS {2}", sql.Substring(16), Int32.MaxValue, "MPA_" + (new Random()).Next(9));
            }
            query.Command.CommandText = newSql;
        }
    }
}
    

Seems ok to me.

Thanks for your help Walaa/Daelmo. Am proceeding with this approach.