如何包含一个特定的行，从另一个表中使用LINQ到实体

I have a database with these two tables: Customer and CustomerStatus. CustomerStatus is a so called readonly table. All changes to a customer's status result in an INSERT into that table. The current customer status is locatable via CustomerStatus.StatusTimestamp.

我有一个有这两个表的数据库:Customer和CustomerStatus。CustomerStatus是一个所谓的只读表。对客户状态的所有更改都会导致插入到该表中。当前客户状态可以通过customerstate . statustimestamp进行定位。

Now I'm looking for a LINQ to entities query that loads all Customers together with their current status. Something like

现在我正在寻找一个LINQ to entities查询，该查询将所有客户及其当前状态加载在一起。类似的

.Include("CustomerStatus.OrderByDescending(StatusTimestamp).FirstOrDefault()").

其中包括(“CustomerStatus.OrderByDescending(StatusTimestamp).FirstOrDefault())。

I haven't found a proper way with with EF 4.0.

我还没有找到一种使用EF 4.0的合适方法。

My current workaround is very ugly and uses 2 steps:

我目前的解决方案很难看，需要两个步骤:

step 1: LINQ query that loads all status (in the business/data layer):

步骤1:加载所有状态(在业务/数据层)的LINQ查询:

var r = from c in db.Customers.Include("CustomerStatus") select c;

var r = from c in db. customer. include(“CustomerStatus”)select c;

step 2: take the suitable status from each customer in the GUI (in a loop in a MVC3 view):

步骤2:从GUI中的每个客户(在MVC3视图中的循环中)获取适当的状态:

c.CustomerStatus.OrderByDescending(i => i.StatusTimestamp).FirstOrDefault()

c.CustomerStatus。OrderByDescending(i = > i.StatusTimestamp).FirstOrDefault()

Any idea how this can be done directly in one step?

你知道这一步怎么能直接完成吗?

3 个解决方案

#1

EF is more powerful than you give it credit for. One of the things it can do is project to complex types within an IQueryable (ie, in one database hit). This should work:

EF比你想象的更强大。它可以做的一件事是在IQueryable(即在一次数据库访问中)中对复杂类型进行投影。这应该工作:

var data = 
    from c in db.Customers
    select new { 
        Customer = c, 
        LastStatus = c.CustomerStatus  // assuming navigation property set up right
                      .OrderByDescending(s => s.StatusTimestamp)
                      .FirstOrDefault()
    };

Now data is an IQueryable<anonymous_type>, and each item has a Customer property giving the customer, and a LastStatus giving the latest-timestamped status, or null if there aren't any status records for the customer.

现在，data是一个IQueryable< unknowous_type >，并且每个条目都有一个客户属性来给客户，以及一个给出最新时间戳状态的LastStatus，如果客户没有任何状态记录，则为null。

You can use a named type here too, it's just quicker for me to type it this way :).

你也可以在这里使用一个命名类型，我用这种方式输入会更快。

#2

I'm not sure how your schema looks like but what about

我不确定你的模式是什么样子的，但是关于

var query = from c in db.Customers 
            join CustomerStatus s on c.StatusTimestamp = s.StatusTimestamp
            order by s.StatusTimestamp
            select c;

#3

-1

Something like this??

是这样的吗? ?

   var r = (from c in db.Customers.Include("CustomerStatus")
            where c.CustomerStatus.OrderByDescending(i => i.StatusTimestamp)
            select c).FirstOrDefault()

#1