使用动态LINQ展平一对多关系

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

是否可以使用动态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.

例如,我可能有一个Users列表,User类包含许多UserPreferences的列表。 UserPreference类本质上是名称/值对。

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?

有没有办法创建一个匿名类型,以便UserPreferences将汇总到用户?

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.

最终,这个用户列表将绑定到ASP.NET GridView Web控件。此操作涉及大量数据,性能至关重要。

Any suggestions are appreciated!

任何建议表示赞赏!

6 个解决方案

#1

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,

我知道它并没有完全回答你的问题,但是在运行时将字符串编译成新类,就像dlinq一样,它总是有一种难闻的气味。考虑只是简单地使用像这样的DataTable,

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);
}

#2

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:

这应该做到这一点。展平通常使用SelectMany扩展方法完成,但在这种情况下我使用let表达式。删除null首选项的代码有点难看,可能会有所改进,但它可以工作:

        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,
            };

#3

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.

我最好的建议是不使用动态LINQ,但添加一个flatuser类,然后遍历用户。这个代码很简单,如果你能够得到一个类似结果的linq查询,它会产生相同的代码,虽然你无法确定它是如何优化的,因为它可能涉及一些会产生性能的连接惩罚而不仅仅是循环。如果您使用LINQ to SQL从数据库中提取此数据,则可以使用实体关系来使用linq而不是此循环来获取数据。

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;
    }
}

#4

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,

不行。当您使用DLINQ Select(字符串)时,您拥有的最强编译时类信息是Object,因此u.FirstName将引发编译错误。拉动运行时生成的匿名类的属性的唯一方法是使用反射。虽然,如果你可以等,这可以用C#4.0这样,

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

#5

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.

我认为这里务实的答案是说你试图强迫C#成为一种动态语言,而任何解决方案都会将C#推向极限。听起来像是在尝试将仅在查询时确定的列的数据库查询转换为基于这些列并在运行时确定的集合。

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.

Linq和Gridview绑定非常简洁,除了你必须开始考虑权衡使这个编译器弯曲解决方案工作的好处,所以你不必自己动态生成gridview行和列。

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.

此外,如果您关注性能,我会考虑生成原始HTML。依靠基于集合的WebForms控件来有效地显示大量数据集可能会有点冒险。

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.

你添加了几个OnItemDataBound事件,拳击和拆箱将真正搞砸了作品。我也假设你想要在行中添加交互式按钮和文本框,并且做1000个FindControls从未如此快。

#6

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.)

可能有更有效的方法来做到这一点,但实际上回答你的问题,我想出了以下代码。 (请注意,我之前从未使用过DynamicLinq,因此可能有更好的方法来使用它来实现您的目标。)

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;
}