MVVM light datagrid loading from two relational database tables

Question

How to Load DataGrid with two related tables using MVVM light, I am using .NET RIA and silverlight 4.

for example if my data tables are:

userInfo- userID, Name, AddressID
Address - AddressID, StreetName, Zip

how can i create datagrid that displays [Name, StreetName, ZIp]

Answer 1

There are a couple approaches possible here. I will explain both that come to mind. First, is using a relationship already defined in your database. Second is to return via a custom model class.

Option 1: I will assume you have created an Entity Framework Model (with a relationship) from your database on the Web Project and created the DomainService to host the Model. !!!One key to do when you create the model is to create meta-data classes for the models!!! (I missed this the first few times through, and it is critical to modify the behavior of your models, such as you are needing here)

Within the meta-data model, you will find the association property, and decorate it with the [Include] attribute. Here is an example I have from another project with a WorklanDetail, where I want to return the associated Assignments:

[MetadataTypeAttribute(typeof(WorkplanDetailMetadata))]
public partial class WorkplanDetail
{
    internal sealed class WorkplanDetailMetadata
    {
        [Include]
        public EntityCollection<Assignment> Assignments { get; set; }
    }
}

then, you can just reference the property in your Silverlight application, and viola your address data is available.

To bind a single Entity (EntityReference, not collection) then you will just use you property to access the sub-entity on your datagrid's binding... For exmaple:

Text="{Binding Path=Address.StreetName}"

That is the simplest method that I know of.

Option 2 requires creating your own custom class (you must at least have a property decorated with the [Key] attribute ot facilitate the transfer to/from the client). Here is an example, I have used to get foldersearch result information:

public class FolderSearchResult
{
    [Key]
    public string EFOLDERID { get; set; }
    public string Subject { get; set; }
    public string FolderName { get; set; }
}

The key being that the EFOLDERID propety has the [Key] attribute, which uniquely identifies each item just like a PK in a database.

Then in your service class you can return this kind of like this:

public IEnumerable<FolderSearchResult> GetFolderResults(string search)
    {
        var query = from ge in this.ObjectContext.Generic_Engagement
                    from f in this.ObjectContext.eFolders
                    where ge.EFOLDERID == f.eFolderID &
                    f.eArchived == 0 &
                    f.eSubject.Contains(search) &

                    (from wp in this.ObjectContext.Workplans
                     where wp.EFOLDERID == f.eFolderID
                     select wp).Count() == 0 &

                     (from r in this.ObjectContext.Resources
                      where r.EFOLDERID == f.eFolderID
                      select r).Count() == 0

                    select new FolderSearchResult()
                      {
                        EFOLDERID = f.eFolderID,
                        FolderName = f.eFolderName,
                        Subject = f.eSubject
                      };
        return query.AsEnumerable<FolderSearchResult>();
    }

Couple of notes about this approach:

1. This works best in my opinion when you don't need Read-Only access. If you are doing updates/inserts/deletes use the first approach.
2. This works well to help when you need to create some logical object between completely different data sources (maybe userid and employeeid from database, plus display name, site location, etc. from Active Directory).
3. Updating this way is possible, but you must do a bit more work to separate out your items and update everything properly.

In summary, I would suggest using the first approach, unless the following occur: You need a logical object which crosses data-storage (separate db sources), or you need fast read-only access (such as ItemSource binding for drop-down list).

Offloading sorting and filtering logic to the server, even at the expense of additional entities loaded in memory so that your client can remain fast pays dividends in my experience. Using local sort in a CollectionViewSource or something can do the same, but just a few of these can really start slowing your application speed.