I have two tables, let's call them X and Y, which have a 1:m relationship. I am binding table X to a gridview and want to include information about table Y, specifically how many rows in table Y with a particular attribute are related to the current row in table X. So let's say X is parent and Y is children and I want to have the number of minor kids and the number of adult kids listed in my table, so it'd look something like this:

Parent Name # minor kids # adult kids James 1 2 Cecelia 2 0 ...

How would I do this using LLBLGen?

Thanks.

Laurie

Hi Laurie,

You could use a DynamicList and apply a ScalarQueryExpression for the count fields.

Take a look at some examples: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=10808&StartAtMessage=0&#60266 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=11244

Well, let's flesh this out a little more because your solution doesn't seem to fit the way I'm going things. What I'm doing is using a Parents collection to populate the datagrid. My code goes something like this:

ParentCollection myParents = new ParentCollection();
IPredicateExpression Filter = new PredicateExpression();
Filter.Add(ParentFields.Gender == 1); // 1=Male
sortExpression sorter = new SortExpression(ParentFields.LastName | SortOperator.Ascending);
MyParents.GetMulti(Filter, 0, sorter);

gvMyParents.DataSource = myParents;
gvMyParents.DataBind();

and the Gridview looks something like this:

<asp:GridView ID="gvMyParents" runat="server" AutoGenerateColumns="False">
        <RowStyle VerticalAlign="Top" />
        <Columns>
            <asp:TemplateField>
                <ItemTemplate>
                    <b><asp:Label ID="lLastName" runat="server" Text='<%# Bind("LastName") %>'></asp:Label>, <asp:Label ID="lFirstName" runat="server" Text='<%# Bind("FirstName") %>'></asp:Label></b><br />
                    <asp:Label ID="lAddress" runat="server" Text='<%# Bind("Address") %>'></asp:Label><br />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField><ItemTemplate>X Adult Children</ItemTemplate></asp:TemplateField>
            <asp:TemplateField><ItemTemplate>Y Minor Children</ItemTemplate></asp:TemplateField>
        </Columns>
    </asp:GridView>

I want to be able to replace the X with the number of adult children and the Y with the number of minor children, which values are calculated by SUMming the number of such children in the Children table.

I was thinking there would be an easy way to add properties to the ParentEntity class that would contain/calculate the summed values, but don't know how to add it or how to reference it in my GridView. (Alternately, I could create a Typed View or a Typed List that would contain the values, but I kinda wanted to do it in my C# code rather than in SQL.)

Any ideas?

Thanks!

Laurie

Hi Lauri,

Ok. I suggested DynamicList because it would make a little more efficient the query (you just retrieve the count of children, no the children per-se).

Now, if you want to use EntityCollections you have two options:

A. Write a simple property on your ParentEntity. You can do this in a partial class or on the CUSTOM_USER_CODE_REGIONS on the ParentEnttiy.cs file. The property could look like:

public int NumerOfAdults
{
     get
     {
          return this.Children.Count;
     }
}

Please note that this property only count the already objects in your parent. That means that, if you are Adapter you must prefetch the Children right? If you are using SelfServicing this would work without prefethpath but in this case I would recommend you use them for performance.

If you want to filter the count and you are using prefetchpaths (needed in the case of Adapter) you can actually filter the prefetchPath, so you wont filter in-memory.

Now, it could be that you don't want to filter on prefetchpaths because you need different filters depending on the property (a property that shows adults, other that shows kids). You can use EntityViews to filter the children collection. For example (assuming you are using Adapter, but it pretty the same for SelfServicing):

public int NumerOfAdults
{
     get
     {
          EntityView2<ChildEntity> childrenView = new EntityView2<ChildEntity>(this.Children);
          IPredicate filter = (ChildFields.Age > 21);
          childrenView.Filter = filter;
          return childrenView.Count;
     }
}

The same if for other properties you want to create. If you are using .NET3.5 you can simplify the in-memory filter. Now you can use those properties on your databinding control.

B. The other option is that you don't need to fetch the children, you just create additional fields expression that query the counts (similar to DynamicList, but working on EntityCollections). For more information please take a look at this article (Step 6): http://weblogs.asp.net/fbouma/archive/2006/06/09/LLBLGen-Pro-v2.0-with-ASP.NET-2.0.aspx

Hope helpful

Thanks. Since I'm using self servicing, I had to use EntityView instead of EntityView2, but it's working smashingly.