Hi

I'm revisitng an older problem here, but I think the SQL I have now got will help explain this a little better. Here's what I want:

SELECT   Profile.CombinationID, Profile.Name
FROM         dbo.ProdCombo AS Profile INNER JOIN
                      dbo.ProdComboProfile AS ProfileValue4 ON Profile.CombinationID = ProfileValue4.ProdComboID INNER JOIN
                      dbo.ProdComboProfile AS ProfileValue3 ON Profile.CombinationID = ProfileValue3.ProdComboID INNER JOIN
                      dbo.ProdComboProfile AS ProfileValue2 ON Profile.CombinationID = ProfileValue2.ProdComboID INNER JOIN
                      dbo.ProdComboProfile AS ProfileValue1 ON Profile.CombinationID = ProfileValue1.ProdComboID
WHERE    (ProfileValue1.ProdAttributeID = 40) AND (ProfileValue2.ProdAttributeID = 3) AND (ProfileValue3.ProdAttributeID = 116) AND 
                      (ProfileValue4.ProdAttributeID = 143)

I'm trying to look up how many ProdCombo entities I have, with values taken from the Profile table.

I had a look at this post: http://llblgen.com/TinyForum/Messages.aspx?ThreadID=11745

However, I had the problem that (see final post):

DataAccessAdapter adapter = new DataAccessAdapter();

Is not allowed even though I've tried adding all the namespaces I can think of... I can reference IDataAccessAdapter and DataAccessAdapterBase though and if I use the IDataAccessAdapter access adapter, it wants me to use EntityField2.DefineField rather than fields.DefineField which goes on to unravel the code.

I was wondering if I can use the GetMulti() method to do this, or if I have to go down the typed list route?

If I can use GetMulti by adding in my relations like in the post I mentioned, then can anyone give me some good pointers... and failling that can anyone think why I can't reference IDataAccessAdapter?

Many thanks for having the patience to read all that!!

Best regards

Darren

Hi Darren,

If you are using SelfServicing, you have to use GetMultiAsDataTable, something like:

ResultsetFields fields = new ResultsetFields(3);
fields.DefineField(EmployeeFields.FirstName, 0, "FirstNameManager", "Manager");
fields.DefineField(EmployeeFields.LastName, 1, "LastNameManager", "Manager");
fields.DefineField(EmployeeFields.LastName, 2, "AmountEmployees", "Employee", AggregateFunction.Count);
IRelationCollection relations = new RelationCollection();
relations.Add(EmployeeEntity.Relations.EmployeeEntityUsingEmployeeId, "Employee", "Manager", JoinHint.None);

IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields[0]);
groupByClause.Add(fields[1]);

DataTable dynamicList = new DataTable();
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, dynamicList, 0, null, null, relations, true, groupByClause, null, 0, 0);

for more info, please read LLBLGenPro Help - Using generated code - Using typedViews, typedLists and dynamicLists - Using dynamicLists.

Regards,

Hi daelmo - thanks for the response that looks to be exactly what I am looking for - though where I have the problem is with the filter. Here's the C# that I now have:

ResultsetFields fields = new ResultsetFields(6);
                fields.DefineField(ProductTypeOptionAttributeCombinationFields.CombinationId, 0);
                fields.DefineField(ProductTypeOptionAttributeCombinationFields.ProductId, 1);
                fields.DefineField(ProductTypeOptionAttributeCombinationProfileFields.ProductTypeOptionAttributeId, 2, "Att1");
                fields.DefineField(ProductTypeOptionAttributeCombinationProfileFields.ProductTypeOptionAttributeId, 3, "Att2");
                fields.DefineField(ProductTypeOptionAttributeCombinationProfileFields.ProductTypeOptionAttributeId, 4, "Att3");
                fields.DefineField(ProductTypeOptionAttributeCombinationProfileFields.ProductTypeOptionAttributeId, 5, "Att4");

                IRelationCollection relations = new RelationCollection();
                relations.Add(ProductTypeOptionAttributeCombinationEntity. Relations.ProductTypeOptionAttributeCombinationProfileEntityUsingProductTypeOptionAttributeCombinationId, JoinHint.Inner);

                DataTable dynamicList = new DataTable();
                TypedListDAO dao = new TypedListDAO();

                dao.GetMultiAsDataTable(fields, dynamicList, 0, new SortExpression(), filter, relations, false, new GroupByCollection(), null, 0, 0);

I've got several instaces of ProductTypeOptionAttributeId - each with its own alias. What I would like to do is now specify a value for each of these.

I've had a look through the forums and the manual and whilst the forums give lots of different ways of doing this with things like predicate buckets, I can't see a way of getting them to work with the above code. I've spent most if my Saturday night trying

Hopefully you'll have a great code snippett up your sleeve for me

Many thanks

Darren (going for more coffee!)

Hi Darren,

If I understand correctly you have problems with the filter, however I don't see the code for your filter.

I don't know whether you want to filter the resulset based on _ProductTypeOptionAttributeId _predicates (which doesn't make sense, because all your _ProductTypeOptionAttributeId _fields would have the same value, wouldn't?) OR if you want to put constant values inside those fields.

Maybe the desirable SQL code could be helpful here

(Edit) Never mind... I just saw your SQL code at your first post I'm working in your snippet... (going for coffee!)

You have to add as many relations as your query have. Also you have to specify the object alias when you define the fields and the relations.

Here is an untested code:

ResultsetFields fields = new ResultsetFields(6);
fields.DefineField(ProductTypeOptionAttributeCombinationFields.CombinationId, 0);
fields.DefineField(ProductTypeOptionAttributeCombinationFields.ProductId, 1);
fields.DefineField(ProductTypeOptionAttributeCombinationProfileFields.ProductTypeOptionAttributeId, 2, "Att1", "ProfileValue1");
fields.DefineField(ProductTypeOptionAttributeCombinationProfileFields.ProductTypeOptionAttributeId, 3, "Att2", "ProfileValue2");
fields.DefineField(ProductTypeOptionAttributeCombinationProfileFields.ProductTypeOptionAttributeId, 4, "Att3", "ProfileValue3");
fields.DefineField(ProductTypeOptionAttributeCombinationProfileFields.ProductTypeOptionAttributeId, 5, "Att4", "ProfileValue4");

IRelationCollection relations = new RelationCollection();
relations.Add(
    ProductTypeOptionAttributeCombinationEntity.Relations. ProductTypeOptionAttributeCombinationProfileEntityUsingProductTypeOptionAttributeCombinationId, 
    "ProfileValue1",
    JoinHint.Inner);

relations.Add(
    ProductTypeOptionAttributeCombinationEntity.Relations. ProductTypeOptionAttributeCombinationProfileEntityUsingProductTypeOptionAttributeCombinationId, 
    "ProfileValue2",
    JoinHint.Inner);

relations.Add(
    ProductTypeOptionAttributeCombinationEntity.Relations. ProductTypeOptionAttributeCombinationProfileEntityUsingProductTypeOptionAttributeCombinationId, 
    "ProfileValue3",
    JoinHint.Inner);

relations.Add(
    ProductTypeOptionAttributeCombinationEntity.Relations. ProductTypeOptionAttributeCombinationProfileEntityUsingProductTypeOptionAttributeCombinationId, 
    "ProfileValue4",
    JoinHint.Inner);

IPredicateExpression filter = new PredicateExpression();
filter.Add(new FieldCompareValuePredicate(fields[2], ComparisonOperator.Equal, 40));
filter.Add(new FieldCompareValuePredicate(fields[3], ComparisonOperator.Equal, 3));
filter.Add(new FieldCompareValuePredicate(fields[4], ComparisonOperator.Equal, 116));
filter.Add(new FieldCompareValuePredicate(fields[5], ComparisonOperator.Equal, 143));

DataTable dynamicList = new DataTable();
TypedListDAO dao = new TypedListDAO();

dao.GetMultiAsDataTable(fields, dynamicList, 0, new SortExpression(), filter, relations, false, new GroupByCollection(), null, 0, 0);

Let me know if everything work as expected

Hi David

The code 'looks' correct - leave this with me, and I will give it a try later on today.

I want to check the values for each of the ProductTypeOptionAttributeCombinationProfileFields.ProductTypeOptionAttributeId

because if my Product has the values of 75, 78, 29, 42, through the FKs I can tell that this is an XL Red Mens Shirt for example...

Leave this with me and I will give it a try today.

Thanks

Darren