实体框架组按日期返回新类 [英] Entity framework group by date and return new class

问题描述

我正在使用.NET和实体框架。
我想按日期分组一个表，没有时间部分。

我有上课：

  public sealed class QueryItem {
 public int Year {get;组; } 
 public int Month {get;组; } 
 public int Day {get;组; } 
} 
  

方法（我正在使用BLToolkit，如果你想知道什么是DbManager）：

  protected override IQueryable< QueryItem>来自DbManager.GetTable< SomeTableModel>（）
中的查询的新增{Year = query.CreationDate.Year，Month = query.CreationDate.Month，Day的InitiateQuery（）{
返回
 = query.CreationDate.Day} 
 into list1 
选择新的QueryItem {Year = list1.Key.Year，Month = list1.Key.Month，Day = list1.Key.Day}; 
} 
  

在这种方法中，linq被翻译成这个SQL查询：

  SELECT 
 [查询]。[创建日期] 
 FROM 
 [SomeDatabase.SomeTable] [查询] 
 GROUP BY 
 DatePart（Year，[query]。[CreationDate]），
 DatePart（Month，[query]。[CreationDate]），
 DatePart（Day，[query]创建日期]），
 [查询]。[创建日期] 
  

但是，如果我使用这个代码（它不可用，我需要返回一个类对象）：

  var result = 
 from DbManager.GetTable< SomeTableModel>（）
 group query by new {Year = query。 CreationDate.Year，Month = query.CreationDate.Month，Day = query.CreationDate.Day} 
 into list1 
 select list1; 
  

它将被翻译成：

  SELECT 
 [t1]。[c1] as [c11]，
 [t1]。[c2] as [c21]，
 [ 。[c3] as [c31] 
 FROM 
（
 SELECT 
 DatePart（Year，[selectParam]。[CreationDate]）as [c1]，
 DatePart月份，[selectParam]。[CreationDate]）as [c2]，
 DatePart（Day，[selectParam]。[CreationDate]）as [c3] 
 FROM 
 [SomeDatabase.SomeTable] [ selectParam] 
）[t1] 
 GROUP BY 
 [t1]。[c1]，
 [t1]。[c2]，
 [t1] ] 
  

这是正确的查询。

我想实体框架试图优化我的查询或类似的东西，这就是为什么我有错误的查询。

我是对的吗？
我是用正确的方式做的吗？
我应该怎么做（也许使用直接SQL查询或其他东西）？

解决方案

使用起来要简单得多 EntityFunctions *这里：

$在DbManager.GetTable< SomeTableModel>（）
组查询中由EntityFunctions.TruncateTime（查询）创建一个查询。创建日期）
进入list1
选择list1;

^{* DbFunctions 作为实体框架6。}

I'm using the .NET and the Entity Framework. I want to group a table by a date without the time part.

I've got the class:

public sealed class QueryItem {
    public int Year { get; set; }
    public int Month { get; set; }
    public int Day { get; set; }
}

And the method (I'm using the BLToolkit, if you want to know what is DbManager):

protected override IQueryable<QueryItem> InitiateQuery() {
    return
        from query in DbManager.GetTable<SomeTableModel>()
        group query by new { Year = query.CreationDate.Year, Month = query.CreationDate.Month, Day = query.CreationDate.Day }
        into list1
        select new QueryItem {Year = list1.Key.Year, Month = list1.Key.Month, Day = list1.Key.Day};
}

In this method linq translated into this SQL query:

SELECT
    [query].[CreationDate]
FROM
    [SomeDatabase.SomeTable] [query]
GROUP BY
    DatePart(Year, [query].[CreationDate]),
    DatePart(Month, [query].[CreationDate]),
    DatePart(Day, [query].[CreationDate]),
    [query].[CreationDate]

And this is wrong query because it doesn't group only by a date.

But if I use this code (it's not usable, I need to return a class object):

var result =
    from query in DbManager.GetTable<SomeTableModel>()
    group query by new { Year = query.CreationDate.Year, Month = query.CreationDate.Month, Day = query.CreationDate.Day }
    into list1
    select list1;

It will be translated into this:

SELECT
    [t1].[c1] as [c11],
    [t1].[c2] as [c21],
    [t1].[c3] as [c31]
FROM
    (
        SELECT
            DatePart(Year, [selectParam].[CreationDate]) as [c1],
            DatePart(Month, [selectParam].[CreationDate]) as [c2],
            DatePart(Day, [selectParam].[CreationDate]) as [c3]
        FROM
            [SomeDatabase.SomeTable] [selectParam]
    ) [t1]
GROUP BY
    [t1].[c1],
    [t1].[c2],
    [t1].[c3]

And this is the right query.

I suppose that Entity Framework trying to optimise my query or something like this and this is why I've got the wrong query.

Am I right? Am I doing it in the right way? What should I do (maybe use direct SQL query or something else)?

解决方案

It is much simpler to use EntityFunctions* here:

var result =
    from query in DbManager.GetTable<SomeTableModel>()
    group query by EntityFunctions.TruncateTime(query.CreationDate)
    into list1
    select list1;

^{*DbFunctions as of Entity Framework 6.}

这篇关于实体框架组按日期返回新类的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！