Entity framework Querying

Question

Hello Everyone, I am trying to figure out how to query my entities based on the following situation.

I have two tables, LKCATEGORY AND LKSUBCATEGORY as you can imagine this two tables are related to each other. However when i come to a page that needs to display only active categories and subcategories how do i do this and pull back a list of LKCATEGORY with the LKSUBCATEGORY included in the reference with both of these filters in place(isActive = true on both entities).

My Repository call that pulls back a list of type LKCATEGORY looks like:

        //Create the Entity object
        List<LKCATEGORY> category = null;

            using (SOAE strikeOffContext = new SOAE())
            {
                //Invoke the query
                category = AdminDelegates.selectCategory.Invoke(strikeOffContext).ByActive(true).ToList();
            }

While the Delegate(selectCategory) looks like:

        public static Func<SOAE, IQueryable<LKCATEGORY>> selectCategory =
    CompiledQuery.Compile<SOAE, IQueryable<LKCATEGORY>>(
    (category) => from c in category.LKCATEGORY.Include("LKSUBCATEGORY")
                select c);

and finally the Filter(ByActive)

    public static IQueryable<LKCATEGORY> ByActive(this IQueryable<LKCATEGORY> qry, bool active)
    {
        //Return the filtered IQueryable object
        return from c in qry
               where c.ACTIVE == active
               select c;

    }

I realize i only have the filter on the LKCATEGORY entity and of course i would only be filtering that table. My question is how do i filter both LKCATEGORY AND LKSUBCATEGORY and pull back a LIST of type LKCATEGORY with LKSUBCATEGORY included and filtered?

Thanks in advance, Billy

Answer 1

This may not be your solution, but you can divide this into two queries and try this (I assume this is first operation on LKCATEGORY in context):

var categories = context.LKCATEGORY.Where(c => c.ACTIVE).ToList();
var subcategories = context.LKSUBCATEGORY.Where(c => c.ACTIVE).ToList();
return categories;

Now look at categories.LKSUBCATEGORY list and you'll see that you have only active ones. Other solution involves using linq join instead of Include.

EDIT

Alternative way:

context.LKCATEGORY.Where(c => c.ACTIVE).Select(c => new { Category = c, Subcategory = c.LKSUBCATEGORY.Where(sc => sc.ACTIVE) }).ToList().Select(a => a.Category);