Flatten One-to-Many Relationship Using Dynamic LINQ

Question

Is it possible to flatten a one-to-many relationship using dynamic LINQ?

For example, I might have a list of Users and the User class contains a list of many UserPreferences. The UserPreference class is essentially a name/value pair.

A user will define what types of user preferences are available for a group of users.

public class User
{
    public string FirstName
    {
     get;
     set;
    }

    public string LastName
    {
     get;
     set;
    }

    public IList<UserPreference> UserPreferences
    {
     get;
     set;
    }
}

public class UserPreference
{
    public UserPreference(string name, object userValue)
    {
     this.Name = name;
     this.UserValue = userValue;
    }

    public string Name
    {
     get;
     set;
    }

    public object UserValue
    {
     get;
     set;
    }
}

Therefore one user group might be defined in the following way:

List<User> users = new List<User>();

User user1 = new User();
user1.FirstName = "John";
user1.LastName = "Doe";

user1.UserPreferences.Add(new UserPreference("Favorite color", "Red"));

User user2 = new User();
user2.FirstName = "Jane";
user2.LastName = "Doe";

user2.UserPreferences.Add(new UserPreference("Favorite mammal", "Dolphin"));
user2.UserPreferences.Add(new UserPreference("Favorite color", "Blue"));

users.Add(user1);
users.Add(user2);

return users;

The desired output would be:

First Name   Last Name  Favorite Color  Favorite Mammal
John     Doe   Red   NULL
Jane     Doe   Blue   Dolphin

Is there a way to create an anonymous type so that UserPreferences would get rolled up into the User?

For example,

var u = UserScopedSettingAttribute.Select("new (FirstName as FirstName, UserValue as FavoriteColor)", null);

string name = u.FirstName;
string color = u.FavoriteColor;

Ultimately this list of Users will get bound to an ASP.NET GridView web control. There will be a large volume of data involved in this operation and performance will be critical.

Any suggestions are appreciated!

Answer 1

My best suggestion would be to not use dynamic LINQ, but add a flatuser class and then loop through the users. The code for this is simple, and if you were able to get a linq query with similar results it would generate the same code, although you can't really tell how optimized it would be as it might involve some joins that would incur a performance penalty instead of just looping. If you were pulling this from a database using LINQ to SQL then you could use an entity relation to to get the data using linq instead of this loop.

Loop:

List<FlatUser> flatusers = new List<FlatUser>();
foreach (User u in users)
{
    foreach (UserPreference up in u.UserPreferences)
    {
        flatusers.Add(new FlatUser
        {
            FirstName = u.FirstName,
            LastName = u.LastName,
            Name = up.Name,
            UserValue = up.UserValue
        });
    }
}

Flat User Class:

public class FlatUser
{
    public string FirstName
    {
        get;
        set;
    }

    public string LastName
    {
        get;
        set;
    }

    public string Name
    {
        get;
        set;
    }

    public object UserValue
    {
        get;
        set;
    }
}

Answer 2

I know it doesn't exactly answer your question, but compiling strings into new classes at runtime like dlinq does has always had kind of a bad smell to it. Consider just simply using a DataTable like this,

DataTable prefs = new DataTable();
IEnumerable<DataColumn> cols = (from u in users
                                from p in u.UserPreferences
                                select p.Name)
                               .Distinct()
                               .Select(n => new DataColumn(n));

prefs.Columns.Add("FirstName");
prefs.Columns.Add("LastName");
prefs.Columns.AddRange(cols.ToArray());

foreach (User user in users)
{
    DataRow row = prefs.NewRow();
    row["FirstName"] = user.FirstName;
    row["LastName"] = user.LastName;
    foreach (UserPreference pref in user.UserPreferences)
    {
        row[pref.Name] = pref.UserValue;
    }
    prefs.Rows.Add(row);
}

Answer 3

Unfortunately

var u = UserScopedSettingAttribute.Select("new {FirstName as FirstName, UserValue as FavoriteColor}", null);
string name = u.FirstName;
string color = u.FavoriteColor;

won't work. When you use DLINQ Select(string) the strongest compile time class information you have is Object, so u.FirstName will throw a compile error. The only way to pull the properties of the runtime generated anonymous class is to use reflection. Although, if you can wait, this will be possible with C# 4.0 like this,

dynamic u = UserScopedSettingAttribute.Select("new {FirstName as FirstName, UserValue as FavoriteColor}", null);
string name = u.FirstName;
string color = u.FavoriteColor;

Answer 4

There are probably more efficient ways to do this, but to actually answer your question, I came up with the following code. (Note that I've never worked with DynamicLinq before, so there may be a better way to use it to accomplish your goal.)

I created a console application, pasted in the classes from your post, then used the following code.

static void Main(string[] args)
{
    var users = GetUserGroup();
    var rows = users.SelectMany(x => x.UserPreferences.Select(y => new { x.FirstName, x.LastName, y.Name, y.UserValue }));
    var userProperties = rows.Select(x => x.Name).Distinct();
    foreach (var property in userProperties)
    {
        Console.WriteLine(property);
    }
    Console.WriteLine();

    // The hard-coded variety.
    var results = users.Select(x => new
    {
        x.FirstName,
        x.LastName,
        FavoriteColor = x.UserPreferences.Where(y => y.Name == "Favorite color").Select(y => y.UserValue).FirstOrDefault(),
        FavoriteAnimal = x.UserPreferences.Where(y => y.Name == "Favorite mammal").Select(y => y.UserValue).FirstOrDefault(),
    });

    ///////////////////////////////////////////////////////////////////////////////////////////////////////////////
    // The dynamic variety.
    DynamicProperty[] dynamicProperties = new DynamicProperty[2 + userProperties.Count()];
    dynamicProperties[0] = new DynamicProperty("FirstName", typeof(string));
    dynamicProperties[1] = new DynamicProperty("LastName", typeof(string));
    int propIndex = 2;
    foreach (var property in userProperties)
    {
        dynamicProperties[propIndex++] = new DynamicProperty(property, typeof(string));
    }
    Type resultType = ClassFactory.Instance.GetDynamicClass(dynamicProperties);
    ConstructorInfo constructor = resultType.GetConstructor(new Type[] {});
    object[] constructorParams = new object[] { };
    PropertyInfo[] propInfoList = resultType.GetProperties();
    PropertyInfo[] constantProps = propInfoList.Where(x => x.Name == "FirstName" || x.Name == "LastName").OrderBy(x => x.Name).ToArray();
    IEnumerable<PropertyInfo> dynamicProps = propInfoList.Where(x => !constantProps.Contains(x));
    // The actual dynamic results creation.
    var dynamicResults = users.Select(user =>
    {
        object resultObject = constructor.Invoke(constructorParams);
        constantProps[0].SetValue(resultObject, user.FirstName, null);
        constantProps[1].SetValue(resultObject, user.LastName, null);
        foreach (PropertyInfo propInfo in dynamicProps)
        {
            var val = user.UserPreferences.FirstOrDefault(x => x.Name == propInfo.Name);
            if (val != null)
            {
                propInfo.SetValue(resultObject, val.UserValue, null);
            }
        }
        return resultObject;
    });
    //////////////////////////////////////////////////////////////////////////////////////////////////////////

    // Display the results.
    var displayResults = dynamicResults;
    //var displayResults = results;

    if (displayResults.FirstOrDefault() != null)
    {
        PropertyInfo[] properties = displayResults.First().GetType().GetProperties();
        int columnWidth = Console.WindowWidth / properties.Length;

        int index = 0;
        foreach (PropertyInfo property in properties)
        {
            Console.SetCursorPosition(index++ * columnWidth, Console.CursorTop);
            Console.Write(property.Name);
        }
        Console.WriteLine();

        foreach (var result in displayResults)
        {
            index = 0;
            foreach (PropertyInfo property in properties)
            {
                Console.SetCursorPosition(index++ * columnWidth, Console.CursorTop);
                Console.Write(property.GetValue(result, null) ?? "(null)");
            }
            Console.WriteLine();
        }
    }

    Console.WriteLine("\r\nPress any key to continue...");
    Console.ReadKey();
}

static List<User> GetUserGroup()
{
    List<User> users = new List<User>();

    User user1 = new User();
    user1.FirstName = "John";
    user1.LastName = "Doe";
    user1.UserPreferences = new List<UserPreference>();

    user1.UserPreferences.Add(new UserPreference("Favorite color", "Red"));
    user1.UserPreferences.Add(new UserPreference("Birthday", "Friday"));

    User user2 = new User();
    user2.FirstName = "Jane";
    user2.LastName = "Doe";
    user2.UserPreferences = new List<UserPreference>();

    user2.UserPreferences.Add(new UserPreference("Favorite mammal", "Dolphin"));
    user2.UserPreferences.Add(new UserPreference("Favorite color", "Blue"));

    users.Add(user1);
    users.Add(user2);

    return users;
}

Answer 5

This should do it. Flattening is generally done with SelectMany extension method, but in this case I am using a let expression. The code to remove the null preferences is a bit ugly and could prob be improved but it works:

        var flattenedUsers = from user in GetUsers()
                let favColor = user.UserPreferences.FirstOrDefault(pref => pref.Name == "Favorite color")
                let favMammal = user.UserPreferences.FirstOrDefault(pref => pref.Name == "Favorite mammal")
                select new
                           {
                               user.FirstName,
                               user.LastName,
                               FavoriteColor = favColor == null ? "" : favColor.UserValue,
                               FavoriteMammal = favMammal == null ? "" : favMammal.UserValue,
            };

Answer 6

I think the pragmatic answer here is to say your attempting to force C# to become a dynamic language and any solution is going to be really pushing C# to its limits. Sounds like your trying to transform a database query of columns that are only determined at query time into a collection that is based on those columns and determined at run time.

Linq and Gridview binding is really pretty and succinct and all but you have to start thinking about weighing the benefit of getting this compiler bending solution to work just so you don't have to dynamically generate gridview rows and columns by yourself.

Also if your concerned about performance I'd consider generating the raw HTML. Relying on the collection based WebForms controls to efficiently display large sets of data can get dicey.

You add in a couple of OnItemDataBound events and boxing and unboxing is going to really gum up the works. I'm assuming too your going to want to add interactive buttons and textboxes to the rows as well and doing 1000 FindControls has never been fast.