Hello,

I'm trying to add a property 'NumberOfOrders' to the entityfactory of Customer (Northwind). I added the following to 'Public Class CustomerEntityFactory'

Public Overridable Overloads Function Create(fields As IEntityFields) As IEntity Implements IEntityFactory.Create
            Dim toReturn As IEntity = Create()
            toReturn.Fields = fields
            
            ' __LLBLGENPRO_USER_CODE_REGION_START CreateNewCustomerUsingFields
            Dim scalarField As EntityField
            toReturn.Fields.Expand(1)
            scalarField = New EntityField("NumberOfOrders", New ScalarQueryExpression        (OrderFields.OrderId.SetAggregateFunction(AggregateFunction.Count), (CustomerFields.CustomerId = OrderFields.CustomerId)))
            toReturn.Fields.DefineField(scalarField, toReturn.Fields.Count - 1)
            ' __LLBLGENPRO_USER_CODE_REGION_END     
            Return toReturn
        End Function

When binding this entitycollection I do get the option to choose NumberOfOrders as a column for my grid but it doesn't get filled with data. I must be forgetting something....

I adapted this from code from Frans' BLOG and trying to implement it using selfservicing and VB.NET. The project uses LLBLGen V2 and VS 2005.

Regards,

Willem Jan

Does the query contain the subquery in the select list? (enable DQE tracing to see what query is generated)

Well, I think it doesnt't generate the subquery. I started the tracing and this part is from the output window. This query fetched the customer data which should also include the subquery.

Generated Sql query: Query: SELECT [Northwind].[dbo].[Customers].[CustomerID] AS [CustomerId], [Northwind].[dbo].[Customers].[CompanyName], [Northwind].[dbo].[Customers].[ContactName], [Northwind].[dbo].[Customers].[ContactTitle], [Northwind].[dbo].[Customers].[Address], [Northwind].[dbo].[Customers].[City], [Northwind].[dbo].[Customers].[Region], [Northwind].[dbo].[Customers].[PostalCode], [Northwind].[dbo].[Customers].[Country], [Northwind].[dbo].[Customers].[Phone], [Northwind].[dbo].[Customers].[Fax] FROM [Northwind].[dbo].[Customers] WHERE ( ( ( [Northwind].[dbo].[Customers].[Country] = @Country1))) ORDER BY [Northwind].[dbo].[Customers].[CompanyName] ASC

Parameter: @Country1 : String. Length: 15. Precision: 0. Scale: 0. Direction: Input. Value: "USA".

I set the sort order and a fixed parameter for Country in code. This is where I expected the subquery. When looking down the trace output I see another query being generated. This generates the order records for each customer which is part of the prefetch path used. this set of data is shown in my grid.

So somehow I don't get the subquery as expected.

Willem Jan

You should override CreateFields in a new factory. That's the method I override as well in my factory.

I have created the ExtendedCustomerEntityFactory but where can I set in Selfservicing which factory to use?

I have in my LLBLgenProDatasource only the option to select an entitycollection and not a specific entityfactory.

Shortie wrote:

I have created the ExtendedCustomerEntityFactory but where can I set in Selfservicing which factory to use?

I have in my LLBLgenProDatasource only the option to select an entitycollection and not a specific entityfactory.

Create a derived class of CustomerCollection and add the factory there in the empty constructor: /// <summary> CTor</summary> public SpecialCustomerCollection():base(new ExtendedCustomerEntityFactory()) { }

If you don't use Design time databinding, so you don't need to design things in the asp.net editor, you can also set the collection in the code behind's page_load handler when the page isn't a postback. Then you can use the CustomerCollection normally, and set its EntityFactoryToUse property to an instance of ExtendedCustomerEntityFactory.

Well somehow this is complicated. I have changed my design-time binding to binding in code where I added the EntityFactoryToUse.

  Dim _colCustomers As New CollectionClasses.CustomerCollection
  _colCustomers.EntityFactoryToUse = New FactoryClasses.ExtendedCustomerEntityFactory
  _customerDS.EntityCollection = _ColCustomers

Now I do get the query to include the subquery for NumberOfOrders. Now it complains that the fields are not compatible. The Fields returned from the EntityFactory is 12, one more than the CustomerEntity as it is added by the ExtendedCustomerfactory.

I Added the partial class for a property NumberOfOrders to CustomerEntity. Are these properties mapped to the Fields?

Namespace LLBL_Northwind.EntityClasses

    Partial Public Class CustomerEntity

        ' Uitbreiding CustomerEntity mt de property NumberOfOrders
        Public ReadOnly Property [NumberOfOrders]() As Nullable(Of Integer)
            Get

                Dim value As Object
                value = Me.Fields(Me.Fields.Count - 1).CurrentValue
                If value Is Nothing Then
                    Return Nothing
                Else
                    Return Convert.ToInt32(value)
                End If

            End Get
        
        End Property

    End Class


End Namespace

My error is:

The EntityFields object specified has a different layout than expected

When looking into Autos window I see I have created 12 fields while my CustomerEntity only has 11 specified.

I must be close I guess but what am I missing here...

I'll write a testcase to see if I can repro it.

Ok, found it. You also have to override Create in the factory. This is because in selfservicing first the entity is created, then the fields object is replaced:

public class ExtendedCustomerEntityFactory : CustomerEntityFactory
{
    /// <summary>
    /// Create the fields for the customer entity and adjust the fields collection 
    /// with the entity field object which contains the scalar expression
    /// </summary>
    /// <returns></returns>
    public override IEntityFields CreateFields()
    {
        IEntityFields toReturn = base.CreateFields();
        AddScalarField(toReturn);
        return toReturn;
    }

    public override IEntity Create()
    {
        IEntity toReturn = base.Create();
        AddScalarField(toReturn.Fields);
        return toReturn;
    }


    private void AddScalarField(IEntityFields fields)
    {
        fields.Expand(1);
        IEntityField scalarField = new EntityField("NumberOfOrders",
                new ScalarQueryExpression(OrderFields.OrderId.SetAggregateFunction(
                    AggregateFunction.Count),
                (CustomerFields.CustomerId == OrderFields.CustomerId)));
        fields.DefineField(scalarField, fields.Count - 1);
    }
}

usage:

[Test]
public void CustomPropertyCustomerFetch()
{
    CustomerCollection customers = new CustomerCollection();
    customers.EntityFactoryToUse = new ExtendedCustomerEntityFactory();
    customers.GetMulti(CustomerFields.Country == "Germany");
    foreach(CustomerEntity customer in customers)
    {
        Assert.IsTrue((customer.NumberOfOrders.Value > 0));
    }
}

property:

public Nullable<int> NumberOfOrders
{
    // read the last field in the fieldslist, as we've added our scalar expression field to the end of the fields list.
    get
    {
        object value = this.Fields[this.Fields.Count - 1].CurrentValue;
        if(value != null)
        {
            return Convert.ToInt32(value);
        }
        else
        {
            return null;
        }
    }
}

Thanks, I've got it working now. I also had to add <Serializable()> _ to my extended class. This is truly a great piece of work!

Glad you're up and running, Shortie!