我在数据库中有一个包含消息的表,即:
| Id | FromId | ToId | Text | Date | Read |
这是类的样子:
public class Message
{
public int Id { get; set; }
public IdentityUser From { get; set; }
public IdentityUser To { get; set; }
public string Text { get; set; }
public DateTime Date { get; set; }
public bool Read { get; set; }
}
我需要使用实体框架核心形成一个请求,以获得所有用户对话框的列表。我已经勾画出一个示例SQL查询:
SELECT FromId, MAX(Date) as updated_at, sum(Read) as new_messages
FROM (SELECT FromId, Date, Read
FROM Messages
WHERE ToId = 'f3578a36-208c-4504-8f3c-afa564455537'
UNION SELECT ToId, Date, 0
FROM Messages
WHERE FromId = 'f3578a36-208c-4504-8f3c-afa564455537'
ORDER BY Date DESC) as talks
GROUP BY FromId
ORDER BY Date DESC;
其中"f3578a36-208c-4504-8f3c-afa56455537";是我要获取其对话框列表的用户的Id。
然而,我无法使用实体框架执行它(或者不知道如何执行(。有没有办法只使用EF来做到这一点?我使用SQLite。
使用EF,我会这样写,我会分别查询来自和发送给用户的消息,并对它们进行适当的分组:
var chatsFromUser = dbContext.Messages
.Where(m => m.From.Id == "f3578a36-208c-4504-8f3c-afa564455537")
.GroupBy(m => m.To.Id);
var chatsToUser = dbContext.Messages
.Where(m => m.To.Id == "f3578a36-208c-4504-8f3c-afa564455537")
.GroupBy(m => m.From.Id);
但是,我还建议您在Message
实体类中为用户定义ID
的属性
public class Message
{
public int Id { get; set; }
public IdentityUser From { get; set; }
// choose appropiate type, GUID maybe?
public string FromId { get; set; }
public IdentityUser To { get; set; }
// choose appropiate type, GUID maybe?
public string ToId { get; set; }
public string Text { get; set; }
public DateTime Date { get; set; }
public bool Read { get; set; }
}
然后查询将稍微简化,因为您可以直接从消息对象访问ID:
var chatsFromUser = dbContext.Messages
.Where(m => m.FromId == "f3578a36-208c-4504-8f3c-afa564455537")
.GroupBy(m => m.ToId);
var chatsToUser = dbContext.Messages
.Where(m => m.ToId == "f3578a36-208c-4504-8f3c-afa564455537")
.GroupBy(m => m.FromId);