如何使用Entity Framework和MySQL包括相关实体获取每个组的最新记录 [英] How to get latest record for each group using Entity Framework and MySQL including related entity

问题描述

我正在使用具有MySQL数据库和DbContext的实体框架。我有一个具有相关实体发件人的实体消息。 （消息还有一个相关实体接收方）。我正在尝试编写一个只会返回每个接收器的最新消息的查询。但是当我这样做时，我也想加载相关的发件人，以便我可以访问发送者的一个属性（电子邮件字段），我需要包括在我返回的数据传输对象中。 MessageDTO是我正在返回的数据传输对象，其中包含消息的ID，消息的内容和发件人的电子邮件。

如果我排除发件人的电子邮件然后，以下查询完全返回我需要的内容（即每个接收者的最新消息）：

  var refGroupQuery =（from m in dbContext.Messages.SqlQuery（select * from messages order by created_at desc）
 group m by m.receiver_id into refGroup 
 select new MessageDTO {id = refGroup.FirstOrDefault（）。 id，content = refGroup.FirstOrDefault（）。content}）; 
  

但是，上述语句不会加载与消息相关联的发件人，将发件人的电子邮件包含在DTO中，我得到一个NullReferenceException，如下所示：

  var refGroupQuery =（from m in dbContext.Messages。 SqlQuery（select * from messages order by created_at desc）
 group m by m.receiver_id into refGroup 
 select new MessageDTO {id = refGroup.FirstOrDefault（）。id，content = refGroup.FirstOrDefault（） .content，sender_email = refGroup.FirstOrDefault（）。sender.email}）; 
  

refGroup.FirstOrDefault（）。sender.email抛出NullReferenceException，因为sender为null。

如何在查询中加载发件人，以便我可以将发件人的电子邮件加入我的DTO？

编辑：

根据请求，我包括由Gert Arnold建议的方法生成的SQL：

  {SELECT 
 1 AS`C1`，
`Apply1`.`id`，
`Apply1`.`sender_id`，
`Apply1`.`RECEIVER_ID1` as`receiver_id`，
`Apply1`.`created_at`，
`Apply1`.`read_status`，
`Extent3`.`email` 
 FROM（SELECT 
`Distinct1`.`receiver_id`，
（SELECT 
`Project2`.`id` 
 FROM（SELECT 
`Extent2`.` id`，
`Extent2`.`sender_id`，
`Extent2`.`receiver_id`，
`Extent2`.`created_at`，
`Extent2`.`read_status` 
 FROM`messages` AS`Extent2` 
 WHERE（`Extent1`.`receiver_id` =`Extent2`.`receiver_id`）OR（（`Extent1`.`receiver_id` IS NULL） AND（`Extent2`.`receiver_id` IS NULL）））AS`Project2` LIMIT 1）AS`id`，
（SELECT 
`Project2`.`sender_id` 
 FROM（SELECT 
`Extent2`.`id`，
`Extent2`.`sender_id`，
`Extent2`.`receiver_id`，
`Extent2`.`content`，
`Extent2`.`created_at`，
`Extent2`.`read_status` 
 FROM`messages` AS`Extent2` 
 WHERE（`Extent1`.`receiver_id` =`Extent2` .`receiver_id`）OR（（`Extent1`.`receiver_id` IS NULL）AND（`Extent2`.`receiver_id` IS NULL）））AS`Project2` LIMIT 1）AS'sender_id`，
（SELECT 
`Project2`.`receiver_id` 
 FROM（SELECT 
`Extent2`.`id`，
`Extent2`.`sender_id`，
`Extent2`。 `receiver_id`，
`Extent2`.`content`，
`Extent2`.`created_at`，
`Extent2`.`read_status` 
 FROM`messages` AS`Extent2 `
 WHERE（`Extent1`.`receiver_id` =`Extent2`.`receiver_id`）OR（（`Extent1`.`receiver_id` IS NULL）AND（`Extent2`.`receiver_id` IS NULL））） AS`Project2` LIMIT 1）AS'RECEIVER_ID1`，
（SELECT 
`Project2`.`re ceivable_type` 
 FROM（SELECT 
`Extent2`.`id`，
`Extent2`.`sender_id`，
`Extent2`.`receiver_id`，
` Extent2`.`content`，
`Extent2`.`created_at`，
`Extent2`.`read_status` 
 WHERE（`Extent1`.`receiver_id` =`Extent2`.`receiver_id `）OR（（`Extent1`.`receiver_id` IS NULL）AND（`Extent2`.`receiver_id` IS NULL）））AS'Project2` LIMIT 1）AS'content`，
（SELECT 
`Project2`.`created_at` 
 FROM（SELECT 
`Extent2`.`id`，
`Extent2`.`sender_id`，
`Extent2`.`receiver_id` ，
`Extent2`.`content`，
`Extent2`.`created_at`，
`Extent2`.`read_status` 
 FROM`messages` AS`Extent2` 
 WHERE（`Extent1`.`receiver_id` =`Extent2`.`receiver_id`）OR（（`Extent1`.`receiver_id` IS NULL）AND（`Extent2`.`receiver_id` IS NULL）））AS'Project2 `LIMIT 1）as`created_at`，
（SELECT 
`Project2`.`updated_at` 
 FROM（SELECT 
`Extent2`.`id`，
` Extent2`.`sender_id`，
`Extent2`.`receiver_id`，
`Extent2`.`content`， 
`Extent2`.`created_at`，
`Extent2`.`read_status` 
 FROM`messages` AS`Extent2` 
 WHERE（`Extent1`.`receiver_id` =` Extent2`.`receiver_id`）OR（（`Extent1`.`receiver_id` IS NULL）AND（`Extent2`.`receiver_id` IS NULL）））AS`Project2` LIMIT 1）AS`read_status` 
 FROM （SELECT DISTINCT 
`Extent1`.`receiver_id` 
 FROM`messages` AS'Extent1`）AS'Distinct1`）AS`Apply1` LEFT OUTER JOIN`users` AS'Extent3` ON`Apply1` .`sender_id` =`Extent3`.`id`} 
  

解决方案

p>您不需要 SqlQuery 结构在分组之前进行排序：

  var refGroupQuery = from m in dbContext.Messages 
 group m by m.receiver_id into refGroup 
 let firstItem = refGroup.OrderByDescending（x => x.created_at）
 .FirstOrDefault（）
选择新的MessageDTO {
 id = firstItem.id，
 content = firstItem.content，
 sender_email = firstItem.sender。电子邮件
}; 
  

这样做一样，但它将整个语句翻译成SQL，这有两个优点

• 发件人不为每封邮件懒惰加载


• sender.email 在发件人为空时不会崩溃，因为在SQL中没有空对象引用。整个表达式（ sender.email ）只返回null。

I am using Entity Framework with a MySQL Database and DbContext. I have an entity "Message" that has a related entity "Sender". (The "Message" also has a related entity "Receiver"). I am trying to write a query that will return only the "latest" message for each receiver. But when I do this, I also want to load the associated "Sender" so that I can get access to one of the Sender's properties (e-mail field) which I need to include in my Data Transfer Object I am returning. "MessageDTO" is the Data Transfer Object I am returning that includes the id of the message, the content of the message and the sender's email.

If I exclude the sender's email from the DTO, then the following query returns exactly what I need (namely, the latest message for each receiver):

var refGroupQuery = (from m in dbContext.Messages.SqlQuery("select * from messages order by created_at desc")
     group m by m.receiver_id into refGroup
     select new MessageDTO { id = refGroup.FirstOrDefault().id, content = refGroup.FirstOrDefault().content});

However, the above statement does not load the Sender that is associated with the Message so when I re-include the sender's email in the DTO I get a NullReferenceException as shown below:

var refGroupQuery = (from m in dbContext.Messages.SqlQuery("select * from messages order by created_at desc")
     group m by m.receiver_id into refGroup
     select new MessageDTO { id = refGroup.FirstOrDefault().id, content = refGroup.FirstOrDefault().content, sender_email = refGroup.FirstOrDefault().sender.email});

refGroup.FirstOrDefault().sender.email throws a NullReferenceException because sender is null.

How can I load the Sender in the query so that I can include the Sender's email in my DTO?

EDIT:

As requested, I include the SQL that gets generated by Gert Arnold's suggested method:

{SELECT
1 AS `C1`, 
`Apply1`.`id`, 
`Apply1`.`sender_id`, 
`Apply1`.`RECEIVER_ID1` AS `receiver_id`, 
`Apply1`.`created_at`, 
`Apply1`.`read_status`, 
`Extent3`.`email`
FROM (SELECT
`Distinct1`.`receiver_id`, 
(SELECT
`Project2`.`id`
FROM (SELECT
`Extent2`.`id`, 
`Extent2`.`sender_id`, 
`Extent2`.`receiver_id`, 
`Extent2`.`created_at`, 
`Extent2`.`read_status`
FROM `messages` AS `Extent2`
 WHERE (`Extent1`.`receiver_id` = `Extent2`.`receiver_id`) OR ((`Extent1`.`receiver_id` IS  NULL) AND (`Extent2`.`receiver_id` IS  NULL))) AS `Project2` LIMIT 1) AS `id`, 
(SELECT
`Project2`.`sender_id`
FROM (SELECT
`Extent2`.`id`, 
`Extent2`.`sender_id`, 
`Extent2`.`receiver_id`, 
`Extent2`.`content`, 
`Extent2`.`created_at`, 
`Extent2`.`read_status`
FROM `messages` AS `Extent2`
 WHERE (`Extent1`.`receiver_id` = `Extent2`.`receiver_id`) OR ((`Extent1`.`receiver_id` IS  NULL) AND (`Extent2`.`receiver_id` IS  NULL))) AS `Project2` LIMIT 1) AS `sender_id`, 
(SELECT
`Project2`.`receiver_id`
FROM (SELECT
`Extent2`.`id`, 
`Extent2`.`sender_id`, 
`Extent2`.`receiver_id`, 
`Extent2`.`content`, 
`Extent2`.`created_at`,
`Extent2`.`read_status`
FROM `messages` AS `Extent2`
 WHERE (`Extent1`.`receiver_id` = `Extent2`.`receiver_id`) OR ((`Extent1`.`receiver_id` IS  NULL) AND (`Extent2`.`receiver_id` IS  NULL))) AS `Project2` LIMIT 1) AS `RECEIVER_ID1`, 
(SELECT
`Project2`.`receivable_type`
FROM (SELECT
`Extent2`.`id`, 
`Extent2`.`sender_id`, 
`Extent2`.`receiver_id`, 
`Extent2`.`content`, 
`Extent2`.`created_at`,  
`Extent2`.`read_status`
 WHERE (`Extent1`.`receiver_id` = `Extent2`.`receiver_id`) OR ((`Extent1`.`receiver_id` IS  NULL) AND (`Extent2`.`receiver_id` IS  NULL))) AS `Project2` LIMIT 1) AS `content`, 
(SELECT
`Project2`.`created_at`
FROM (SELECT
`Extent2`.`id`, 
`Extent2`.`sender_id`, 
`Extent2`.`receiver_id`,  
`Extent2`.`content`, 
`Extent2`.`created_at`, 
`Extent2`.`read_status`
FROM `messages` AS `Extent2`
 WHERE (`Extent1`.`receiver_id` = `Extent2`.`receiver_id`) OR ((`Extent1`.`receiver_id` IS  NULL) AND (`Extent2`.`receiver_id` IS  NULL))) AS `Project2` LIMIT 1) AS `created_at`, 
(SELECT
`Project2`.`updated_at`
FROM (SELECT
`Extent2`.`id`, 
`Extent2`.`sender_id`, 
`Extent2`.`receiver_id`, 
`Extent2`.`content`, 
`Extent2`.`created_at`, 
`Extent2`.`read_status`
FROM `messages` AS `Extent2`
 WHERE (`Extent1`.`receiver_id` = `Extent2`.`receiver_id`) OR ((`Extent1`.`receiver_id` IS  NULL) AND (`Extent2`.`receiver_id` IS  NULL))) AS `Project2` LIMIT 1) AS `read_status`
FROM (SELECT DISTINCT 
`Extent1`.`receiver_id`
FROM `messages` AS `Extent1`) AS `Distinct1`) AS `Apply1` LEFT OUTER JOIN `users` AS `Extent3` ON `Apply1`.`sender_id` = `Extent3`.`id`}

解决方案

You don't need the SqlQuery construct to do the ordering before the grouping:

var refGroupQuery = from m in dbContext.Messages
     group m by m.receiver_id into refGroup
     let firstItem = refGroup.OrderByDescending(x => x.created_at)
                             .FirstOrDefault()
     select new MessageDTO { 
                              id = firstItem.id, 
                              content = firstItem.content,
                              sender_email = firstItem.sender.email
                           };

This does the same, but it translates the whole statement into SQL, which has two advantages

• sender is not lazy loaded for each message
• sender.email does not crash when sender is null, because in SQL there is no null object reference. The whole expression (sender.email) just returns null.

这篇关于如何使用Entity Framework和MySQL包括相关实体获取每个组的最新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！