实体框架GroupBy使用mySQL最老的 [英] Entity Framework GroupBy take the oldest with mySQL

问题描述

我有一个巨大的项目清单，需要一个属性分组。然后选择每个组中最老的。

简化示例：选择每个 FirstName 的最旧用户。 / p>

  using（ED.NWEntities ctx = new ED.NWEntities（））
 {
 IQueryable <使用者名称>结果= ctx.User.GroupBy（x => x.FirstName）
 .Select（y => y.OrderBy（z => z.BirthDate）
 .FirstOrDefault（））
 .AsQueryable（）; 
} 
  

类用户

  public partial class User 
 {
 public int UserID {get;组; } 
 public string FirstName {get;组; } 
 public string LastName {get;组; } 
 public Nullable< System.DateTime> BirthDate {get;组; } 
} 
  

我想知道为什么这个语句花了这么久，直到我设置了一个断点 Result 并查看生成的SQL语句：

  {SELECT 
`Apply1`.`UserID`，
`Apply1`.`FIRSTNAME1` as'FirstName`，
`Apply1`.`LastName`，
`Apply1`.`BirthDate` 
 FROM（SELECT 
`Distinct1`.`FirstName`，
（SELECT 
`Project2`.`UserID` 
 FROM`User` AS`Project2` 
 WHERE（`Distinct1`.`FirstName` =`Project2`.`FirstName`）OR（（`Distinct1`.`FirstName` IS NULL）AND（`Project2`.`FirstName` IS NULL））
 ORDER BY 
`Project2`.`BirthDate` ASC LIMIT 1）AS'UserID`，
（SELECT 
`Project2`.`FirstName` 
 FROM`User` AS`Project2` 
 WHERE（`Distinct1`.`FirstName` =`Project2`.`FirstName`）OR（（`Distinct1`.`FirstName` IS NULL）AND（`Project2`.`FirstName` IS NULL））
 ORDER BY 
`Project2`.`BirthDate` ASC限制1）作为'FIRSTNAME1'，
（SELECT 
`Project2`.`LastName` 
 FROM`User` AS`Project2` 
 WHERE（`Distinct1`.`FirstName` =`Project2`.`FirstName`）OR（（`Distinct1`.`FirstName` IS NULL）AND（`Project2 `.`FirstName` IS NULL））
 ORDER BY 
`Project2`.`BirthDate` ASC限制1）AS'LastName`，
（SELECT 
`Project2`.` BirthDate` 
 FROM`User` AS`Project2` 
 WHERE（`Distinct1`.`FirstName` =`Project2`.`FirstName`）OR（（`Distinct1`.`FirstName` IS NULL）AND （`Project2`.`FirstName` IS NULL））
 ORDER BY 
`Project2`.`BirthDate` ASC限制1）作为`BirthDate` 
 FROM（SELECT DISTINCT 
` As`Distinct1`）AS'Apply1`} 
  

问题：有没有办法解决他更有效率？子选择是昂贵的，并且EF每列生成一个。我使用mySQL .NET连接器版本6.9.5.0

解决方案

使用Jon Skeet的 answer on distinct ..

  public static IEnumerable< TSource> DistinctBy< TSource，TKey> 
（此IEnumerable< TSource>源，Func< TSource，TKey> keySelector）
 {
 HashSet&TK; seenKeys = new HashSet< TKey>（）; 
 foreach（源代码中的TSource元素）
 {
 if（seenKeys.Add（keySelector（element）））
 {
 yield return element; 
} 
} 
} 
  

你可以试试： p>

  using（ED.NWEntities ctx = new ED.NWEntities（））
 {
 IQueryable< ED.User> ;结果= ctx.User.OrderBy（y => y.BirthDate）
 .DistinctBy（z => z.FirstName）
 .AsQueryable（）; 
} 
  

I have a huge list of Items and need to Group them by one property. Then the oldest of each group should be selected.

Simplified Example: Select the oldest User of each FirstName.

using (ED.NWEntities ctx = new ED.NWEntities())
{
    IQueryable<ED.User> Result = ctx.User.GroupBy(x => x.FirstName)
                                    .Select(y => y.OrderBy(z => z.BirthDate)
                                    .FirstOrDefault())
                                    .AsQueryable();
}

Class User:

public partial class User
{
    public int UserID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public Nullable<System.DateTime> BirthDate { get; set; }
}

I was wondering why this statement took so long until I set a breakpoint at Result and looked into the SQL statement generated:

{SELECT
`Apply1`.`UserID`, 
`Apply1`.`FIRSTNAME1` AS `FirstName`, 
`Apply1`.`LastName`, 
`Apply1`.`BirthDate`
FROM (SELECT
`Distinct1`.`FirstName`, 
(SELECT
`Project2`.`UserID`
FROM `User` AS `Project2`
 WHERE (`Distinct1`.`FirstName` = `Project2`.`FirstName`) OR ((`Distinct1`.`FirstName` IS  NULL) AND (`Project2`.`FirstName` IS  NULL))
 ORDER BY 
`Project2`.`BirthDate` ASC LIMIT 1) AS `UserID`, 
(SELECT
`Project2`.`FirstName`
FROM `User` AS `Project2`
 WHERE (`Distinct1`.`FirstName` = `Project2`.`FirstName`) OR ((`Distinct1`.`FirstName` IS  NULL) AND (`Project2`.`FirstName` IS  NULL))
 ORDER BY 
`Project2`.`BirthDate` ASC LIMIT 1) AS `FIRSTNAME1`, 
(SELECT
`Project2`.`LastName`
FROM `User` AS `Project2`
 WHERE (`Distinct1`.`FirstName` = `Project2`.`FirstName`) OR ((`Distinct1`.`FirstName` IS  NULL) AND (`Project2`.`FirstName` IS  NULL))
 ORDER BY 
`Project2`.`BirthDate` ASC LIMIT 1) AS `LastName`, 
(SELECT
`Project2`.`BirthDate`
FROM `User` AS `Project2`
 WHERE (`Distinct1`.`FirstName` = `Project2`.`FirstName`) OR ((`Distinct1`.`FirstName` IS  NULL) AND (`Project2`.`FirstName` IS  NULL))
 ORDER BY 
`Project2`.`BirthDate` ASC LIMIT 1) AS `BirthDate`
FROM (SELECT DISTINCT 
`Extent1`.`FirstName`
FROM `User` AS `Extent1`) AS `Distinct1`) AS `Apply1`}

Question: Is there a way to solve his more efficient? Sub-selects are expensive and EF generates one per column. I use mySQL .NET Connector version 6.9.5.0

解决方案

Using Jon Skeet's answer on distinct..

public static IEnumerable<TSource> DistinctBy<TSource, TKey>
    (this IEnumerable<TSource> source, Func<TSource, TKey> keySelector)
{
    HashSet<TKey> seenKeys = new HashSet<TKey>();
    foreach (TSource element in source)
    {
        if (seenKeys.Add(keySelector(element)))
        {
            yield return element;
        }
    }
}

You can try:

using (ED.NWEntities ctx = new ED.NWEntities())
{
    IQueryable<ED.User> Result = ctx.User.OrderBy(y => y.BirthDate)
                                    .DistinctBy(z => z.FirstName)
                                    .AsQueryable();
}

这篇关于实体框架GroupBy使用mySQL最老的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！