在LINQ中执行FirstOrDefault的另一种方式 [英] Alternative way to do FirstOrDefault in LINQ

问题描述

我有一个成员资格表，该成员资格表记录用户是否是列表的成员.当对用户的成员资格进行更新时，会写入新记录，而先前的记录将保留原样，从而可以保留其成员资格的历史记录.要获取用户的会员资格状态，需要选择他们的最新条目.

I've got a membership table that records whether a user is a member of a list. When an update to a user's membership occurs a new record is written and the previous records are left as is which allows a history of their memberships to be maintained. To get a user's membership status involves selecting their most recent entry.

下面是一些用户列表成员资格数据的示例.目的是找到一个LINQ表达式，该表达式按列表和用户分组，但仅返回具有最近插入记录的行.

An example of some user list membership data is below. The aim is to find a LINQ expression that groups by list and user but only returns the row with most recently inserted record.

List Name, Username, Comment, ExpiresOn, Inserted
Test List, joeb, second update, 2012-03-13 16:55:03, 2012-01-31 22:28:40
Test List, joeb, first update, 2012-02-13 16:55:01, 2012-01-31 22:28:39
Test List, joeb, initial, 2012-01-13 16:55:02, 2012-01-31 22:28:38

SQL查询说明了如何提取当前列表的成员身份.

An SQL query illustrates how the current list membership status can be extracted.

select ulm2.ID, ulm2.ExpiresOn, ulm2.Comment, ulm2.Inserted 
from UserListMembership as ulm1
left outer join UserListMembership ulm2 on ulm1.id = ulm2.id
group by ulm1.userlistid, ulm1.userid;

问题是如何为不使用嵌套的FirstOrDefault调用的查询编写LINQ表达式，这将导致我的MySQL实体框架提供程序抛出"System.NotSupportedException:不支持指定的方法".例外?

The question is how to write a LINQ expression for the query that doesn't use a nested FirstOrDefault call which causes my MySQL entity framework provider to throw a "System.NotSupportedException: Specified method is not supported." exception?

更新:

下面是我失败的LINQ表达式，添加FirstOrDefault调用后，它将引发不支持指定的方法".

Below is my failing LINQ expression, it throws the "Specified method is not supported" once the FirstOrDefault call is added.

var query = from mem in db.UserListMemberships
            where
              mem.User.UserUsernames.Any(y => y.Username.ToLower() == username.ToLower())
              && mem.UserList.Account.Subscriptions.Any(x => x.ID == subscriptionID)
              && mem.ExpiresOn > utcNow
            group mem by new { mem.UserListID, mem.UserID } into g
            select new { UserListMembership = (from mem2 in db.UserListMemberships where mem2.UserListID == g.Key.UserListID && mem2.UserID == g.Key.UserID orderby mem2.Inserted descending select mem2).FirstOrDefault() };

 return query.Select(a => a.UserListMembership).ToList();

推荐答案

没有发布代码，任何人都很难看到您的问题所在.

Without posting code it's hard for anyone to see what your issue is.

您是否不能仅按日期字段对结果进行排序，然后选择第一条记录?像users.OrderByDescending(u => u.Inserted).FirstOrDefault()一样?

Are you not able to just order the results by your date field and then select the first record? Something like users.OrderByDescending(u => u.Inserted).FirstOrDefault()?

这篇关于在LINQ中执行FirstOrDefault的另一种方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！