Linq + Entity Framework Select Only Certain Child Records

Question

I am trying to achieve something with Linq and Entity Framework. What I am trying to achieve is exactly the same as this question:

LINQ: dot notation equivalent for JOIN

In my case though I want to load a customer and related invoices but only certain invoices, not all of them.

I have tried to implement the given answer in the above link but my invoices come out as null.

    public IEnumerable<Customer> GetAllCustomers()
    {
        List<Customer> customers;

        using (var context = new EfContext())
        {
            customers = context.Customers.Join
                (
                    context.Invoices,
                    c => c.Id,
                    i => i.CustomerId, (c, i) => new { c, i }
                ).Where(z => z.i.Id > 3)
                .Select(z => z.c).ToList();
        }

        return customers;
    }

Schema

This seems to work, just need to double check it:

    public Customer GetCustomer(int id)
    {
        Customer customer;

        using (var context = new EfContext())
        {
            customer = context.Customers.Where(c => c.Id == id)
                .Select(c => new
                {
                    c,
                    Invoices = c.Invoices.Where(i => i.Id > 0 && i.Lines.Any(li => li.Item == "Bacon"))
                    .ToList().Select(z => new { z, Lines = z.Lines.Where(l => l.Item == "Bacon")}).ToList()
                })
                .ToList()
                .Select(x => x.c)
                .FirstOrDefault();
        }

        return customer;
    }

The invoices are not selected in the final query result. What you're trying is [this](http://stackoverflow.com/a/16801205/861716). — Gert Arnold, May 11 '14 at 08:31
Kundan Singh Chouhan - Yes the relationship is there - see the image of my models and schema. — Dave Amour, May 11 '14 at 12:17
Gert Arnold - the link you posted looks worth a try. Let me try this out and get back to you... — Dave Amour, May 12 '14 at 04:58
Gert Arnold - this seems to do what I want and with just one database hit - this code seems to work - does it look ok? I added code to my original post. — Dave Amour, May 12 '14 at 05:19
Yes, that's it, except that you don't need these `ToList` calls in the first `Select` and the last one can be replaced by `AsEnumerable` (I edited my original answer accordingly). — Gert Arnold, May 12 '14 at 07:31
Gert Arnold - I want to accept yours as an answer but I don't use SO much - how do I do that? — Dave Amour, May 13 '14 at 04:38
The procedure is to mark your question as a duplicate. This is not a bad thing. You question serves as a signpost to an accepted answer and this prevents nearly equal answers to get scattered all over the place. You can do that through the "close" link. When enough people do this your question will be accepted as duplicate. — Gert Arnold, May 13 '14 at 08:24

score 0 · Answer 1 · answered May 11 '14 at 05:49

0

This is probably related to entity "load" issues.
When you use entity framework, you can configure it to load all of your related entities on each query(e.g. when you query customers and find a related invoice then bring it as well, and you can tell him to do that explicitly if you don't want him to do it by default).

Check this article out and decide if you need eager\lazy\explicit loading:

answered May 11 '14 at 05:49

Amir Popovich

29,350
9
53
99

On that article, the section titled "Applying filters when explicitly loading related entities" may do what I need so let me check that out... – Dave Amour May 11 '14 at 12:22
Nothing in that link helps much I'm afraid. I do already know about loading related entities but what normally happens is that ALL related entities are loaded. What I need though is things like this: 1. Load a customer and only those invoices for a certain date 2. Load a customer and only those invoices for a certain date and also only load line items which are for product X I also need to do the above for either a single customer or a collection of customers. – Dave Amour May 12 '14 at 04:52
The filter example on the link posted actually makes 2 database calls which is no good and also it only works for a single entity and also doesn't work for nested relationships. – Dave Amour May 12 '14 at 04:55

Linq + Entity Framework Select Only Certain Child Records

1 Answers1