LINQ To SQL SubSelect Like 查询

发布于 2024-07-23 10:36:35 字数 1081 浏览 4 评论 0原文

想象一下有两张桌子。

Order
+----------------+
| ID             |
| Name           |
+----------------+

OrderStatus
+----------------+
| ID             |
| OrderId        |
| StatusId       |
+----------------+

一个订单可以有多个 OrderStatus，可以称为 OrderStatusHistory。我将有一个 StronglyTypeObject Order，其描述如下。

namespace my.project
{
    public class Order
    {
        Int64 OrderId { get; set; }
        String Name { get; set; }
        Int64 StatusId { get; set; }
    }
}

Order 对象中的此 StatusId 意味着 OrderStatus 表中的当前（最后一个）StatusId。

我尝试使用 LINQ 构建 IQueryable 对象列表。这是我的，不工作;)，Linq 代码

var result = from r in dbContext.ORDER
             select new Order
             {
                 OrderId = r.ID,
                 Name = r.Name,
                 StatusId = dbContext.OrderStatus
                            .Where(p => p.OrderId == r.ID).Last().StatusId
             }

我也尝试过使用 Max(p=>p.XXX) 但没有成功。有人对这个问题有提示吗？

任何帮助将不胜感激......

戈登

原文

imagine there are two tables.

Order
+----------------+
| ID             |
| Name           |
+----------------+

OrderStatus
+----------------+
| ID             |
| OrderId        |
| StatusId       |
+----------------+

A Order can have more than one OrderStatus, which could be called OrderStatusHistory.
I'll have an StronglyTypeObject Order, which is descripted as follows

namespace my.project
{
    public class Order
    {
        Int64 OrderId { get; set; }
        String Name { get; set; }
        Int64 StatusId { get; set; }
    }
}

This StatusId in the Order Object is meant to be the current (last) StatusId from the OrderStatus Table.

I have tried to build a IQueryable List of Objects with LINQ. Here is my, not working ;), Linq Code

var result = from r in dbContext.ORDER
             select new Order
             {
                 OrderId = r.ID,
                 Name = r.Name,
                 StatusId = dbContext.OrderStatus
                            .Where(p => p.OrderId == r.ID).Last().StatusId
             }

I have also tried working with Max(p=>p.XXX) but it hasn't worked out.
Does anyone has a hint on this problem?

Any Help would be much appreciated...

Gordon

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

塔塔猫 2024-07-30 10:36:37

好吧，这样怎么样：（取两个，按降序排列，然后取第一个.. top 1 ）

var result = from r in dbContext.ORDER
         select new Order
         {
             OrderId = r.ID,
             Name = r.Name,
             StatusId = dbContext.OrderStatus
                                 .Where(p => p.OrderId == r.ID)
                                 .OrderByDescending(p => p.OrderID)
                                 .Select(p => p.StatusId)
                                 .FirstOrDefault()
         }

alright, how about this: (take two, order descending and take first .. top 1 )

var result = from r in dbContext.ORDER
         select new Order
         {
             OrderId = r.ID,
             Name = r.Name,
             StatusId = dbContext.OrderStatus
                                 .Where(p => p.OrderId == r.ID)
                                 .OrderByDescending(p => p.OrderID)
                                 .Select(p => p.StatusId)
                                 .FirstOrDefault()
         }

回复收藏 0 原文

﹉夏雨初晴づ 2024-07-30 10:36:36

根据您的评论，我已更新以下内容以使用 First，在这种情况下，您将需要对键执行 OrderByDescending 以获得正确的顺序。

var result = from r in dbContext.ORDER
         select new Order
         {
             OrderId = r.ID,
             Name = r.Name,
             StatusId = dbContext.OrderStatus
                        .Where(p => p.OrderId == r.ID)
                        .OrderByDescending( p => p.ID )
                        .First()
                        .StatusId
         }

另外，如果定义了 FK 关系，则无需创建中间对象即可更轻松地获取最后一个 StatusId。在这种情况下，我认为您可以使用 Last （如果对象已预加载），因为您将使用 LINQtoObjects，而不是 LINQToSQL。 YMMV。

var currentStatus = order.OrderStatuses.Last().StatusId;

后者可以作为 ORDER 的分部类的方法添加，以便您可以将其引用为。

var currentStatus = order.CurrentStatus;

public partial class ORDER
{
     public int64 CurrentStatus
     {
         get
         {
             return this.OrderStatuses.Last().StatusId;
         }
     }
}

Based on your comment, I've updated the below to use First and, in that case, you will need to do an OrderByDescending on the key to get it in the right order.

var result = from r in dbContext.ORDER
         select new Order
         {
             OrderId = r.ID,
             Name = r.Name,
             StatusId = dbContext.OrderStatus
                        .Where(p => p.OrderId == r.ID)
                        .OrderByDescending( p => p.ID )
                        .First()
                        .StatusId
         }

Also, if you have a FK relationship defined, it should be much easier to get the last StatusId without creating an intermediate object. In this case, I think you can use Last (if the objects are preloaded) since you'll be doing LINQtoObjects, not LINQToSQL. YMMV.

var currentStatus = order.OrderStatuses.Last().StatusId;

The latter could be added as a method on a partial class for ORDER so that you can refer to it as.

var currentStatus = order.CurrentStatus;

public partial class ORDER
{
     public int64 CurrentStatus
     {
         get
         {
             return this.OrderStatuses.Last().StatusId;
         }
     }
}

回复收藏 0 原文

~没有更多了~