Linq 分组问题

Question

我正在开发一个简单的 session 室预订系统。每个 session 室都根据其大小等分配了一种房间类型。我希望根据以下因素确定预订房间的数量:

01/01/2011  1   Room A  2
01/01/2011  2   Room B  5
01/01/2011  3   Room C  3
01/01/2011  4   Room D  2
01/01/2011  5   Room E  1
01/01/2011  6   Room F  5
02/01/2011  1   Room A  3
02/01/2011  2   Room B  5
02/01/2011  3   Room C  2
02/01/2011  4   Room D  5
02/01/2011  5   Room E  2
02/01/2011  6   Room F  2
03/01/2011  1   Room A  2
03/01/2011  2   Room B  5
03/01/2011  3   Room C  2

分组数据如:

Date        Room A  Room B  Room C  Room D  Room E  Room F
01/01/2011  2       5       3       2       1       5
02/01/2011  3       5       2       5       2       2
03/01/2011  2       5       2       4       5       8
04/01/2011  4       7       3       5       2       2

我以前使用数据集成功地做到了这一点，但我需要能够在 Linq 中为这个项目使用 Linq to entites 来做到这一点

有人可以建议最好的方法吗？

谢谢

Answer 1

这是我遇到此类问题时所做的:

var rooms = bookings
    .Select(b => b.Room)
    .Distinct()
    .OrderBy(r => r)
    .ToArray();

var query = (
    from b in bookings
    group b by b.Date into gbs
    let l = gbs.ToLookup(gb => gb.Room, gb => gb.Count)
    select new
    {
        Date = gbs.Key,
        RoomCounts = rooms.Select(r => l[r].Sum()).ToArray(),
    }).ToArray();

这基本上会产生以下数组:

var rooms = new []
{
    "Room A", "Room B", "Room C", "Room D", "Room E", "Room F",
};

var query = new []
{
    new
    {
        Date = new DateTime(2011, 01, 01),
        RoomCounts = new [] { 2, 5, 3, 2, 1, 5 }
    },
    new
    {
        Date = new DateTime(2011, 01, 02),
        RoomCounts = new [] { 3, 5, 2, 5, 2, 2 }
    },
    new
    {
        Date = new DateTime(2011, 01, 03),
        RoomCounts = new [] { 2, 5, 2, 0, 0, 0 }
    },
};

RoomCounts 数组的长度与 rooms 数组的长度相同，每个索引位置的值与 rooms 数组中的房间匹配.

它通常相当可行。

另一种方法是创建一个数组数组来表示有点像电子表格的网格。

var query2 = (new object[]
    {
        (new object[] { "Date" })
            .Concat(rooms.Cast<object>())
            .ToArray()
    }).Concat(
        from b in bookings
        group b by b.Date into gbs
        let l = gbs.ToLookup(gb => gb.Room, gb => gb.Count)
        select (new object[] { gbs.Key })
            .Concat(rooms.Select(r => l[r].Sum()).Cast<object>())
            .ToArray())
            .ToArray();

这会产生以下内容:

    var q2 = new object[]
    {
        new object[] {
            "Date", "Room A", "Room B", "Room C", "Room D", "Room E", "Room F" },
        new object[] { new DateTime(2011, 01, 01), 2, 5, 3, 2, 1, 5 },
        new object[] { new DateTime(2011, 01, 02), 3, 5, 2, 5, 2, 2 },
        new object[] { new DateTime(2011, 01, 03), 2, 5, 2, 0, 0, 0 },
    };

替代方案的替代方案，以防查询看起来有点毛茸茸，就是这样做:

Func<object, IEnumerable, object[]> prepend = (o, os) =>
    (new object[] { o }).Concat(os.Cast<object>()).ToArray();

Func<object[], IEnumerable<object[]>, object[][]> prepends = (o, os) =>
    (new object[][] { o }).Concat(os).ToArray();

var query2 = prepends(prepend("Date", rooms),
    from b in bookings
    group b by b.Date into gbs
    let l = gbs.ToLookup(gb => gb.Room, gb => gb.Count)
    select prepend(gbs.Key, rooms.Select(r => l[r].Sum())));

这种形式的查询仍然会产生相同的对象网格，但恕我直言，它比第一种形式更具可读性。

希望对您有所帮助。