select first child of entity

[Issam]

hi,

let's say that i have a customers and invoices tables

what i want is to select all the customers with their last invoice based on descending date



thanks and good day

[Boyan]

Hello Issam,

Lets assume you have the following set up - tables named Customers and Invoices with one-to-many relationship between them. Additionally the Invoice table has a date column.
In order to select all customers along with the newest invoice you could use the following LINQ query:

var customers = context.Customers.Include(c => c.Invoices).Select(c => new
{
    Customer = c,
    LatestInvoice = c.Invoices.OrderByDescending(i=>i.Date).FirstOrDefault()
});

Please note that the above is not an optimal solution as the Include() method will load all invoices as well but could be easily applied if you do not expect a large number of invoices. On the other hand you could omit the Include() method resulting in a query execution per customer which could be still applicable in case of a small amount of customers for example if customers are displayed in small groups (pages). 

If both of the above solutions does not meet your requirements, you could make native SQL query or stored procedure in order to retrieve just the right data from the database in a single query. If you are interested in such solution we would be happy to assist you.

Please let us know if you have any further questions.

Regards,
Boyan
Telerik

[Issam]

Thanks Boyan,

it worked just fine !,
in my form i have 2 panels, a search and a detail one,
in the the search i have a listbox binded to a minimal patients informations

yes actually it's a medical app,
and when the user select a patient the detail panel shows all the details of the selected one with a new query that look like the one you showed me, and afect the datacontext of the detail panel with

so at any time the de details (includes) are fetched from  database for a single patient

now this seem to work  with this scenario, and i am interested on your opinion about this configuration

thanks again

[Boyan]

Hi Issam,

From the additional information you have provided it seems that the most appropriate solution for you would be to populate your search listbox with one query, for examples:

var customers = context.Customers.Where(x=>x.FirstName == firstName);

Once you have the list of customers that satisfy a criteria and would like to display the details panel with additional information, you could create a second query selecting the latest invoice for a customer (querying by the customer's primary key) like so:
 

var latestInvoice = customers.First(x=>x.Id == selectedCustomerId).Invoices.FirstOrDefault();

Writing your queries this way (avoiding projection in anonymous types)  would help you minimize the database queries and traffic.
In case you have a desktop application and have a read-only search-details form (no transactions are expected) it would be feasible to use a single context on that page this way taking further advantage of the Telerik Data Access Level One Cache. On the other hand if you have a web application and your context is disposed per request it is possible to enable the Telerik Data Access Second Level Cache gaining additional performance boast. More information on how to work with the cache and what benefits it provides could be found in this documentation article. 

I hope this helps you. Do not hesitate to contact us with any further questions. 

Regards,
Boyan
Telerik