Prev: line break in link button's text
Next: Different use
From: Harlan Messinger on 28 Mar 2010 22:05 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?
From: Andy O'Neill on 29 Mar 2010 05:34 "Harlan Messinger" <h.usenetremoverthis(a)gavelcade.com> wrote in message news:81ag79FliiU1(a)mid.individual.net... > 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.
From: Harlan Messinger on 29 Mar 2010 06:44 Harlan Messinger wrote: > 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.)
From: Harlan Messinger on 29 Mar 2010 06:54 Andy O'Neill wrote: > > "Harlan Messinger" <h.usenetremoverthis(a)gavelcade.com> wrote in message > news:81ag79FliiU1(a)mid.individual.net... >> 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. > 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?
From: Harlan Messinger on 29 Mar 2010 07:49
Harlan Messinger wrote: > 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(); } |