尝试将linq数据分配到DTO类时出错

Question

我需要将Linq数据分配到我的DTO类中，但这个Linq查询得到了这个错误。

LINQ to Entities does not recognize the method 'System.String ToString(System.String, System.IFormatProvider)' method, and this method cannot be translated into a store expression. 

我删除了int转换以避免错误，然后我得到了这个错误

联接子句中某个表达式的类型不正确。在加入的调用中类型推断失败

我的代码如下，

public class TextDto
{
public string AddedData { get; set; }
public string AddedTime { get; set; }
public string Sender { get; set; }
public string Name { get; set; }
public string Messsage { get; set; }
}

linq码

var allText = from nm in context.NotificationMessages
join u in context.Users on new {
Sender = (int) nm.Sender
}
equals new {
Sender = u.UserId
}
select new {
nm.Id,
nm.AddedTime,
nm.Lobby.Branch.BranchName,
u.FirstName,
u.LastName,
nm.Message
};
var allTextList = allText.Select(data = >new TextOnDemandDto {
AddedTime = data.AddedTime.ToString("hh:mm tt", CultureInfo.InvariantCulture),
Messsage = data.Message,
Name = data.FirstName + " " + data.LastName
}).ToList();

这个sql，我把它转换成linq

SELECT nm.id,
nm.addedtime,
b.branchname,
u.firstname,
u.lastname,
nm.message
FROM   notificationmessage nm
INNER JOIN lobby l
ON l.lobbyid = nm.fklobbyid
INNER JOIN branch b
ON b.branchid = l.fkbranchid
INNER JOIN [user] u
ON nm.sender = u.userid  

Answer 1

尝试将查询更改为：

var allText = (from nm in context.NotificationMessages
join u in context.Users 
on nm.Sender equals  u.UserId into ug
from u in ug.DefaultIfEmpty()
join l in context.Lobbys 
on    nm.fklobbyid eguals  l.lobbyid into lg
from l in lg.DefaultIfEmpty()
join b in  context.Branches 
on l.fkbranchid  equals b.branchid into bg
from b in bg.DefaultIfEmpty()
select new {
nm.Id,
nm.AddedTime,
b.BranchName,
u.FirstName,
u.LastName,
nm.Message
}).ToList();

更新：

为了获得FKNotificationMessageTypeId＝3的记录，在"；从b."；以及"；选择"：

... from b ...
where nm.FKNotificationMessageTypeId == 3
select new ....