Nested ListView

[Harlan Messinger]

I've got an Item table and a Category table. The Category table has
columns categoryId and categoryName. The Item table has a categoryId
that is a foreign key into Category, and a column called typeId.

I've created a DataContext for my database. It generally works as expected.

I want to display the items grouped by category, with group headings. So
I created a LinqDataSource based on my data context to provide the
Category data, and created a ListView that uses it as a source and it
displays my category names.

In the ItemTemplate, under the category name, I have

<asp:ListView ID="listviewItem" runat="server"
DataSource='<%#Eval("Items") %>'>

This works too. I have configured this inner ListView to list data from
the items, and they care correctly listed under the appropriate categories.

HOWEVER: what do I do if I want to list only the Items having typeId=3?

 

[Andy O'Neill]

I've got an Item table and a Category table. The Category table has
columns categoryId and categoryName. The Item table has a categoryId that
is a foreign key into Category, and a column called typeId.

I've created a DataContext for my database. It generally works as
expected.

I want to display the items grouped by category, with group headings. So I
created a LinqDataSource based on my data context to provide the Category
data, and created a ListView that uses it as a source and it displays my
category names.

In the ItemTemplate, under the category name, I have

<asp:ListView ID="listviewItem" runat="server"
DataSource='<%#Eval("Items") %>'>

This works too. I have configured this inner ListView to list data from
the items, and they care correctly listed under the appropriate
categories.

HOWEVER: what do I do if I want to list only the Items having typeId=3?

One interesting aspect about linq is that it doesn't generate any sql until
you ask it to iterate the result.
What's that mean?
That allows you to use something called the pipeline filter pattern.
You can take one linq thingummy and add a criteria onto it.
Using that pattern might be overkill in this instance but would allow you to
eaily cope with further requests to refine the data.
It's a great pattern for user configurable filtering on screens or reports.

 

[Harlan Messinger]

I've got an Item table and a Category table. The Category table has
columns categoryId and categoryName. The Item table has a categoryId
that is a foreign key into Category, and a column called typeId.

I've created a DataContext for my database. It generally works as expected.

I want to display the items grouped by category, with group headings. So
I created a LinqDataSource based on my data context to provide the
Category data, and created a ListView that uses it as a source and it
displays my category names.

In the ItemTemplate, under the category name, I have

<asp:ListView ID="listviewItem" runat="server"
DataSource='<%#Eval("Items") %>'>

This works too. I have configured this inner ListView to list data from
the items, and they care correctly listed under the appropriate categories.

HOWEVER: what do I do if I want to list only the Items having typeId=3?

After three hours of horsing around I figured it out. First:

<%@ Import Namespace="System.Collections.Generic" %>
<%@ Import Namespace="System.Linq" %>
<%@ Import Namespace="[the namespace containing the context class]" %>

Then:

<asp:ListView
ID="listviewItem"
runat="server"
DataSource='<%# ((IEnumerable<Item>) Eval("Items")).Where(item =>
item.TypeId == 3) %>'>

(I had included the Items property among the properties selected for
inclusion in the outer Category ListView.)

 

[Harlan Messinger]

One interesting aspect about linq is that it doesn't generate any sql
until you ask it to iterate the result.
What's that mean?
That allows you to use something called the pipeline filter pattern.
You can take one linq thingummy and add a criteria onto it.
Using that pattern might be overkill in this instance but would allow
you to eaily cope with further requests to refine the data.
It's a great pattern for user configurable filtering on screens or reports.

Before seeing your reply I sent a follow-up to my note where I show a
solution I came up with. I think it's the kind of thing you're referring
to, so thanks for the reinforcement.

However, I've come up with another puzzler: in the outer ListView, I
have categories that aren't associated with any of the items in my data,
so I don't want them to display. In SQL I'd use "select distinct
Category.categoryId, Category.categoryName from Item inner join Category
...." or "select categoryId, categoryName from Category where exists ..."
but I haven't figure out how to accomplish that with the
LinqDataSource/ListView combination. Any thoughts?

 

[Harlan Messinger]

However, I've come up with another puzzler: in the outer ListView, I
have categories that aren't associated with any of the items in my data,
so I don't want them to display. In SQL I'd use "select distinct
Category.categoryId, Category.categoryName from Item inner join Category
..." or "select categoryId, categoryName from Category where exists ..."
but I haven't figure out how to accomplish that with the
LinqDataSource/ListView combination. Any thoughts?

Holy cow, I figured that one out too (after three more hours of
experimentation). You don't even need a TableName attribute in the
LinqDataSource tag. Create a handler for the Selecting event:

protected void linqsrcCategory_Selecting(object sender,
LinqDataSourceSelectEventArgs e)
{
AuctionData context = new AuctionData();
e.Result = (from item in context.Items
where item.TypeId == 3
select item.Category)
.Distinct()
.OrderBy(category => category.CategoryId)
.ToList();
}

 

[Andy O'Neill]

Andy O'Neill wrote:

Before seeing your reply I sent a follow-up to my note where I show a
solution I came up with. I think it's the kind of thing you're referring
to, so thanks for the reinforcement.

That sort of solution will of course only cope with the one parameter.
The pipeline filter thing allows a list of different filters to be applied.
May be of no interest to you right now, but when they come back and want you
to filter on any combination of 5 different options - that's the thing to
look into.

 

[Harlan Messinger]

That sort of solution will of course only cope with the one parameter.
The pipeline filter thing allows a list of different filters to be applied.
May be of no interest to you right now, but when they come back and want
you to filter on any combination of 5 different options - that's the
thing to look into.

Google didn't turn up anything substantive on "pipeline filter" in
conjunction with LINQ. Is that the usual name for it?