>Hi all just playing around with LINQ and EF and need some help with what I think is a pretty simple query
>
>

>
>int InvoiceAmount = 1000000;
>
>var results =
>                 from cl in Context.clients.ToList().Distinct()
>                 from inv in cl.invoices.ToList().Where(x => x.invoiceamount >= InvoiceAmount)
>                 where cl.id == inv.clientid
>                 select cl;
>

>
>clients and invoices are the two underlying tables and are joined on id->clientid fields, where one client has one or more invoices, in my attempt shown above I thought I would only get invoices ( invoices is a navigational property of client ) with a value >= InvoiceAmount but I get all that are linked to a client. I think it must be down to LINQ's deferred execution but don't know how to work around it except by filtering the invoice list again which is not ideal
>
>

>int i = 0;
>                foreach (client c in results)
>                {
>                     i++;
>                     Console.WriteLine("Company: {0} (id: {1})", c.companyname, c.id);
>
>                     foreach (invoice inv in c.invoices) // This is where I think the query selects all invoices related to the current client.
>                     {
>                         Console.WriteLine("\t Invoice reference: {0}", inv.invoicereference);
>                         Console.WriteLine("\t\t Invoice date: {0}", inv.invoicedate.Value.ToString("dd/MM/yyyy"));
>                         Console.WriteLine("\t\t Invoice amount: {0}", inv.invoiceamount.Value.ToString("C"));
>                         Console.WriteLine("\t\t Charge type: {0}", inv.chargetypes.chargetypename);
>                     }
>

>

You've got a couple of things wrong here. First, don't call ToList on each entity collection when you are doing your query. This causes it to bring over all of the clients and all of the invoices for those clients immediately. If you leave off the ToList, EF will generate an appropriate query for you and only grab the records you need. In general, ToList, ToDictionary, ToArray or any other function that causes the query to be run should be left as the last step so that EF can generate a query that only grabs what it needs.

Second, you don't need the invoices as a separate list/join in your query. Your navigation property can accomplish the same thing:

int InvoiceAmount = 1000000;

var results =
                 from cl in Context.clients.ToList().Distinct()
                 where cl.invoices.Any(inv => inv.invoiceamount >= InvoiceAmount)
                 select cl;

Third, your query only selects the client. The client's invoices navigation property will always include all of the invoices for the client as it doesn't know anything about your previous query. You need to filter it when using it:

int i = 0;
                foreach (client c in results)
                {
                     i++;
                     Console.WriteLine("Company: {0} (id: {1})", c.companyname, c.id);

                     foreach (invoice inv in c.invoices.Where(x => x.invoiceamount >= InvoiceAmount).ToList()) 
                     {
                         Console.WriteLine("\t Invoice reference: {0}", inv.invoicereference);
                         Console.WriteLine("\t\t Invoice date: {0}", inv.invoicedate.Value.ToString("dd/MM/yyyy"));
                         Console.WriteLine("\t\t Invoice amount: {0}", inv.invoiceamount.Value.ToString("C"));
                         Console.WriteLine("\t\t Charge type: {0}", inv.chargetypes.chargetypename);
                     }

I would change the whole thing to the following:

int InvoiceAmount = 1000000;

var results = Context.invoices // Start at invoices because this is the list we really care about
	.Include(inv => inv.client) // Eager load the client so it doesn't require additional queries
	.Where(inv => inv.invoiceamount >= InvoiceAmount) // Filter results
	.GroupBy(inv => inv.client) // Group results by client so we can loop through the clients
	.ToList(); // Execute the query

foreach (var invGroup in results)
{
	// invGroup is an IGrouping<client, invoice>. See http://msdn.microsoft.com/en-us/library/vstudio/bb344977%28v=vs.110%29.aspx
	Console.WriteLine("Company: {0} (id: {1})", invGroup.Key.companyname, invGroup.Key.id);

	foreach (invoice inv in invGroup) 
	{
		Console.WriteLine("\t Invoice reference: {0}", inv.invoicereference);
		Console.WriteLine("\t\t Invoice date: {0}", inv.invoicedate.Value.ToString("dd/MM/yyyy"));
		Console.WriteLine("\t\t Invoice amount: {0}", inv.invoiceamount.Value.ToString("C"));
		Console.WriteLine("\t\t Charge type: {0}", inv.chargetypes.chargetypename);
	}
}