01 Querying from a WPF client in a 3 tiers application

Being able to query from the client is a very useful feature as far as you want to provide a flexible data centric 3 Tiers solution.

Our three tiers are the following:

• A Database
• An ASP.NET Web Application to host services
• A WPF client

Server

In the previous workshop, we downloaded the DB and created an ASP.NET Web Application with the edmx.

After installing WAQS vsix, you can right click on the project in the solution explorer / WAQS / Server generation.

Then click on Generate:

After many seconds, you have your server ready to expose your entities.

Client

Now let’s work on the client.

We want to have this: a list of customer with the aggregation of their company name and contact name and the total they spent, order by this total.

Add a new WPF project into the solution.
Then right click on this project / WAQS / WPF Client Generation

Then click on Generate:

After many seconds, you now are able to CRUD your entities.

You will create a View Model.
For this, add a new class in the client project.
Then, right click on the associated file it in the Solution Explorer / WAQS / Init view model.

Then select the MainWindow as associated view and click on Initialize.

Now you are ready to query your entities.

In MainWindow.xaml, use this xaml.

In your ViewModel, add this code:

private IEnumerable<CustomerInfo> customers;
public IEnumerable<CustomerInfo> Customers
{
    get
    {
        if (customers == null)
        {
            LoadCustomersAsync().ConfigureAwait(true);
        }
        return customers;
    }
    private set
    {
        customers = value;
        NotifyPropertyChanged.RaisePropertyChanged(nameof(Customers));
    }
}

private async Task LoadCustomersAsync()
{
    Customers = await (from c in _context.Customers.AsAsyncQueryable()
                       let totalSpent = c.Orders.Sum(o => o.OrderDetails.Sum(od => od.Quantity * od.UnitPrice * (1 - od.Discount)))
                       orderby totalSpent descending
                       select new CustomerInfo
                       {
                           Id = c.Id,
                           Name = c.CompanyName + " " + c.ContactName,
                           TotalSpent = totalSpent
                       }).ExecuteAsync();
}

public class CustomerInfo
{
    public string Id { get; set; }
    public string Name { get; set; }
    public double TotalSpent { get; set; }
}`

Note the AsAsyncQueryable and ExecuteAsync methods which are the ones used by WAQS to querying in the server and so in the database.

Then if you ran this code, you will have an exception.
This exception is not relative to WAQS but is a very frequent trap with LINA To Entities queries.
In fact, in SQL there is neither NullReferenceException nor navigation property. So, if a customer had no order, the TotalSpent value returned by the database would be null.

In order to avoid it you can use a nullable property or use this syntax:

private async Task LoadCustomersAsync()
{
    Customers = await (from c in _context.Customers.AsAsyncQueryable()
                       let totalSpent = c.Orders.Sum(o => o.OrderDetails.Sum(od => od.Quantity * od.UnitPrice * (1 - od.Discount)))
                       orderby totalSpent descending
                       select new CustomerInfo
                       {
                           Name = c.CompanyName + " " + c.ContactName,
                           TotalSpent = (double?)totalSpent ?? 0
                       }).ExecuteAsync();
}

Now, you can run it. You are able to querying our entities from the client.
BTW, note that it's faster with a SQL Server instance

At the end of this workshop, your solution should be the same than this one.