Hi All,

I am hitting my head to to write code for the following query but haven't had any luck so far. It uses a dervied table with join inside as well as another join with derived table results as well. If anyone can help me with it, it will be highly appreciated.

SELECT A.ID, A.Name
FROM ResultNames A
LEFT JOIN 
(
    SELECT A.Name, B.ID FROM ResultsMapping B JOIN ResultsMappingDetails C ON B.ID = C.ID WHERE B.Name = 'Some Name'
) D
ON A.ID = D.ID
WHERE A.Name = 'another name'
AND D.ID IS NULL

I am using Self Servicing model for entities ( and not the Linq)

Do you really need the inner sub select - most queries like this can be written as a series of joins - and usually get optimised by SQL to the same thing.

If you do, DerivedTableDefinitions accept Relation objects which let you define the joins between the tables.

Could you show us what you have got so far, and what's not working about it ?

Matt

This is what I got at this point. I think the only thing I am not getting is to figure out how to add that additional filter D.ID IS NULL that need to be applied on the dervied table. If you can give me some idea on how to do, it will be great.

ResultsetFields fields = new ResultsetFields(2);
            fields.DefineField(ResultsMappingDetailFields.Name, 0);
            fields.DefineField(ResultsMappingDetailFields.Id, 1);

            RelationCollection relationsForDerivedTable = new RelationCollection();
            relationsForDerivedTable.Add(ResultsMappingEntity.Relations.ResultsMappingDetailEntityUsingId);

            IPredicateExpression selectFilter = new PredicateExpression(ResultsMappingFields.Name == 'Some Name');
            DerivedTableDefinition dtDerived = new DerivedTableDefinition(fields, "c", selectFilter, relationsForDerivedTable);

            // specify the relation which is a dynamic relation. 
            DynamicRelation relation = new DynamicRelation(dtDerived, JoinHint.Right, EntityType.ResultNameEntity, "o",
                                           (new EntityField(ResultsMappingDetailFieldIndex.Id.ToString(), "c", typeof(string)) == ResultNameFields.Id.SetObjectAlias("o")));

            RelationCollection relations = new RelationCollection();
            relations.Add(relation);
            relations.SelectListAlias = "o";  // entities should target the aliased order table. 

            // fetch the data
            ResultNameCollection orders = new ResultNameCollection();
            IPredicateExpression selectFilter2 = new PredicateExpression(ResultNameFields.ProviderName == 'another name');

the only thing I am not getting is to figure out how to add that additional filter D.ID IS NULL that need to be applied on the dervied table

I guess you meant applied on the outer select.

Why don't you try using an EntityField with an ObjectAlias set to the propper alias (the one you used in the DynamicRelation to refer to the derived table).

Anyway I think you'd better define the field in the select list (ResultSetFields), and then you can use this field in your IsNull predicate.

Yes I meant the outer select. This is what I am trying to filter on the outer select

            // fetch the data
            ResultNameCollection orders = new ResultNameCollection();
            IPredicateExpression selectFilter2 = new PredicateExpression(ResultNameFields.Name == 'another name');
            selectFilter2.AddWithAnd(new EntityField2("Id", "o", typeof(int)) == DBNull.Value);
            orders.GetMulti(selectFilter2, relations);

But now I am getting following exception. When I see the query in query profiler its not using the alias names that I have provided in the query above, rather its showing alias names like LPA_c1 and LPA_o2. Any ideas?

Object reference not set to an instance of an object. at SD.LLBLGen.Pro.DQE.SqlServer.SqlServerSpecificCreator.CreateParameter(IEntityFieldCore field, IFieldPersistenceInfo persistenceInfo, ParameterDirection direction, Object valueToSet) at SD.LLBLGen.Pro.ORMSupportClasses.FieldCompareValuePredicate.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.DaoBase.PerformGetMultiAction(ITransaction containingTransaction, IEntityCollection collectionToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPredicate selectFilter, IRelationCollection relations, IPrefetchPath prefetchPathToUse, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetMulti(ITransaction containingTransaction, IEntityCollection collectionToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IEntityFactory entityFactoryToUse, IPredicate selectFilter, IRelationCollection relations, IPrefetchPath prefetchPathToUse, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.EntityCollectionBase1.GetMulti(IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relations, IPrefetchPath prefetchPathToUse, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.EntityCollectionBase1.GetMulti(IPredicate selectFilter, IRelationCollection relations) at EA.Common.User_Control_Classes.ctlLabMappingAddEdit.UpdateAvailableResultNames(String labName, String mappingName) in C:\SandBox\EMR_Advantage\branches\3.2-Devel\icare2.0\Common\User Control Classes\ctlLabMappingAddEdit.cs:line 241 at EA.Common.User_Control_Classes.ctlLabMappingAddEdit.btnAddResultNameToMapping_Click(Object sender, EventArgs e) in C:\SandBox\EMR_Advantage\branches\3.2-Devel\icare2.0\Common\User Control Classes\ctlLabMappingAddEdit.cs:line 151 at DevExpress.XtraEditors.Repository.RepositoryItemButtonEdit.RaiseButtonClick(ButtonPressedEventArgs e) at DevExpress.XtraEditors.ButtonEdit.OnClickButton(EditorButtonObjectInfoArgs buttonInfo) at DevExpress.XtraEditors.ButtonEdit.OnMouseUp(MouseEventArgs e) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at DevExpress.Utils.Controls.ControlBase.WndProc(Message& m) at DevExpress.XtraEditors.TextEdit.WndProc(Message& msg) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

I guess the field's alias should be "c" instead of "o" Anyway I suggest you should revise all the aliases used, as I find some discrepancies between the query and the code.

Also test the SQL query against the database first before proceeding with code.

well the sql query itself works fine and giving me correct results. I tried c instead of o for alias names but still getting same exception. May be I am just misunderstanding this whole idea of derived table and how to relate it with the regular tables using LLBL. Will you mind just writing sample code against the query I have provided?

I wasn't speaking about changing all the alias names. Just the one in the following code:

        ResultNameCollection orders = new ResultNameCollection();
        IPredicateExpression selectFilter2 = new PredicateExpression(ResultNameFields.Name == 'another name');
        selectFilter2.AddWithAnd(new EntityField2("Id", "o", typeof(int)) == DBNull.Value);
        orders.GetMulti(selectFilter2, relations);

Since the derived table has an alias of "c".

I'll try to cook a simple example for you.

That's exactly what I tired, I only changed the alias name in the selectFilter2.AddWithAnd and got same exception.

I appreciate your help

I made up the following query against Northwind, which look similar to your query.

SELECT C.CompanyName, C.Country, D.OrderID
FROM Customers C
LEFT JOIN 
(
    SELECT O.OrderID, O.EmployeeID, O.CustomerID FROM Orders O JOIN [Order Details] OD ON O.OrderID = OD.OrderID WHERE OD.ProductID = 14
) D
ON C.CustomerID = D.CustomerID
WHERE C.Country= 'France'
AND D.OrderID Is Null

And when run against the databsae, it gives the following results:

Blondesddsl père et fils    France  NULL
Du monde entier                 France  NULL
Folies gourmandes           France  NULL
France restauration         France  NULL
La corne d'abondance            France  NULL
La maison d'Asie            France  NULL
Paris spécialités           France  NULL
Spécialités du monde            France  NULL
Victuailles en stock            France  NULL
Vins et alcools Chevalier   France  NULL

And here is the code to produce the above.

            // Build the Derived Table
            var subfields = new ResultsetFields(3);
            subfields.DefineField(OrderFields.OrderId, 0);
            subfields.DefineField(OrderFields.EmployeeId, 1);
            subfields.DefineField(OrderFields.CustomerId, 2);

            var subFilter = new PredicateExpression(OrderDetailFields.ProductId == 14);
            var subRelation = new RelationCollection(OrderEntity.Relations.OrderDetailEntityUsingOrderId);

            var dtDefinition = new DerivedTableDefinition(subfields, "O", subFilter, subRelation);

            var relation = new DynamicRelation(dtDefinition, JoinHint.Right, EntityType.CustomerEntity, "C",
                                           (new EntityField(OrderFieldIndex.CustomerId.ToString(), "O", typeof(string)) ==
                                            CustomerFields.CustomerId.SetObjectAlias("C")));

            var relations = new RelationCollection(relation);

            // Define the Main Query
            var fields = new ResultsetFields(2);
            fields.DefineField(CustomerFields.CompanyName, 0, "CompanyName", "C");
            fields.DefineField(CustomerFields.Country, 1, "Country", "C");

            var filter = new PredicateExpression();
            filter.Add(((EntityField)fields[1]) == "France");
            filter.Add(OrderFields.OrderId.SetObjectAlias("O") == DBNull.Value);

            var dynamicList = new DataTable();
            var dao = new TypedListDAO();
            dao.GetMultiAsDataTable(fields, dynamicList, 0, null, filter, relations, false, null, null, 0, 0);

Hope this helps.

Walaa,

You are the Man It worked like a charm. Thanks again for your help. Highly appreciated.

a little twist added in this query I need to add sort by in the query but the problem is that everything have to be access via the alias and not sure how to do that. So let's say I want to sort the result from above query by Country and Company names. Any example will be much helpful.

If you want to sort on such fields, you can just use them in the sortexpression:

...
var sorter = new SortExpression();
sorter.Add(new SortClause(fields[1], SortOperator.Ascending)); // country
sorter.Add(new SortClause(fields[0], SortOperator.Ascending)); // company

var dynamicList = new DataTable();
var dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, dynamicList, 0, sorter, filter, relations, false, null, null, 0, 0);