Linq to Entities - Filter sub table

I have two tables in my Edmx, Categories and Items. The items table has a "Visible" bool field.

I want to return the categories table with the attached Items table with only the records where Visible = True.

Can you give me an example of how to only return the items where Visible = True?

I have tried the following which hasn't worked

var categoryList = from cl in db.Categories where cl.Items.Any(item => item .Visible == true) select cl

Thanks

Mark

SQL Query is:

ADO.NET:Execute Reader "SELECT [Extent1].[ID] AS [ID], [Extent1].[Category_Name] AS [Category_Name], [Extent1].[CategoryNote] AS [CategoryNote], [Extent1].[CategoryOrder] AS [CategoryOrder] FROM [dbo].[Categories] AS [Extent1] WHERE EXISTS (SELEC开发者_如何学JAVAT 1 AS [C1] FROM [dbo].[Items] AS [Extent2] WHERE ([Extent1].[ID] = [Extent2].[CategoryID]) AND ([Extent2].[Visible] IS NOT NULL) AND ([Extent2].[Visible] = 1) )" The command text "SELECT [Extent1].[ID] AS [ID], [Extent1].[Category_Name] AS [Category_Name], [Extent1].[CategoryNote] AS [CategoryNote], [Extent1].[CategoryOrder] AS [CategoryOrder] FROM [dbo].[Categories] AS [Extent1] WHERE EXISTS (SELECT 1 AS [C1] FROM [dbo].[Items] AS [Extent2] WHERE ([Extent1].[ID] = [Extent2].[CategoryID]) AND ([Extent2].[Visible] IS NOT NULL) AND ([Extent2].[Visible] = 1) )" was executed on connection

you code should work!!

try the following code:

db.Categories.Where(c => c.Items.Any(i => (bool)i.Visible)).Select(c => c)

you run the SQL Profiler / IntelliTrace to check the generated code.

EDIT: I modified the code EDIT2: Check this code ( I reversed the query)

db.Items.Where(i => i.Visible != null && (bool)i.Visible).SelectMany(i => i.Categories);