Entity framework Querying

Question

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

LukLed · Accepted Answer · 2010-02-17 07:15:21Z

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);

edited Feb 17, 2010 at 7:15

answered Feb 17, 2010 at 0:08

LukLed

31.9k17 gold badges87 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Billy Logan Over a year ago

Worked like a charm. I knew of another way to link the two with separate queries, but i like this one alot better. Thanks. If you have time please post your linq join example. Thanks Again! Billy

Collectives™ on Stack Overflow

Entity framework Querying

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related