Hi, i'm having trouble creating a list based on data from a view. I can create the list with no issues, however when i attempt to filter the result (with a predicateexpression) i get errors.

The following code fails at the call to DataAccessAdapter.FetchTypedList with SqlException

The multi-part identifier "CRIExtract.dbo.vClaims.ClientId" could not be bound.

If line two (a filter against the clientid) is removed/commented-out the code runs fine.

I had a look at what was being generated and discovered that if I replace

[CRIExtract].[dbo].[vClaims].[ClientId] = @ClientId1

with

[vClaims].[ClientId] = @ClientId1

The sql executes successfully.

public DataTable LoadClaimReport(int entityId, string groupBy)
    {
        RelationPredicateBucket filter = new RelationPredicateBucket();
        filter.PredicateExpression.Add(VClaimsFields.ClientId == entityId);

        VClaimsFieldIndex fieldEnum = (VClaimsFieldIndex)Enum.Parse(typeof(VClaimsFieldIndex), groupBy);
        EntityField2 groupByField = (EntityField2)EntityFieldFactory.Create(fieldEnum);

        ResultsetFields fields = new ResultsetFields(3);
        //fields.DefineField(VClaimsFields.ClientId, 0, "ClientId", "vClaims");
        fields.DefineField(VClaimsFields.Period, 0, "Period", "vClaims");
        fields.DefineField(groupByField, 1, groupBy, "vClaims");
        fields.DefineField(VClaimsFields.Incurred, 2, "IncurredSum", "vClaims", AggregateFunction.Sum);
        
        IGroupByCollection groupByClause = new GroupByCollection();
        groupByClause.Add(fields[0]);
        groupByClause.Add(fields[1]);

        SortExpression exp = new SortExpression(VClaimsFields.Period | SortOperator.Descending);

        DataTable tlist = new DataTable();

        DataAccessAdapter adapter = new DataAccessAdapter();
        adapter.FetchTypedList(fields, tlist, filter, 0, exp, true, groupByClause);
        adapter.CloseConnection();

        return tlist;
    }

What am I doing wrong? All the examples in the docs seem to add a relationship object in to the Relations collection of the PredicateBucket. I dont have any relations to add though...

I added the following line below line 2

((FieldCompareValuePredicate)filter.PredicateExpression[0].Contents).ObjectAlias = "vClaims";

And this seems to have fixed the problem... I dont really understand what I've done though... and it's kind of annoying having to cast things. Do I need to do this? What am I doing?

Normaly when a SQL query is formulated in LLBLGen Pro the full qualifier name of the tables and fields is used such as: [CRIExtract].[dbo].[vClaims].[ClientId]

Unless you use an Alias for the table, which you have used in the ResultSet fields definitions. (the last parameter "vClaims"). Then you need to use this alias in the Where Clause too.

You only have to use it if join on the same table twice and you want to differentiate between them, so you don't realy have to use it in your situation.

If you haven't used this alias, you wouldn't need the Alias in the Where Clause (predicate expression).

Similar thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=4856

(edit) If yhou want to use the Alias, you could do one of the following:

EntityField2 ClientID = (EntityField2)VClaimsFields.ClientId;
// or: EntityField2 ClientID = EntityFieldFactory.Create(VClaimsFieldIndex.ClientId);
//if you don't like the casting :)

ClientID.ObjectAlias = "vClaims";
filter.PredicateExpression.Add(ClientID == entityId);

OR

filter.PredicateExpression.Add(new FieldCompareValuePredicate(VClaimsFields.ClientId, null, ComparisonOperator.Equal, entityId, "vClaims"));
//instead of using: filter.PredicateExpression.Add(VClaimsFields.ClientId == entityId);

Ok thanks Walaa. Its all making sense now