Archive

Archive for June, 2010

Entity Framework Sorting by master then detail

As I get more and more comfortable with Entity Framework, I realize that there are some simple things within Linq2Sql that I absolutely miss. One of these features is the ability to Sort by a property in the underlying collection you are returning then by a property on a child collection as well.

This is easy with Linq2Sql, all I have to do is use the DataLoadOptions and embed a lambda expression that contains my order by expression. Once that is done, I can then pass the options the underlying LoadOptions property of the instance that I am working with and it is ready to go. This will provide me parent/child sorting capabilities.

There is another way that you can sort fairly easily in both Entity Framework and Linq2Sql but I personally don’t like it since most of my development uses Silverlight and WCF RIA Services. I like to get change tracking for free. This other way is to use projection when basically allows you to create a new anonymous type and return this new type with all of the sorting and flattening out if you need it. This approach is good for read-only screens or when you know that you need to denormalize your data across several entities and you are not concerned about losing the change tracking and have a mechanism for handling that yourself.

Now on to the way to get Parent/Child or Master/Detail sorting in the Entity Framework. Here is a sample Query that exposes a Customer object with its corresponding Order collection attached as well. The Customer object is also sorted by the CustomerName:

[Query()]
public IQueryable<Customer> ReturnCustomers()
{
    return this.ObjectContext.Customer
    .Include("Order")
    .OrderBy(x => x.CustomerName);

}

This satisfies the top level ordering but since the Order entity is a collection off of the Customer entity, I have no way to embed a Linq expression to force sorting the child record as well. I have tried using several different approaches but ultimately I believe this is a limitation with WCF RIA Services more than it is with Entity Framework.

I will now show you how I am facilitating the child detail level sorting on the client side of the application. First I am going to expose this newly created Query via a DomainDataSource. The following is a snippet of XAML showing all of the pieces working together:

<UserControl.Resources>
  <CollectionViewSource x:Name="cvsOrders" 
    Source="{Binding CurrentCustomer.Order}">
    <CollectionViewSource.SortDescriptions>
      <compMod:SortDescription PropertyName="OrderDate" 
        Direction="Ascending"/>
    </CollectionViewSource.SortDescriptions>
  </CollectionViewSource>
</UserControl.Resources>

<riaCtls:DomainDataSource x:Name="_ddsCustomers" 
    QueryName="ReturnCustomersQuery"
    DomainContext="{Binding DomainContext, Mode=TwoWay}">
</riaCtls:DomainDataSource>
<ListBox x:Name="lboCustomers"
    Grid.Column="0"
    ItemsSource="{Binding ElementName=_ddsCustomers, Path=Data}"
    SelectedItem="{Binding CurrentCustomer, Mode=TwoWay}">
</ListBox>
<toolkit:DataGrid x:Name="dgOrders"
    Grid.Column="1"
    ItemsSource="{Binding Source={StaticResource cvsOrders}}">
</toolkit:DataGrid>

Let’s start with the DomainDataSource first. As you can see, it is simply calling the Query that was created on the server. Next we see that we have a lbCustomers ListBox that sets the Data property of the DomainDataSource to its ItemsSource property. I am also setting the SelectedItem of the ListBox to the property exposed on my ViewModel called, “CurrentCustomer”. This allows me to access from other controls.

Note: The namespace alias compMod comes from the following mapping: xmlns:compMod=”clr-namespace:System.ComponentModel;assembly=System.Windows”

Finally, I have created a CollectionViewSource object. This may seem old hat to most WPF programmers and is a great addition to Silverlight as well. I set the Source property of the CollectionViewSource to the property I exposed on the ViewModel but I also reference the child collection of Order. I then add a SortDescription that sorts the Order collection based on the OrderDate property.

Now when this screen opens, it will be ordered by CustomerName first, then when a user selects a given Customer, all of the Orders will also be ordered by OrderDate as well.

This may seem to be a lot of work getting the Master/Detail or Parent/Child ordering to work but this solution turns out to be quite flexible and allows you to extend it to pretty much whatever you can think of.

Hope this helps….

Problem Deploying WCF RIA Services using Entity Framework 4

June 29, 2010 1 comment

So I had my software tested and ready to deploy to the client. I installed the application on a local test server and confirmed that everything was working fine. It is using Silverlight 4, Prism, WCF RIA Services on top of Entity Framework 4. All seemed to be smooth sailing….

However, once I deployed to the client site on their internal servers, everything went wrong. After spending several days and installing Visual Studio on the client’s server to see what was wrong, I finally found the culprit. In hindsight, this may be a simple solution but when you are knee deep looking at code and configuration it is easy to overlook.

The problem turned out that I had developed and tested the application against SQL Server 2008. When I deployed the application to the client, their database instance is SQL Server 2005.

In the Entity Framework 4, it actually targets the database instance for obtaining the correct schema. There is no way to modify this in the Visual Designer or properties pane of Visual Studio. You must open the edmx document using the Xml (Text) Editor.

Here is a sample of what it looked like:

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="2.0" xmlns:edmx="http://schemas.microsoft.com/ado/2008/10/edmx">
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
      <Schema Namespace="GlobalServices.Web.DataAccess.Store" 
        Alias="Self" Provider="System.Data.SqlClient" 
        ProviderManifestToken="2008">...</Schema>
    </edmx:StorageModels>
  <edmx:Runtime>
</edmx:Edmx>

Here is what it should look like:

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="2.0" xmlns:edmx="http://schemas.microsoft.com/ado/2008/10/edmx">
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
      <Schema Namespace="GlobalServices.Web.DataAccess.Store" 
        Alias="Self" Provider="System.Data.SqlClient" 
        ProviderManifestToken="2005">...</Schema>
    </edmx:StorageModels>
  <edmx:Runtime>
</edmx:Edmx>

As you can see, the only difference is in the ProviderManifestToken attribute. All I had to do was change the value from 2008 to 2005.

If you have developed an application using Entity Framework and were targeting SQL Server 2008 but then needed to deploy to a client using SQL Server 2005, please be aware of this necessary change for your application to work.

NOTE: Also be aware that you may need to change more than this if you are using SQL Server 2008 specific data types, (e.g. datetime2).

Hope this helps….

Filtering a DomainDataSource in Xaml

Another common feature that I use in the DomainDataSource control is the ability to filter data. You can define your filters either in XAML or programmaticallly.

Here are the available properties for a FilterDescriptor:

Name Description
IgnoredValue Gets or sets the value that is not applied to a query operation for filtering.
IsCaseSensitive Gets or sets a value indicating whether the FilterDescriptor is case sensitive for string values.
Operator Gets or sets the filter operator.
PropertyPath Gets or sets the name of the property path used as the data value to assess whether the entity meets the filter check.
Value Gets or sets the value to use for evaluating the filter condition.

The following is an example of filtering based on the value of another control:

<riaCtls:DomainDataSource 
    x:Name="ddsForms" 
    QueryName="ReturnFormsAssocaitedWithAMasterFormOnlyQuery" 
    AutoLoad="True">
    <riaCtls:DomainDataSource.DomainContext>
        <data:MyDomainContext />
    </riaCtls:DomainDataSource.DomainContext>

    <riaCtls:DomainDataSource.FilterDescriptors>
        <riaCtls:FilterDescriptor 
            PropertyPath="ApplicationId" Operator="IsEqualTo" 
            Value="{Binding ElementName=cboApplications, Path=SelectedItem.ApplicationId}" />
    </riaCtls:DomainDataSource.FilterDescriptors>
            
    <riaCtls:DomainDataSource.SortDescriptors>
        <riaCtls:SortDescriptor PropertyPath="Name" Direction="Ascending" />
    </riaCtls:DomainDataSource.SortDescriptors>

</riaCtls:DomainDataSource>

Most of the properties are self explanator but I wanted to go over the three main attributes on the FilterDescriptor object. You tell the filter what property you want it to operate on by passing in a value to the PropertyPath attribute. You tell it how to operate using the Operator attribute by choosing one of the following values:

Member name Description
IsLessThan The data value must be smaller than the filter value.
IsLessThanOrEqualTo The data value must be smaller than or equal to the filter value.
IsEqualTo The data value must be equal to the filter value.
IsNotEqualTo The data value must be different from the filter value.
IsGreaterThanOrEqualTo The data value must be larger than or equal to the filter value.
IsGreaterThan The data value must be larger than the filter value.
StartsWith The data value must start with the filter value.
EndsWith The data value must end with the filter value.
Contains The data value must contain the filter value.
IsContainedIn The data value must be contained in the filter value.

Finally, the Value attribute is what value you are going to pass to the filter.

If you want to provide a more complex criteria, you can simple do the following:

<riaCtls:DomainDataSource 
    x:Name="ddsForms" 
    QueryName="ReturnFormsAssocaitedWithAMasterFormOnlyQuery" 
    FilterOperator="And"
    AutoLoad="True">
    <riaCtls:DomainDataSource.DomainContext>
        <data:MyDomainContext />
    </riaCtls:DomainDataSource.DomainContext>

    <riaCtls:DomainDataSource.FilterDescriptors>
        <riaCtls:FilterDescriptor 
            PropertyPath="ApplicationId" Operator="IsEqualTo" 
            Value="{Binding ElementName=cboApplications, Path=SelectedItem.ApplicationId}" />
        <riaCtls:FilterDescriptor 
            PropertyPath="TagId" Operator="IsEqualTo" 
            Value="{Binding ElementName=cboTags, Path=SelectedItem.TagId}" />
    </riaCtls:DomainDataSource.FilterDescriptors>
            
    <riaCtls:DomainDataSource.SortDescriptors>
        <riaCtls:SortDescriptor PropertyPath="Name" Direction="Ascending" />
    </riaCtls:DomainDataSource.SortDescriptors>

</riaCtls:DomainDataSource>

The thing to notice is the new attribute FilterOperator. You can either do logical AND or logical OR operations. This gives you quite abit of flexibility when trying to provide your user with the flexibility of sifting through data.

I try to keep all of my behavior interactions bound to properties on my corresponding ViewModel . By exposing properties, I can use these values in other parts of my ViewModel without needing to expose the individual controls on my View. I sometimes also run into problems when I create my views dynamically and I can’t always rely on other controls being loaded or working when I want them to. Thus binding to properties seem to be the natural place for me when I want to filter my DomainDataSource.

The following is an example of binding to a ViewModel:

<riaCtls:DomainDataSource 
    x:Name="ddsForms" 
    QueryName="ReturnFormsAssocaitedWithAMasterFormOnlyQuery" 
    AutoLoad="True">
    <riaCtls:DomainDataSource.DomainContext>
        <data:MyDomainContext />
    </riaCtls:DomainDataSource.DomainContext>

    <riaCtls:DomainDataSource.FilterDescriptors>
        <riaCtls:FilterDescriptor 
            PropertyPath="ApplicationId" Operator="IsEqualTo" 
            Value="{Binding CurrentApplicationId}" />
    </riaCtls:DomainDataSource.FilterDescriptors>
            
    <riaCtls:DomainDataSource.SortDescriptors>
        <riaCtls:SortDescriptor PropertyPath="Name" Direction="Ascending" />
    </riaCtls:DomainDataSource.SortDescriptors>

</riaCtls:DomainDataSource>

Hope this helps…

Sorting a DomainDataSource in Xaml

June 2, 2010 2 comments

Sometimes when using the DomainDataSource control, it just doesn’t makes sense to write a custom query method only to provide sorting capabilities. Fortunately this is provided out of the box for you. You can do this either in XAML or you and programmatically code this up yourself.

The following is an example of doing a simple sort operation in Xaml:

<riaCtls:DomainDataSource
    x:Name="ddsApplications" QueryName="GetCg_xa_ApplicationQuery"
    DomainContext="{Binding DomainContext, Mode=TwoWay}">
    <riaCtls:DomainDataSource.SortDescriptors>
        <riaCtls:SortDescriptor PropertyPath="Name" Direction="Ascending" />
    </riaCtls:DomainDataSource.SortDescriptors>
</riaCtls:DomainDataSource>

The underlying grid is just setting its ItemsSource property to the Data property of the DomainDataSource. Here is the output of the sorted data in a grid:

Sorting Sample

A little more complex example is if you want to have a multi-column sort:

<riaCtls:DomainDataSource
    x:Name="ddsMasterForms" QueryName="ReturnMasterFormsIncludingForeignKeysQuery"
    DomainContext="{Binding DomainContext, Mode=TwoWay}">
    <riaCtls:DomainDataSource.SortDescriptors>
        <riaCtls:SortDescriptor PropertyPath="Tag.Name" Direction="Ascending" />
        <riaCtls:SortDescriptor PropertyPath="Name" Direction="Ascending" />
    </riaCtls:DomainDataSource.SortDescriptors>
</riaCtls:DomainDataSource>

As you can see, I wrote a custom query that will also bring back all of the foreign keys associated with my main object. I then insert the corresponding SortDescriptor objects in the order that I wish to perform the sorting operation. This is nice since I can go across objects or properties at the same level. The underlying grid is just setting its ItemsSource property to the Data property of the DomainDataSource.

Here is a screen shot of the complex sort:

Complex Sorting Sample

Finally, you can now using DataBinding if you wanted to have a more dynamic sorting.

<riaCtls:DomainDataSource
    x:Name="ddsApplications" QueryName="GetCg_xa_ApplicationQuery"
    DomainContext="{Binding DomainContext, Mode=TwoWay}">
    <riaCtls:DomainDataSource.SortDescriptors>
        <riaCtls:SortDescriptor
            PropertyPath="{Binding Path=SelectedItem.Content, ElementName=cboSort}"
            Direction="Ascending" />
    </riaCtls:DomainDataSource.SortDescriptors>
</riaCtls:DomainDataSource>

The above example assumes you have a ComboBox named, “cboSort”. If you want to bind to your ViewModel in MVVM:

<riaCtls:DomainDataSource
    x:Name="ddsApplications" QueryName="GetCg_xa_ApplicationQuery"
    DomainContext="{Binding DomainContext, Mode=TwoWay}">
    <riaCtls:DomainDataSource.SortDescriptors>
        <riaCtls:SortDescriptor
            PropertyPath="{Binding CurrentSortText}"
            Direction="Ascending" />
    </riaCtls:DomainDataSource.SortDescriptors>
</riaCtls:DomainDataSource>

The above example assumes you have a property called, “CurrentSortText” on your ViewModel. This gives you some flexibility of how you want to interact with the DomainDataSource.

NOTE: If you leave off the Direction property on the SortDescriptor, the default direction will be ascending.

Hope this helps…

Follow

Get every new post delivered to your Inbox.

Join 209 other followers