0

I have a simple requirement if done via sql query. I have table A which is category with ID,Name table b which is categoryItems which has foreign key of A CategoryId,Name. I am using linqdatasource with simple select statements to show data from table A.

select * from A (simple)

I have a requirement now where i want to "not show" categories in first place who do not have any items associated with them i. e (select count(*) from B where CategoryId="")>0

Very easy by modifying sql statement, was wondering if it can be done by possible accessing foreign key relationship data using any out of box linq functionality and applying the validation.

Just keen on it!..

Thanks a ton!

Mandar Jogalekar
  • 3,199
  • 7
  • 44
  • 85

2 Answers2

2

You want to use the Any() method on Category.CategoryItems

Any() returns true if the Count > 0 and false if the Count == 0

// Select only the Categories which have at least one CategoryItem.
IEnumerable<Category> categoriesWithItems = Context.Categories.Select(x => x.CategoryItems.Any());

For linqdatasource you want to use the Selecting event handler. MSDN.

The aspx:-

<asp:LinqDataSource ID="LinqDataSource1"
                runat="server"  
                ContextTypeName="MyDataContext"
                OnSelecting="LinqDataSource1_Selecting">
</asp:LinqDataSource>

The method:-

public void LinqDataSource1_Selecting(object sender, LinqDataSourceSelectEventArgs e)
{ 
    e.Result = categoriesWithItems = Context.Categories.Select(x => x.CategoryItems.Any());    
}
Sam Leach
  • 12,746
  • 9
  • 45
  • 73
0

You could try this

var categories = (from c in category select c).Select<Category>(x => x.categoryItems.Count() > 0);

This relies on your objects and will do the trick just fine.

Alternatively you can also use Where and more.

See this link for a good explanation on Linq operators and extenders

Tikkes
  • 4,599
  • 4
  • 36
  • 62